That title’s quite a mouthful, huh! Let’s start by explaining what we’re actually trying to achieve here. Actually, before we start, let’s just say that this paper shows you how I implemented protocol transition and constrained delegation with SQL Server and IIS, and it is not intended to be an exhaustive study of the subject.
We were faced with a problem when developing a new application. In our application we wanted the user to go to an intranet site in order to be able to look up and edit passwords for service accounts, application accounts, and other privileged accounts. We wanted the website to then impersonate the user and connect to the database on their behalf. So instead of the application using SQL Authentication, or using it’s own service account to authenticate with, we wanted to delegate the authentication to the application so that it could log on as who ever logged on to the application.
Constrained delegation refers to the case where you only want to grant an application rights to impersonate users when asking for certain services, as opposed to all services. Protocol transitioning is a short way of saying that you don’t care how the user authenticated with the application. Instead, you will let the application use Kerberos to impersonate the user with another service.
Microsoft has written a great article about this concept, but it is a little hard going:
http://www.microsoft.com/technet/prodtechnol/windowsserver2003
/technologies/security/constdel.mspx
The steps that we took in order to implement the above are as follows:
1. Create a domain user account to run the SQL Server service (s-SQLServer).
2. Use Enterprise Manager to set the SQL Server service account to the above domain user.
3. Create a domain user account to run the IIS application pool (s-AppPool).
4. Create the Service Principal Names
– run setspn -A http/<website FQDN> s-AppPool
– run setspn -A MSSQLSvc/<sqlhost FQDN>:<port> s-SQLServer
<website FQDN> is the fully qualified domain name for the website, e.g. dev.mysite.company.local.
<sqlhost FQDN> is the fully qualified domain name for the sql server machine e.g. sql1.company.local.
<port> is the port that the SQL service runs on. If you are running a SQL cluster then you’ll have to register the MSSQLSvc spn both with and without the :<port>.
5. Use AD Users and Computers to set the delegation properties of s-AppPool
– Locate this user in Users and Computers
– Right click on the user and go to edit the properties (see fig 1)
– Click on the Delegation tab (which only appears if the setspn command has been run, and there is at least one spn still registered to that account)
– Select Trust this user for delegation to specified services only
– Click Add
– Look up the s-SQLServer account to find the MSSQLSvc spn
– Click on the MSSQLSvc spn
– Click Ok (so this is the constrained delegation bit done)
– Select Use any authentication protocol
– Click Ok (and this is the protocol transition feature)
Fig 1: sample account properties