The majority of the time, the problems I see with Kerberos are due to a bad SPN (Service Principal Name) configuration. So in this post we'll talk about what an SPN contains and how it should look. An SPN contains several pieces of information:

• The service identifier (this is MSSQLSvc for the Microsoft SQL Server database engine, for instance)
• The NetBIOS or DNS name that should be accessed by the client. This may also include a network port or instance name.
• The account that the service is running under (this could be the computer account)

Books Online covers what this should look like depending on your version of SQL Server: 2005 | 2008 | 2008R2. If you look carefully at the language, you'll note that in the 2008/2008R2 versions of Books Online, there is a note about the fact that the port was dropped for connections that are not TCP/IP based, like Named Pipes and Shared Memory. Therefore, we have different cases as to how we should do the SPNs based on the version of SQL Server we are talking about. The biggest challenge is getting the service + server name/port right, so that's what we'll focus on today. Tomorrow we'll look at using SETSPN to check SPN entries and how to add and remove them.

For 2005:

Default Instance:

• MSSQLSvc/MyServer:1433
• MSSQLSvc/MyServer.MyDomain.com:1433

Named Instance (assuming it's listening on port 9999):

• MSSQLSvc/MyServer:9999
• MSSQLSvc/MyServer.MyDomain.com:9999

If you look at the recent documentation, you'll only see examples with the latter of the two SPNs (MyServer.MyDomain.com). This is the Fully Qualified Domain Name (FQDN) version. You don't see the shorter example (MyServer) which we typically refer to as the NetBIOS name. All the KB articles put out by Microsoft used to have both and in fact, some of the auxiliary products, like SharePoint have docs that still indicate to put in both. I always err on the site of caution, meaning I'll put in both. Is it strictly required? No, not if DNS is set up correctly on your clients (and they should be if you're doing Kerberos).

For 2008 and later:

Default instance:

• MSSQLSvc/MyServer
• MSSQLSvc/MyServer.MyDomain.com

Note that the TCP port isn't required. In fact, if you want to use Named Pipes or Shared Memory, the SPN shouldn't have the port number. With that said, there's nothing wrong with having both, so far as I am aware:

• MSSQLSvc/MyServer
• MSSQLSvc/MyServer:1433
• MSSQLSvc/MyServer.MyDomain.com
• MSSQLSvc/MyServer.MyDomain.com:1433

Named Instance (assuming instance name of Named and port of 9999):

• MSSQLSvc/MyServer:Named
• MSSQLSvc/MyServer.MyDomain.Com:Named

Note the change from port 999 to the instance name of Named. This allows for the Named Pipes and Shared Memory connections. Again, I err on the side of caution:

• MSSQLSvc/MyServer:Named
• MSSQLSvc/MyServer:9999
• MSSQLSvc/MyServer.MyDomain.Com:Named
• MSSQLSvc/MyServer.MyDomain.Com:9999