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)).
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
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 AUTHORITY\ANONYMOUS 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 Authority\ANONYMOUS LOGON.
There are six steps in getting this to work:
Configure Service Principal Names (SPNs) for the appropriate services / accounts for SQL and IIS
Check that IIS is authenticating to SQL Server (A) using Kerberos
Grant SQL Server (A) delegation rights for the IIS AppPool Identity account
Grant that account permissions on the SQL Server (B) database as appropriate
Enable DTC Options
Tweak remote queries
The Solution (Steps)
Step 1 – Configure SPNs
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
setspn -a domain\sqlsvc-account MSSQLSvc/host.domain.com:1433
setspn -a domain\sqlsvc-account MSSQLSvc/host.domain.com
setspn -a domain\sqlsvc-account MSSQLSvc/host
setspn -a domain\sqlsvc-account MSSQLSvc/host:1433
setspn -a domain\sqlsvc-account MSSQLSvc/host:instanceName
setspn -a domain\sqlsvc-account MSSQLSvc/host:<TCPPORT>
setspn -a domain\sqlsvc-account MSSQLSvc/host.domain.com:instanceName
setspn -a domain\sqlsvc-account MSSQLSvc/host.domain.com:<TCPPORT>
setspn -a domain\apppool-account http/mywebsitehost
setspn -a domain\apppool-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):
See the following MSDN article for more details:
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:
Set a static port (recommended when using clusters)
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:
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
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:
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
This causes the DTC Service to be restarted
See this article for more details:
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:
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.
Enable Kerberos Logging: http://support.microsoft.com/kb/262177