Kerberos double-hop in ASP.NET 4.0 & SQL2008R2

Question

I have an ASP.NET 4.0 application within which I need to forward the authentication to the database. For the purposes of this request for assistance, lets call the web server "app1" and the database server "sql1".

The SQL2008R2 database service is running as a named instance "SQL2008R2" under a custom domain account "SqlServer". The server is running Windows Server 2008 R2 Enterprise Edition. I have created an SPN for this...

setspn -a MSSQLSvc/sql1.mydomain.local:SQL2008R2 SqlServer

The ASP.NET application is running under an application pool using a custom domain account "WebApplicationUser", in Integrated Pipeline mode. It is currently running on my laptop running Windows 7 Enterprise, but will eventually be hosted on Windows Server 2008 R2 Standard Edition. I have created 2 SPN's for the application (on the Windows 7 machine that I am currently running from)...

setspn -a http/app1 WebApplicationUser
setspn -a http/app1.mydomain.local WebApplicationUser

Within Active Directory users and Computers, I have selected the "WebApplicationUser" account and I have enabled constrained delegation to "MSSQLSvc/sql1.mydomain.local:SQL2008R2" using any protocol (I have also tried using Kerbero only).

The Application is setup in IIS 7.5 and the authentication is set to disable Anonymouse, Basic, Digest and Forms whilst enabling "ASP.NET Impersonation" and "Windows". The Windows authentication has "Extended protection" turned off and "Kernel-mode authentication" enabled. The providers are "Negotiate" and "NTLM" in that order.

The ASP.NET application uses EF, and the connection string is configured to use integrated security...

<connectionStrings>
    <add name="MyContext"
             connectionString="metadata=res://*/Data.MyModel.csdl|res://*/Data.MyModel.ssdl|res://*/Data.MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=sql1.mydomain.local\sql2008r2;Initial Catalog=MyDatabase;Persist Security Info=false;Integrated Security=True;MultipleActiveResultSets=True&quot;"
             providerName="System.Data.EntityClient" />
</connectionStrings>

My web config specifies both Windows authentication and impersonation, since I a using async pages, I have also enabled inpersonation policy flowing...

<runtime>
    <alwaysFlowImpersonationPolicy enabled="true" />
</runtime>

<system.web>
    <authentication mode="Windows" />
    <identity impersonate="true" />
</system.web>

If I log on locally (on "web1") and browse to the application (using IE), this all works - but this does not involve the double hop that I am trying to resolve.

If I log on to another machine and then browse to the application using IE, or I browse from the local machine using FireFox, this does not work - note: FireFox does prompt me for the login details. The connection to the database fails with "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

Unlike a lot of the articles (and here might be part of the problem), I am not using any custom code to impersonate the user. It is my understanding that the impersonation will be applied across the board to the application by the web.config settings above. All I do is to open the connection and then close it again when I am finished with it.

I have obviously missed a step (or two), but having looked at all of the documentation that I can find (and there has been a lot), I still cannot find what that step is. It does not help that 99% of the documentation that I can find is actually related to IIS6 and Windows 2003 but the principles should remain the same.

Has anybody suceeded in getting such a configuration to work on Windows 7 and/or Windows Server 2008?

Answer 1

Checklist for Double Hop issues {IIS and SQL Server}

http://blogs.technet.com/b/taraj/archive/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server.aspx

http://www.phishthis.com/2009/10/24/how-to-configure-ad-sql-and-iis-for-two-hop-kerberos-authentication-2/

http://support.microsoft.com/kb/810572

IIS to SQL Server kerberos auth issues