Double Hop Window Authentication

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.

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