ASP.Net web application trying to use Impersonation and Delegation to connect to SQL Server

Graham Clark picture Graham Clark · Jan 20, 2010 · Viewed 15.2k times · Source

I'm trying to use Impersonation and Delegation in an intranet ASP.Net web-app in order to pass authenticated users' credentials onto a SQL Server.

The web server and SQL server are two separate machines, but in the same domain, so Delegation is required.

I've done the following:

  • set <authentication mode="Windows"/> and <identity impersonate="true"/> in my web-app's web.config.
  • enabled Constrained Delegation from the web server to the MSSQLSvc service on the SQL Server, in Active Directory.
  • enabled only Windows Authentication in the website, through IIS.

Apparently this should all work, but it doesn't (the SQL Server is denying access to the anonymous user - "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'").

In IIS7, the Application Pool is set to use Integrated Pipleline Mode and is running with the NetworkService Identity. The website only has Windows Authentication enabled, Extended Protection is Off, Kernel-mode authentication is enabled, and NTLM is the provider.

All the web pages I've read seem to indicate that my setup should work. What am I missing?

Answer

Graham Clark picture Graham Clark · Jan 20, 2010

I've discovered the answer:

The Windows Authentication provider in IIS7 must be set to Negotiate:Kerberos, not NTLM. This means that the Kernel-mode authentication setting must be disabled. This seems to be fine. I think I'm right in saying that Kernel-mode authentication is required when using a custom identity, i.e. one specific identity. Delegation can use an arbitrary number of identities. So all is well.

I've written a blog post about this too, which goes into a bit more detail.