SPN Setup for Double hop from Client (IE) to IIS 8 to SQL Server (2012)

The following is the required steps to set up kerberos authentication for a client to sql server.

This is a rough post, created purely for self reference.  A lot of this work is talked about at the following location http://blogs.msdn.com/b/psssql/archive/2011/02/21/sharepoint-adventures-using-kerberos-with-the-report-server.aspx

On IIS:

  • Open ISS Manager and go to application pools.
  • Set the correct application pool to use a dedicated user – right click and slect advanced settings. Change identity to the domain user required. If you’re not sure what application pool is the being used by the web app, stop them all and then start them individually, whilst refreshing the web app. When the correct one is used, the web app should work (somewhat!).
  • Next, go to the web application web site in IIS manager. Double click authentication and enable ASP.NET Impersonation (right click and select enable).
  • Right click on Windows Authentication and enable too.
  • Right click on Windows authentication and select Advance Settings and untick Enable Kernel-mode Authentication.
  • Click ok then right click on Windows Authentication and select providers.
  • Add Negotiate:Kerberos from the available providers drop down list.  Make sure this is the top of the list of enabled providers, above negotiate.

On a domain connected computer:

Add the SPN’s:

  • Open a cmd window and add an spn for the service account that the web app is running under. This is the domain account you set for the application pool.  This SPN should be for HTTP service for the URL of the server. In this case below the server name:
    setspn -A HTTP/WebserverName domain\domainuser
    i.e: setspn -A HTTP/SGBB1234 CONTOSO\WebAppServiceUser
  • Add the SPN for the Fully qualified domain name too, i.e.:
    setspn -A HTTP/SGBB1234.CONTOSO.UK.COM CONTOSO\WebAppServiceUser
  • Now check the spn is set for the SQL Server Service account. This is the account the SQL Server is running under:
    setspn -l SQLServerServiceAccount
  • If nothing is listed, then add the spn for the database instance using port number and instance name:
    setspn -A MSSQLSvc/ServerNameFQN:portnumber domain\SQLServiceAccount
    setspn -A MSSQLSvc/ServerNameFQN:InstanceName domain\SQLServiceAccount
    i.e.:
    setspn -A MSSQLSvc/DBServer.Contoso.UK.COM:1453 contoso\Account1
    setspn -A MSSQLSvc/DBServer.Contoso.UK.COM:Instance1 contoso\Account1
  • After you have setup the SPN’s using the SETSPN commands for the instance and port of the SQL Server Service Account, run the command <code>dsacls “CN=<CommonName>, OU=<OrganisationUnit>, DC=<DomainComponent>” /G SELF:RPWP;”servicePrincipalName”</code>
    Change the CN, OU and DC values to be appropriate for the service account.  If you look at the SETSPN command for the service account, this will provide you with these values and so the whole string can be copied and pasted I.e.
    <code>dsacls “CN=Account1,OU=Service Accounts, OU=Administrative, OU=HO, DC=contoso,DC=ad,DC=contoso,DC=com” /G SELF:RPWP;”servicePrincipalName”</code>
    The dsacls command is part of the AD feature pack.  You should see the command complete successfully.

    Once all is done, run the command setspn -X to check for any duplicates. Remove any duplicate SPN’s from the incorrect location.

  • You will then need to restart SQL so that it can register the SPN in AD.

Now setup Delegation:

Once the SPN’s are set then the delegation tab will be visible in AD for users:

  • Open AD Users and Computers and navigate to the domain user used to run the app pool web service.
  • Click on Delegation tab and select Trust this user for delegation any service, or to a specific service if more granulatrity required.
  • Do the same for the IIS server in AD too.

Once all the above is complete, restart IIS.

On the client pc, make sure no kerberos tickets are cached and causing a incorrect response.  Do this by opening a cmd window and typing “klist purge”. It should say tickets cleared.

Now do a ctrl + F5 refresh in IE, and the kerberos authentication should work!

Hopefully that is it!

Advertisements

About djheath
I have been in the IT industry since 2003, working in many different guises from a Unix Admin, System Admin, Software Support, Oracle DBA, SharePoint Developer, to my current role as a Senior SQL Server Database Administrator and Architect for De La Rue. This Blog is primarily about problems I have experienced in my technical work which I have struggled with, and not found a concise answer too, or snipits of useful information I don't want to forget.

2 Responses to SPN Setup for Double hop from Client (IE) to IIS 8 to SQL Server (2012)

  1. Travis says:

    Great post. Would you mind answering a question? We have a SQL server running under a domain account, and an IIS server running under a separate domain account. Is delegation required on the SQL account? We want IIS to pass the logged in computer account to SQL. I followed your steps above (without delegation on the SQL account) and I get prompted for a user and password when accessing the IIS site. Is this always going to be the case, or is it because it’s not passing my logged in computer account properly?

    • djheath says:

      Hi,

      You would want to set the IIS domain account to have delegation to the SQL SPN. You shouldn’t need to add delegation to the SQL account.
      In you scenario, I would do the following.

    • Check the SPN’s for SQL are set up correctly and working. I.e. look in the SQL Server log and check you cannot see the message “The SQL Server Network Interface library could not register the Service Principal Name (SPN)…. “. This will be shown at the very beginning of the log when SQL Server started. If you do and you have set up the SPN’s as in the above article, there is an extra step to make sure the SPN’s get created that I need to add to the above post. I’ll go into that in a second.
    • If the SPN’s are setup, open AD and for the IIS account, look in the delegation tab under properties. If you cant see delegation, then enable Advanced Features from the View menu option. Check that “Trust this user for delegation to any server (Kerberos only)” is selected. Then test. If this works. Then set it to only look at the SQL Service for the SQL Server.
    • Finally, in IIS, I would check that it is setup to allow Windows Authentication correctly as above.

      I need to add the following item to the above article as I found recently that some SPN’s still aren’t being automatically registered when the SQL Server Service starts unless the following is done:

    • After you have setup the SPN’s using the SETSPN commands for the instance and port of the SQL Server Service Account, run the command dsacls "CN=, OU=, DC=" /G SELF:RPWP;"servicePrincipalName"
      Change the CN, OU and DC values to be appropriate for the service account. If you look at the SETSPN command for the service account, this will provide you with these values and so the whole string can be copied and pasted I.e.
      dsacls "CN=serviceAccount_1,OU=Service Accounts, OU=Administrative, OU=HO, DC=contoso,DC=ad,DC=contoso,DC=com" /G SELF:RPWP;"servicePrincipalName"
      The dsacls command is part of the AD feature pack. You should see the command complete successfully.
    • After this is done, restart the SQL Server and see if this works ok.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: