Double Hop Window Authentication

Question

I've got a problem getting Windows Authentication (Kerberos) to work when passing credentials from the user, to IIS then from IIS to SQL. I have setup SPN's for SQL, and set the IIS server account up to allow delegation.

If I set the IIS computer account to allow Delegation for any service, it works:

However if I set it up for specific services, the credentials are not passed and I get an error about connecting with the anonymous user:

As you can see, I'm connecting to SQL Express Instance, and I've set up a number of SPN's to try and resolve this, no luck with any of them. Obviously the fact that it works when allowing any service, says to me that something else is missing from this list of services, but I don't know what!

Answer 1

For anyone else that may come across this problem, the issue was down to using Kernal Mode authentication, with a domain user account for the app pool account.

Kernel mode authentication does most of the work for you regarding IIS SPNs, however it expects you to be using the system account for the app pool identity. If you are using a domain account you will need to setup the HTTP SPN for this user. You will then need to delegate access to SQL on this user account, not on the IIS Machine account, as you would normally with kernel mode auth.

Answer 2

I have got this working in the past with IIS6 and Windows 2003 and SQL 2005, but it's a long time since I looked at it, but in case it helps here's what I can find out:

In AD, the web server has one entry for the SQL server set to 'trust for specified services only', 'use any authentication protocol' and the entry has the SQL hostname, not FQDN. Service Type is MSSQLSvc and port is 1433.

The SQL Server computer account is not trusted for delegation.

I also remember having to use 'setspn' at the command line while configuring it, and the settings I have from 'setspn -L webserver' are:

HTTP/intranet.domain.example.org:80 HTTP/intranet:80 HOST/webserverhostname HOST/webserverhostname.domain.example.org 

Where 'intranet' is an alias we use for the website, and put your real FQDNs in, not example.org ones, e.g.

setspn -A HTTP/intranet:80 webserver 

and so on.

Also, this looks like a pretty thorough checklist: http://blogs.technet.com/b/taraj/archive/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server.aspx

Answer 3

I found that irrespective of the web server SPN configuration, I needed to create an SPN for the SQL Server using an HOST record for the SQL Server name, rather than a CNAME alias.

That is, to cover all bases, I have added SPN's as follows;

setspn -A MSSQLSvc/sqlserverhostname.example.org SQLServerServiceAccountName

setspn -A MSSQLSvc/sqlserverhostname:1433 SQLServerServiceAccountName

setspn -A MSSQLSvc/sqlserverhostname.example.org:1433 SQLServerServiceAccountName

setspn -A MSSQLSvc/sqlserverhostname SQLServerServiceAccountName

.. ensuring that "sqlserverhostname" was registered on the DNS as a HOST record.