Setting up delegation between servers for Windows authenticated sites

Recently, I heard that someone was having trouble setting up Windows Server delegation and I remembered that I wrote about it a few times several years ago in 2010. That blog and article does not exist anymore and there is little documentation on the process (most IT professionals will give up and use a different solution), so in the spirit of sharing at this time of year, here it is again.

What is server delegation?

When building a web application you can build your own user administration or use a security mechanism from another system (single-sign-on) or you can use Windows authentication in IIS (there are probably other ways too). For an internal web application where you are using Windows machines on the same domain, Windows authentication in Internet Information Services (IIS) allows you to provide a web application to your users without requiring them to enter (or remember) any credentials. This is because it automatically authenticates them using their Windows credentials.

However, when IIS connects to a SQL Server database on a different server those credentials get lost and SQL Server thinks it is the basic IIS server that is requesting the connection and not the authenticated user. That’s where delegation comes in. It allows the IIS server to pass the credentials of the user to the SQL Server and for the SQL Server to recognise it as a valid user. Note: it is only Internet Explorer on Windows that truly supports delegation although you can reconfigure some of the other browsers to support it.

You may ask; why would anyone want to do this if it might be a problem? At the time of my original article, this question was being frequently asked in online communities and responses range from ‘put them both on the same server’ to ‘use another method like passing the user credentials in code’. A dedicated database server can be optimised for data actions and a dedicated web server can be likewise optimised but a dual purpose server will not provide the same efficiencies. Also, there is a risk if you are operating both on the same server and running multiple web applications that one incorrectly operating web app could compromise or prevent access to all of the apps/databases.

Trust and delegation of authentication credentials between SQL Server and Internet Information Services (IIS) allows you to maintain the credentials of the user that is logged on during database actions. This is great from the database point of view because all actions can be tracked in the database as the actual user and not as an impersonated general system account. You don’t need to modify your Stored Procedures for database actions to accept the user identity or some other credentials because SQL Server will know automatically who is performing the action and can log it accordingly.

Below you will find the original delegation set up instructions that I produced in 2010. There are quite a few screenshots and they might be out of date with newer Windows Servers. I do not know if this will be of use to anyone anymore but I have posted it here on the off chance that it is.

Delegation between Internet Information Services and Microsoft SQL Server

All servers involved in this will have to trust delegation. For me, including in this example, there are two servers involved so both need to be modified. I would have thought only one would need to trust the other but I’m pretty sure when we tried this it never worked.

So we’ll start with the SQL Server side and then move onto the Internet Information Services (IIS) server. I’ve modified the screenshots / text to use a sub-domain of a ficticious example.com domain called “pat”.

SQL Server

In this example the SQL Server name is LONVDEVSQL01 and the service account that SQL Server runs under is a Windows domain account called LONVDEVSQL01-SVC.

From Active Directory make a note of the fully qualified DNS name as you will need this later (lonvdevsql01.pat.example.com). The SQL Server needs to allow delegation in Active Directory by setting “Trust this computer for delegation to any service (Kerberos only)” on the Delegation tab.

SQL Server needs to be running under a network account with delegation enabled. Make sure that “Account is sensitive and cannot be delegated” is unchecked and “Trust this user for delegation to any service (Kerberos only)” is selected on the Delegation tab. The account needs to be a member of Domain Users for the same domain as the server.

You will now need to add a Server Principle Name (SPN) to the network account. Either (a) or (b) below.

a) You can either use the SETSPN tool:
setspn -A MSSQLSvc/LONVDEVSQL01.pat.example.com:1433 EXAMPLE\LONVDEVSQL01
setspn -A MSSQLSvc/LONVDEVSQL01:1433 EXAMPLE\LONVDEVSQL01

b) Or use ADSIEDIT.MSC to add the same two values to the servicePrincipalName attribute of the service account LONVDEVSQL01-SVC.
MSSQLSvc/LONVDEVSQL01.pat.example.com:1433
MSSQLSvc/LONVDEVSQL01:1433

Using SQL Server Configuration Manager alter the protocols to only enable TCP/IP (under SQL Server Network Configuration). The SQL Server needs to be running under the network service account. In the SQL Server Configuration Manager (under SQL Server Services), change SQL Server to run under the network service account (LONVDEVSQL01-SVC). In this example the SQL Server Agent has also been set to run under its own network service account although this is not required for delegation it is good practice to have it running under a different account to the SQL Server service so that any failure (or lock out) from the SQL Server Agents activities does not affect the SQL Server itself. After making all of these changes you will need to at least restart SQL Server but just to be sure you probably want to restart the server itself.

IIS Server

The Web server (LONDEVWEB01 in this example) needs to allow delegation in Active Directory by setting “Trust this computer for delegation to any service (Kerberos only)” on the Delegation tab. This step may not required for the majority of authentication/impersonation scenarios but may as well get it in place for those that do require it.

By default in IIS7 the bare minimum is installed so that there is only enough to allow the welcome page to appear on the default website. We need to install Windows Authentication under the Web Server (IIS) Security section in Server Manager by clicking Add Role Service.

In Internet Information Services (IIS) Manager you can now add your websites and modify the authentication to use the correct method. For a website using Windows Authentication where impersonation of the user credentials or another network account is used we need to enable both Windows Authentication and ASP.NET Impersonation and disable all the methods not in use (especially Anonymous).

If the impersonation uses an account other than the users you can set the username and password by right clicking ASP.NET Impersonation and clicking Edit.

 
On Windows Authentication there is an option to remove Kernal-mode authentication but for our purposes we do not want to do this so we keep the default setting (checked).

It is good practice to run each website in its own application pool so any problems with sites outside of its control do not affect this site. The Identity should be set to Network Service and Load User Profile to True (I don’t know if this last step is actually necessary).

Make sure the website is running under its own application pool (if you have set one up) and then restart the website.
 
If for any reason it does not work then the server may have had Kerberos authentication removed. The best place to check is C:\Windows\System32\inetsrv\config\applicationHost.config. Open it in Notepad and look for the Windows Authentication Providers. Negotiate needs to be above NTLM.

Final note

I hope you have found this useful. Unfortunately, I will not be able to answer questions here on any specific set-up – you should contact an IT consultant if you need help.

Whatever you are doing over the coming festive period, I hope you have a great time.