PhishThis!

Recently, some of our developers were writing an app that required impersonation from the web service, as the user, to the database. Admittedly, Kerberos isn’t one of my strong points.

There were two hops here. From the user -> IIS server and from IIS Server -> SQL Server, but the application in IIS would impersonate the user when authenticating with the SQL server.

So, the idea here is that from the user to the IIS server, we know Kerberos will work. The user passes its ticket to the web service. Nothing unusual. From there, the web app, running as a custom app pool ID, needs to pretend (delegate) to be the user when it authenticates to the SQL server.

There are a few requirements.
1) Your application in IIS should be running under a custom identity – domain\MyAppService
2) SQL Server needs to be running under a domain service account – domain\MySQLService
3) IIS needs to use Negotiate instead of NTLM for that application. It should do this by default, then fall back to NTLM. For whatever reason, my app was using NTLM. IIS should also have Windows Authentication enabled.
4) Change your connection string to impersonate the site user

Step 1 – Set the SPN on your app pool ID for the site, for the hostname and FQDN.
setspn -a http/mysite domain\MyAppService
setspn -a http/mysite.domain.com domain\MyAppService

Step 2 – Set the SPN for the SQL service on your SQL service account – assuming you use the default SQL port
setspn -a MSSQLSvc/hostname domain\MySQLService
setspn -a MSSQLSvc/hostname.domain.com domain/MySQLService
setspn -a MSSQLSvc/hostname:1433 domain\MySQLService
setspn -a MSSQLSvc/hostname.domain.com:1433 domain/MySQLService
Restart SQL

Step 3 – In Active Directory Users and Computers, find the service account, click the delegation tab, and trust it for delegation. You can set it for delegation to anywhere, or constrained delegation to the SPNs you’ll set for the SQL service account.

Step 4 – Force your site or application to use Negotiate. This won’t work with NTLM, so we’ll remove it. (Note: This is for IIS7/7.5)
- Find and open your applicationHost.config. It’s probably under c:\windows\system32\inetsrv\config. You can also set this in the system.webServer section of the web.config for the application.

- Scroll to the bottom and above /configuration copy this in:

   <location path="SitePath">
        <system.webServer>
            <security>
                <authentication>
                    <windowsAuthentication>
                        <providers>
                            <add value="Negotiate" />
                            <remove value="NTLM" />
                        </providers>
                    </windowsAuthentication>
                </authentication>
            </security>
        </system.webServer>
    </location>

If you get a 500 error after adding the above XML, it’s probably because Negotiate is already added elsewhere. Just remove the line that says add value=”Negotiate” and leave the remove NTLM line.

Reference: This post was extremely helpful in solving my problem – http://blogs.technet.com/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx – in the end, I did pretty much everything in that post, and still had the IIS server passing anonymous to SQL, which is what tipped me off that it was using NTLM and not Negotiate.