Can't get Impersonation / delegation to work

[Al]

Sorry that I've posted this in a couple of places, but i'm getting desperate.

I'm trying to use Impersonation in a website, and use delegation to allow
connection to a remote SQL Server. It's this delegation step that I'm stuck
on.

My test enviroment is a Virtual Server 2005 R2, hosting 3 Win2003 Servers
and the domain is called TEST.LOCAL. The first Win2003 is called OLYMPUS and
hosts the Active Directory. The AD is now in Win2003 only mode.
The 2nd Win2003 is called HADES and is running IIS6.0 and SQL Server. HADES
has been set as "Trusted for Delegation" to any service (Kerberos only).
The 3rd Win2003 is called ZEUS and is running SQL Server.

HADES is hosting an ASP.Net 2.0 web page, which makes a SQL connection to
both HADES and ZEUS. The web page / site is set for Intergrated Security only
and the ASP.Net Impersonate is turned on. The web page is in the default
Application Pool which is running under the local Network Service account.
This account is set locally to be both "Act as OS" and "Trusted for
delegation".

When accessing the web page from HADES as http://localhost/SQLTest, both SQL
Server connections are made. I do realise that this isn't really delegation,
but it shows me that the Impersonation is working and that the user is
allowed to connect to all the services that is requires.

When accessing the web page from any of the machines as
http://Hades/SQLTest, the SQL Server connection to ZEUS fails. I've checked
the Security Event Log on ZEUS and can see that a connection is being made as
the Anonymous user and using NTLM.

I have checked the SPN for both ZEUS and HADES. Both as showing the SQL
Server default instances that I'm trying to connect to. Neither SQL Server is
using a domain account, so these are the auto-registered SPN.

I can't see a HTTP SPN for ZEUS, but I'm assuming that as I'm using the
NETWORK SERVICE to run the application pool that this is not a problem.

So, does anyone have any ideas as to what I need to do next?

 

[Dominick Baier]

check the security log on ther server for the logon event - does it show

authentication package: Kerberos??

also have a look at this article:

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx

 

[Joe Kaplan]

That's a good place to start for sure. The usual problem in a delegation
scenario is that you aren't getting Kerberos authentication in the front end
(only NTLM), so you can't delegate. If you are using a 100% 2003
infrastructure (2003 server and AD), you can get around this by using
protocol transition (S4U). IIS will even do this for you automatically.

However, the service needs to be trusted for delegation with "any protocol"
and you must then configure which services you are delegating to
(constrained delegation). You should really be using constrained delegation
anyway, as unconstrained is a much bigger security hole.

Essentially, protocol transition will allow you to go from NTLM on the web
app to Kerberos when you need to delegate to the back end.

The other way to fix this is to get Kerberos auth in the browser.
Generally, there are a few tricks:
- Make sure you have the proper SPN set on the account running the service
(the machine account if app pool runs as Network Service). This should be
"HOST/hostname" or "HTTP/hostname". The hostname can be the NetBIOS name or
the DNS name and you can use both.
- Make sure the browser URL hostname matches the SPN. You'll never get
Kerberos auth using "localhost".
- Make sure the browser is configured to do IWA.

The other important piece is getting Kerberos auth on the backend. Mostly,
the same rules apply. Make sure that the hostname in the connection string
matches the SPN and make sure you have proper SPNs for the account running
SQL Server. In this case, you MUST be able to do Kerberos auth to the SQL
box, so that absolutely needs to be working. One way to verify that is to
disable impersoonation and see if the network service/machine account on the
web box can do Kerberos auth to SQL. Once again, the security event log is
your friend here (enable logon event auditing in local security policy).

Best of luck. My experience has been that once you get it working, it all
kind of makes sense and is exactly what the documentation said, but for some
reason the documentation is never adequate to help you fully understand it
in advance. I've set these up a lot now and I still only get it working the
first time about 50%. I am much faster to troubleshoot than I was though.

Joe K.