There are a lot of articles out there on setting up Kerberos Service Principal Names but today I’m going to make it simple. Bear with me as I start off with the basics; by the end of the post it will all be very clear.
Throughout this post I’ll make reference to a scenario of a client computer connecting to an SQL server called sql1.domain.com however the same applies for any service, for example a web server where the client connects via HTTP.
The SQL server service is running under a domain service account called “domain\SQLSVC“. No SPNs have been set yet.
The Basics
Active directory user and computer accounts are objects in the active directory database. These objects have attributes. Attributes like Name and Description.
Computer and User accounts are actually very similar in the way they operate on a Windows domain and they both share an attribute called ServicePrincipalName. An account object can have multiple ServicePrincipalName attributes defined.
The setspn.exe tool manipulates this attribute. That’s all it does.
The Failure
The client wants to access the SQL server so he asks his domain controller: “Please may I have a ticket for accessing MSSQLSvc/sql1.domain.com”
Now the domain controller asks the active directory database: “Give me the name of the account object who’s ServicePrincipalName is MSSQLSvc/sql1.domain.com“
The active directory database replies: “Sorry, there are no account objects with that ServicePrincipalName”
So the domain controller asks the active directory database again: “Ok then, give me the account object who’s ServicePrincipalName is HOST/sql1.domain.com“
All computer accounts have, by default ServicePinciaplName attributes set to:
HOST/[computername] and HOST/[computername].[domain]
So the active directory database replies to the domain controller: “The account object that has that ServicePrincipalName is sql1.domain.com’s computer account“
The domain controller now creates a ticket that only the computer account of sql1.domain.com can read. He gives the ticket to the client.
The client goes to the SQL service on sql1.domain.com and says “here is my ticket, may I come in?”
The SQL service will attempt to read the ticket. The problem is, the SQL service is not running under the computer account; it is running under a domain service account. It can not read the ticket; the ticket is only intended for the computer account of sql1.domain.com. Authentication fails (falls backto NTLM).
The Fix
Now lets run the setspn.exe tool to manipulate the ServicePrincipalName attribute of the SQL service account.
setspn -a MSSQLSvc/sql1.domain.com domain\SQLSVC
We will also add sql1 (without the domain name) in case we want to access the the server without the domain name appended.
setspn -a MSSQLSvc/sql1 domain\SQLSVC
Now run through the scenario again and this time notice that the domain controller will return a ticket that the SQL server service account can read.
Obviously this is heavily paraphrased but hopefully it helps you understand the reason for setting the SPN attribute on the account that runs a given service. Of course if the service runs under the local NetworkService or LocalSystem account then everything will just work because these local accounts represent the computer account in active directory.
Hi! I’m very new to this. Thanks for a very helpful post. How does the client know to ask for a MSSQLSvc/xxxx ticket? Is MSSQLSvc some kind of well known predefined service class?
This is the right webpage for everyone who hopes to find out
about this topic. You know a whole lot its almost tough to argue with you
(not that I really will need to…HaHa). You definitely put a fresh spin on a subject that
has been discussed for many years. Great stuff, just excellent!
Still the best explanation on the web!!
Even though this old post but very thankful for explaining in simple terms. There was lot of confusion about this. Thanks for this post.
nice and useful explanation !!
Thanks
Hello there! This blog post could not be written any better!
Looking through this article reminds me of my previous
roommate! He always kept preaching about this. I am going
to send this article to him. Fairly certain he’s going to have a good read.
Thanks for sharing!
Thanks very much Rhys.
That’s what I have done but still getting SSPI errors. Oh well, try again.
Ok.a few more things to think about:
-is there any kerberos delegation involved? What’s actually connecting to your sql server?
-use kerbtray on the client to see what tickets you’re getting
-use wirshark to look at kerberos errors, first at the client then at any other box involved. E.g. app server etc.
Hi Rhys, just found your blog, really usefull.
So when you look at the account domain\SQLSVC using adsi edit you will see the servicePrincipalName property set to MSSQLSvc/sql1 and MSSQLSvc/sql1.domain.com. Is that right?
If you look at the machine account sql1 it will not have an entry for MSSQLSvc/sql1. Is that right?
Yes exactly right. And no it shouldn’t have the same spn registered against the computer account. It can happen though. Duplicate spn issues are reported in domain contoller event logs IIRC but it pays to do a search for servicePrincipalName with a tool like Adexplorer. Especially if it’s not a new install and you’re changing service accounts. I don’t think setspn warns about duplicates. Some apps try to register SPNs them self if they have enough AD permission. I can’t remember what sql server does.