11

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?

Martin Robins
  • 6,033
  • 10
  • 58
  • 95
  • Enable Kerberos event logging on the web and database server: http://support.microsoft.com/kb/262177?wa=wsignin1.0 The event viewer is key to finding and fixing kerberos errors. – brian Dec 04 '12 at 16:48
  • I have enabled this, and also downloaded the Network Monitor tool, but I still cannot see enough information to tell me what is wrong. I guess I am not reading something correctly as I have no doubt the answer is in there. – Martin Robins Dec 06 '12 at 09:57

2 Answers2

7

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

Community
  • 1
  • 1
Joe
  • 1,649
  • 12
  • 10
  • 1
    I have seen a couple of these already, and now tried the advice in the others. Alas I still cannot get this to work. – Martin Robins Dec 06 '12 at 09:58
  • 3
    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. http://blog.reveille.org.uk/2010/01/asp-net-impersonation-delegation/ – Joe Dec 13 '12 at 18:50
  • 2
    Joe; you are right - it was the "Negotiate:Kerberos" that was not shown in any of the other documentation that I found - probably because AFAIK that is new for IIS7+ and all the documentation is for IIS6. – Martin Robins Jan 10 '13 at 10:44
  • Ive spent a few days trying to sort this damn DH issue. Cheers for the heads up! – else Dec 19 '14 at 15:04
6

When you configure the SPNs for SQL Server, we have found that we need to include the PORT on which SQL Server listens (1433).

You should download and use Brian Booth's DelegConfig v2 tool to help you setup the correct configuration settings. http://blogs.iis.net/brian-murphy-booth/archive/2009/04/22/delegconfig-v2-beta.aspx

It will basically hold your hand the whole way through the process. We've found the tool to be invaluable.

John Ruiz
  • 2,371
  • 3
  • 20
  • 29
  • I have downloaded the tool. Whilst it is very good, it tells me that the configuration is correct, but when I then try a test access to a table within the database I get the same logon failed message. – Martin Robins Dec 06 '12 at 09:56
  • 1
    My advice is to replace SQL2008R2 with the port 1433. `setspn -a MSSQLSvc/sql1.mydomain.local:1433 SqlServer` – John Ruiz Dec 07 '12 at 14:53
  • John, you were correct about the SPN too; I had named instance SPN's but as soon as I deleted them and created a single SPN for host.fqdn:1433 this helped. – Martin Robins Jan 24 '13 at 14:14