Impersonation and delegation (with SQL Server) in ASP.NET

Question

I've written a simple ASP.NET application that works as a frontend for a simple MSSQL database. The application is accessible over the Internet.

There are two physical servers involved: a WS2008R2 Active Directory domain controller which is also running MSQL Server 2008 R2, and another server, the webserver (WS2008R2/IIS7.5) where my application resides.

The Application Pool for my application "FooPool" has its own AD User identity it runs under "FooUser". FooUser does not have any permission to access the SQL Server database, instead only my own personal user account "MyUser" has that permission.

The idea is that attempts to access this web application first perform Windows Authentication with IIS, my web application then uses Impersonation to access the SQL Server database.

However my application does not work.

I tested the application without it touching SQL Server, just to test impersonation, so I did Response.Write( WindowsIdentity.GetCurrent(false).Name ); which correctly shows the application impersonating MyUser and not acting as FooUser. This works from all modern browsers and across the Internet.

But as soon as it touches MSSQL Server I get the error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." but that should not be happening because no user tokens are for Anonymous Logon.

I've done my homework and read all about Delegation and Impersonation in ASP.NET and I have set up delegation: The FooUser account has a Service Principal Name set-up (I set the SPN to an arbitrary string, is that doing it right?) and is marked for delegation in ADUC.

Finally, my connection string has SSPI enabled, Connection pooling disabled, and the network library set to "dbmssocn".

What else am I forgetting?

Answer 1

Finishing the Configuration for Delegation to Work you must enable constrained delegation:

1. Open Active Directory Users and Computers
2. Find the user account that the IIS Web site is using for the web application pool and double-click it
3. Select the option: Trust this user for delegation to specified
   services only.
4. Make sure that the user is constrained to the SPN associated with the MSSQLSvc service
5. Restart IIS

http://blogs.technet.com/b/askds/archive/2008/11/25/fun-with-the-kerberos-delegation-web-site.aspx