Service Principal Names (SPN) – A Basic Overview
For kerberos to operate correctly, it needs to make use of SPNs. Service Principal Names are built up to link a domain account to a service on a server.
So if you run your SQL server service on the server called: SERVER01 in the domain: turnip.com using a domain account called TURNIP\SQLservice that domain account needs to have the correct SPN associated with it as below:
setspn -l TURNIP\SQLServicewill return the following:
mssqlsvc/SERVER01.turnip.com:1433(The 1433 is the standard SQL port, you don’t always need this.)
However you need to make sure that this exact SPN doesn’t appear under any other domain account or computer object. For example if you had set the SQL server to run under the “local System” before you had configured it to use this domain account, you’ll probably find this SPN attached to the computer object in AD, i.e. SERVER01. You’d need to remove it from there and add it to the domain account to avoid the duplicate SPN issue.
Example command to add the SPN to the service.
setspn -a mssqlsvc/SERVER01.turnip.com TURNIP\SQLServiceIn Windows 2008 (and R2) you can view the SPNs on an object by opening the object properties in Active Directory Users and Computers (ADUC), then clicking on the tab for the “attributes” and scrolling down until you find the SPN attribute.
Service Principal Names (SPN) – Delegation
Now say we plonk a web site in front of the SQL server, users will connect to this web interface, which then in turn connects them to the SQL server. Because the web server will be passing the credentials on to the SQL server it (the webserver) must be trusted for Kerberos delegation as the connection is now not direct from the client but via the website on the web server.
You could of course make the web site logon to the SQL server using only a service account but that makes it difficult to identify the user and thus causes a security problem, would you want all your users to see all your data, probably not. so….
So first thing is to trust the computer account of the web server in this case: SERVER02. You can do this through ADUC by opening the properties of the computer object.
Once that is done we now need to add an SPN for the HTTP service on the web server. Say in this example the HTTP service runs under the “local system” because of this we don’t add the SPN to a domain account just to the computer object SERVER02.
The following commands are needed to add an SPN for the HTTP service to the SERVER02 computer object.
setspn -a http/SERVER02 SERVER02
setspn -a http/SERVER02.turnip.com SERVER02Note: in this example users would access the website using the name of the server i.e. http://server02 or http://server02.turnip.com.
If the HTTP service runs under the service account: TURNIP\HTTPService then you’d do this:
setspn -a http/SERVER02 TURNIP\HTTPService
setspn -a http/SERVER02.turnip.com TURNIP\HTTPServiceIf you run under a HTTPService account, this would also need to be trusted for delegation just like the computer object SERVER02 (web server) was, as it would needed to be trusted for delegation by the SQL Server.
If you had a CNAME alias on the website called http://lookatme you’d need to add that too:
setspn -a http/lookatme TURNIP\HTTPService
setspn -a http/lookatme.turnip.com TURNIP\HTTPServiceI know its all a lot more complicated than that but this should give you a good overview of it all, some links below will help your studies!
Useful Links
http://blog.sonomapartners.com/2007/04/kerberos_and_de.html
http://blogs.msdn.com/b/spatdsg/archive/2007/11/14/kerberos-delegation-end-to-end-part-i.aspx
