ASP.NET Impersonation scenario. IIS 8.5 to SQL 2012, what SPN needs to be setup?

Question

I have read many blogs, and many articles relating to how to have my IIS server pass the authenticated user to SQL. I am stuck however on what exactly I need to set my SPN on and to. Here are my settings...

IIS:
Application Pool .Net v4.0
Integrated Pipepline
Identity = NetworkService

Authentication:
ASP.NET Impersonation = True (enabled)
Windows Authentication Enabled
Provider = Negotiate:Kerberos
Disabled Kernel-mode authentication

IIS SERVER:
Trusted for delegation for any service (kerberos only)

SQL SERVER:
Trusted for delegation for any service (kerberos only)

From what I understand with these settings I should be passing the windows account that I am currently logged in as into the IIS server, and it should be passing that token to SQL to authenticate me. However, it is only continuously prompting me for a user / pass from the site.

I Found this article http://blog.reveille.org.uk/2010/01/asp-net-impersonation-delegation-2/ that tells me I need to set SPN on the "Machine" using something like this:

setspn -A HTTP/[dns name of the site] [machine name]

I am unsure exactly what SPN(s) I need. Could anyone help me out? I'm thinking it's something like

setspn -A HTTP/mywebserver.mydomain.com mywebserver

Or would it be better to use a domain account in my application pool with the SPN set on those?

Answer 1

Good ol' Kerberos. I'm not over-complicate this answer, unless more explanation is requested. I'm going to presume from your question that we are talking about a single server and I will also presume it's domain joined. Hence, I'm going to define the following for my checklist:

• Let the NetBIOS name for the server be websvr1
• Let the FQDN (fully-qualified domain name) for the server be websvr1.domain.com
• And of course, we presume that we are on the domain domain.com
• Let the NetBIOS name for the SQL Server be sql1
• Let the FQDN for the SQL server be sql1.domain.com
• Let the SQL Server service user be DOMAIN\sqlusr
• I'm assuming the default SQL Server port of 1433

Having said that, plus taking into account what's already stated in your question, I would try this setup:

First Hop Setup (SPN)

• Register the following SPNs
• setspn -S HTTP/websvr1.domain.com websvr1$
• setspn -S HTTP/websvr1 websvr1$

First Hop Setup (Authentication)

• Please include Negotiate and NTLM in the providers in order to allow a fallback mechanism.
• NOTE: Ensure Negotiate is on top of NTLM in the providers list.
• You don't need to disable Kernel-mode authentication. Honestly, I'm not sure why so many people disable Kernel-mode authentication. Even for application pools running as a domain account, you can use the useAppPoolCredentials="true" attribute to allow Kernel-mode authentication successfully decrypt a Kerberos ticket encrypted with a domain account.
• The rest of your setup is fine.

Second-Hop Setup (SPN)

• Just to ensure we cover all possible combinations, set the following SPNs:
• setspn -S MSSQLSvc/sql1.domain.com DOMAIN\sqlusr
• setspn -S MSSQLSvc/sql1.domain.com:1433 DOMAIN\sqlusr
• setspn -S MSSQLSvc/sql1 DOMAIN\sqlusr
• setspn -S MSSQLSvc/sql1:1433 DOMAIN\sqlusr

Second-Hop Setup (IIS delegation)

Now that both the IIS and SQL SPNs are setup, you should be able to setup constraint delegation to allow IIS to request an MSSQLSvc service ticket on behalf of the currently logged on user's behalf.

• In the AD delegation tab for the DOMAIN\websvr1 computer account, select the "Trust this computer for delegation to the specified services only" option.
• Leave the Use Kerberos only
• Select the MSSQLSvc\sql1 and MSSQLSvc\sql1.domain.com from the list and add these services.

The setup above is considered a constrained Kerberos delegation, which is much more secure than the unconstrained delegation. And to my understanding that should cover it.

NOTE

I chose to use setspn -S since it will ensure I don't accidentally set duplicate SPNs and break Kerberos authentication. From the example above, please substitute the server names, FQDN and accounts for their real respective values.

NOTE (Again)

For the Web Server SPN, if for some reason the URL host that we are requesting is just a custom DNS entry (please ensure it's an A-record instead of a CNAME, by the way) then you'll need to register said DNS entry instead).

Hopefully the information above helps.