Configuring SQL Server Kerberos for Double-Hop Authentication

The Requirement

We have one database stored on SQL Server (A), which has some synonyms to tables in SQL Server (B).  We want our .NET 4.5 application (running under IIS) to invoke some queries to move data from tables in SQL Server (A) to SQL Server (B), using the synonyms (so the web application doesn’t need to know about SQL Server (B)).

Environment

• Windows Server 2012 R2

• SQL Server 2012, services running as a domain service account

• IIS Application Pool Identity running as a domain service account

• SQL Server (A) has a linked server to SQL Server (B)

• Both SQL Servers running named instances

The Problem

When IIS talks to SQL Server (A), it does so using it’s domain service account (as that is the account running the AppPool).  That account has been granted sufficient privileges over the database on SQL Server (A) such that it can happily perform operations on it.

When the application wishes to work on tables residing in a database on SQL Server (B), through the use of the synonyms in SQL Server (A), because it is a different server when SQL Server (A) tries to run commands on SQL Server (B) it has no user identity to pass with the request.  This results in the following exception being thrown from SQL Server (B):

Login failed for user 'NT AUTHORITYANONYMOUS LOGON'

SQL Server (A) has not been granted delegation rights to submit commands to SQL Server (B) using the IIS AppPool’s Identity.

The Solution (Overview)

The solution is to use Kerberos authentication throughout the flow.  When using Windows Authentication in the connection between the IIS application and SQL Server, as indicated by a connection string entry similar to the following:

Data Source=.;initial catalog=MyDb;integrated security=SSPI;

IIS will first attempt Kerberos authentication (if it can), otherwise it will fallback to NTLM authentication (this is seamless to the client, but it can be seen if you run a network trace).

If Kerberos authentication succeeds between the IIS application and SQL Server (A), then provided SQL Server (A) has been given delegation rights over the IIS AppPool Identity account, it can make a subsequent request to SQL Server (B) (when it needs to) using the IIS AppPool Identity account, rather than NT AuthorityANONYMOUS LOGON.

There are six steps in getting this to work:

1. Configure Service Principal Names (SPNs) for the appropriate services / accounts for SQL and IIS

2. Check that IIS is authenticating to SQL Server (A) using Kerberos

3. Grant SQL Server (A) delegation rights for the IIS AppPool Identity account

4. Grant that account permissions on the SQL Server (B) database as appropriate

5. Enable DTC Options

6. Tweak remote queries

The Solution (Steps)

Step 1 – Configure SPNs

Overview:

• The domain account that the SQL Server services are running under needs a SPN for the MSSQL service (and several variations for each one)

• The domain account that the IIS AppPool is running under needs a SPN for each IIS website that will be connecting to the SQL Server

SQL

setspn -a domainsqlsvc-account MSSQLSvc/host.domain.com:1433

setspn -a domainsqlsvc-account MSSQLSvc/host.domain.com

setspn -a domainsqlsvc-account MSSQLSvc/host

setspn -a domainsqlsvc-account MSSQLSvc/host:1433

OR

setspn -a domainsqlsvc-account MSSQLSvc/host:instanceName

setspn -a domainsqlsvc-account MSSQLSvc/host:<TCPPORT>

setspn -a domainsqlsvc-account MSSQLSvc/host.domain.com:instanceName

setspn -a domainsqlsvc-account MSSQLSvc/host.domain.com:<TCPPORT>

IIS

setspn -a domainapppool-account http/mywebsitehost

setspn -a domainapppool-account http/mywebsitefqdn.com

The SQL SPNs will be automatically created if (and only if) the account it is running under has permissions to create the SPNs (which it attempts to do on start up).  In most scenarios this will not be the case, so you can manually add them in as above.

Make sure you check for duplicate SPNs (any duplicates will stop Kerberos Authentication from working):

setspn -x

See the following MSDN article for more details:

https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

IF USING SQL SERVER NAMED INSTANCES…

By default SQL Server NAMED INSTANCES allocate a TCP port dynamically, so creating the SPN by hand is tricky.  There are two options:

1. Set a static port (recommended when using clusters)

2. Grant the SQL service account permissions to create the SPNs itself when the service starts up

The latter option requires an edit via AdsiEdit.msc as follows:

• Expand the domain you are interested in

• Locate the OU where the service account resides

• Right click on the CN=<service account name> and click Properties

• Click on the Security tab and click Advanced

• In Advanced Security Settings dialog box select SELF under Permission Entries

• Click Edit, select the Properties tab

• Scroll down and tick Allow against:

  • Read servicePrincipalName

  • Write servicePrincipalName

See the following KB article for more information: http://support.microsoft.com/kb/319723

Step 2 – Check Kerberos between IIS and SQL Server (A)

Restart IIS and access a page which causes some database traffic to hit SQL Server (A).  You can run the following query on SQL Server (A) to check the authentication method being used by the current active connections:

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections

You should see something like this:

Check the auth_scheme column to see what is being used.  This will tell you if you need to recheck the SPNs.  If you’re still having troubles, fire up a network monitor (e.g. Wireshark) on the IIS server and filter for Kerberos traffic.

Step 3 – Grant Delegation Rights

Once SQL Server (A) has been presented with the Kerberos ticket from IIS, it still won’t be able to use those credentials to contact SQL Server (B) until it is explicitly allowed.  There are two approaches to this: one is to allow the SQL service account to delegate credentials to any service; the more secure way is to use constrained delegation whereby we specify exactly which services this account can delegate credentials to.

Open Active Directory Users & Computers, right click on the SQL service account and choose Properties.  After adding the SPNs (step 1) a new tab will appear called Delegation.

• Select Trust this user for delegation to specified services only

• Use Kerberos only

• Click Add, enter the SQL service account name and select both sets of SPNs added

• Click OK

Step 4 – Grant SQL Permissions

Don’t forget to do this – the account used by the IIS Application Pool needs to be given suitable permissions on SQL Server (B).

Step 5 – Enable DTC Options

On both SQL Servers the Distributed Transaction Coordinator needs configuring to allow remote connections.  Open the DTC properties:

• Control Panel

• Administrative Tools

• Component Services

• Computers > My Computer > Distributed Transaction Coordinator

• Right click on Local DTC and select Properties

• Select the Security tab

• Enable Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound, No Authentication Required

• Click OK

• This causes the DTC Service to be restarted

See this article for more details:

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

Step 6 – Tweak the Stored Procedures / Remote Queries

After getting Kerberos authentication fully working I hit another issue to do with SQL spawning nested transactions on the linked tables.  The exception thrown was:

Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "SERVERXXX". A nested transaction was required because the XACT_ABORT option was set to OFF.

It turns out there’s some more SQL voodoo needed, namely the following statement at the start of each stored procedure we were running:

SET XACT_ABORT ON

That did the trick.  See this SO post for more details:

http://stackoverflow.com/questions/6036357/making-an-entity-framework-model-span-multiple-databases

Firewall Requirements

Both SQL Server need an inbound allow rule for the Distributed Transaction Coordinator to execute.  This can be done by enabling the predefined rule in Windows Firewall for Distributed Transaction Coordinator (TCP-In):

This is usually disabled by default.

Troubleshooting

Enable Kerberos Logging: http://support.microsoft.com/kb/262177