Kerberos AD Delegation

When a separate Web and SQL server are being used with Windows Authentication granting SQL access using the agents AD credentials Kerberos Delegation needs to be enabled.

This allows the agents AD credentials to be passed from the Web Server on to the SQL server. The web server receives them from the agent pc when the website is accessed. It is this passing of user AD credentials by the web server on to a another server that we have to explicitly enable.

http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx

Setup Guide
An Overview of how to set this up in AD is below.
If you hit issues following the Step by Step please check the Troubleshooting section at the bottom of this article.
The below example is using a default SQL port of 1433 and on a default name SQL instance. If you are using a different port or SQL instance name this will need to be substituted in all locations. Generally for a named SQL instance ":[INSTANCENAME]" is appended after the Fully Qualified server name and no port is specified.

In the below example & screenshots we have a web server (web1) and a SQL server (sql1).

In AD for the web server object we have the delegation tab.
We can select to "trust this computer for delegation to specified services only" and choose "Use Kerberos only".
The "Add..." button loads a "Add Services" window.
Under this window select "Users or Computers", and select the sql server.
You will be returned to the "Add Services" window with a list of "Available Services" displayed.
In this list will be the option "MSSQLSvc [SQLserver].DOMAIN] 1433" which should be selected and "OK" clicked.
The Delegation tab will now show the selected service in the "Services to which this account can present delegated credentials:" section
On the web server you will need to force an update of Group Policy for the new setting to take effect. You will also have to wait for the change to be replicated across domain controllers which can take a few minutes depending on your network setup:
1: Open up a CMD window
2: type "gpupdate /force" and press enter
3: wait for the confirmation that the update has completed.
Restart the CS App Pool and website to ensure the changes have filtered through.

Notes

If the agent pc is not on a trusted domain and is thus using Windows Auth in IIS you need to enable "Use any authentication protocol" in the AD Delegation tab rather than "Use Kerberos only".

Troubleshooting
When delegation is not enabled/functioning correctly you will see errors within the CallScripter Event Viewer log reporting that an anonymous user was rejected access to the SQL server.

Confirm SPNs
SPNs are used as part of the delegation granting process and must be present for delegation to function correctly. They are a Microsoft feature and can be checked as briefly detailed below.
Please note that words in capitals surrounded by square brackets ("[NAME]") are place holders and so should be replaced with the name/value of what is within the square brackets and the brackets removed. ie: [USERNAME] = Michael.Hollett

On the web server check it has SPNs correctly by running the command: setspn -f -q */[WEBSERVER]
Check that there are entries for the [WEBSERVER].[DOMAIN] present. These can be suffixed with "WSMAN/", "TERMSRV/", "HOST/" and others.
From the web server check that the SQL server has the correct SPNs by runnign the command: setspn -f -q */[SQLSERVER]
Check that there are entries for: MSSQLSvc/[SQLSERVER].[DOMAIN] present with and without port 1433 (:1433).

Testing SPNs
You can test the result from an SPN by using the below command line query from the Web Server when running as an elevated domain user:
Klist get MSSQLSvc/[SQLSERVER].[DOMAIN]:1433

Note: modern windows security may block this from working as expected as you can onyl see your own key chain entries now, in the past you could see every session from the web server.

Add Missing SPNs

If the SPNs for SQL on the SQL server are not present they can be manually added. Certain Windows/SQL deployment paths can cause this due to how Microsoft's software works.
Microsoft adding SPN for SQL article: http://msdn.microsoft.com/en-GB/library/ms191153(v=sql.105).aspx#manual

Purge Cached Authentication Tickets
On the web server you can purge any cached user credentials from before Delegation was enabled. This should be used with caution as other applications and file access could be running on these.

Open up a CMD window
type "klist tickets" to show the currently cached authentication tokens (tickets).
type "klist purge" to delete all cached tickets. This will force the agent client (agent IE browser window) to re-authenticate on the next interaction with the CallScripter system.

Domain Controllers 2003
If you have 2003 domain controllers in your domain then you may experience issues relating to all user requests being denied Kerberos delegation access. This is due to the 2003 DC's not being able to handle the AES encryption on the requests.
The possible solutions from Microsoft are:

Disable AES (128 & 256) encryption throughout the domain until all 2003 DCs are replaced.
Disable AES (128 & 256) encryption on just the IIS server. This would be more of a trial and error approach.

Further Issues
Further diagnosing resources can be found on the Microsoft site as well as a number of SQL blogs. A resource we have had success in the more problematic situations is below:
http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/

Support Note

Please note that these changes may affect other services/functions outside of CallScripter and so should be carefully considered for your environment before being applied. These are Microsoft issues and resolutions and so outside of the scope of CallScripter's control.

Article ID: 613, Created: January 30, 2014 at 11:56 AM, Modified: November 23, 2021 at 4:58 PM

Share this article