What SPN do I use and how does it get there?
This month has turned into another Kerberos Month for me. I had an email discussion regarding SPN’s for SQL Server and what we can do to get them created and in a usable state. I thought I would share my response to the questions as it will probably be helpful for someone. Here was the comment that started the conversation. And, by the way, this was actually a good question. I actually see this kind of comment a lot in regards to SPN placement. Not necessarily the setup aspect of it, but for SPN’s in general.
“In prior versions of setup we used to be able to specify the port number for the default and Named Instance. Now, (SQL 2008 & R2) it takes the defaults. 1433 and Dynamic for Named Instances.
If you want to use Kerberos with TCP, you need to know the port number to create the SPN. For Default instances, if you’re using 1433 then you’re ok. But, Named Instances listen on a dynamic port by default, and since you can’t set the port number, any SPN you create will probably be wrong and Kerberos won’t work. It would be great if we could ask the user if they want to change the port number during setup, like we did with SQL 2000.”
Let’s have a look at Books Online first.
Registering a Service Principal Name
https://msdn.microsoft.com/en-us/library/ms191153.aspx
This article goes through the different formats that are applicable to SQL 2008 (they are the same for R2 as well). It also touches on two items that are important to understand. 1. Automatic SPN Registration and 2. Client Connections. Here is the excerpt from the above article in regards to Automatic SPN Registration.
Automatic SPN Registration
When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.
For other connections that support Kerberos the SPN is registered in the format MSSQLSvc/<FQDN>:<instancename> for a named instance. The format for registering the default instance is MSSQLSvc/<FQDN>.
Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.
What does this mean? It means that if the SQL Service account is using Local System or Network Service as the logon account, we will have the permission necessary to register the SPN against the Domain Machine Account. By default, the machine accounts have permission to modify themselves. If we change this over to a Domain User Account for the SQL Service account, things change a little. By default a Domain User does not have the permission required to create the SPN. So, when you start SQL Server with a Domain User Account, you will see an entry in your ERRORLOG similar to the following:
2010-03-05 09:39:53.20 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
This permission is called “Write servicePrincipalName” and can be altered through an MMC snap in called ADSI Edit. For instructions on how to modify this setting, refer to Step 3 in the following KB Article. WARNING: I do NOT recommend you do this on a Cluster. We have seen issues with this causing connectivity issues due to Active Directory Replication issues if more than one Domain Controller is used in your environment.
How to use Kerberos authentication in SQL Server
https://support.microsoft.com/kb/319723
So, if I enable that permission, lets see what the SQL Service does. I have two machines I’m going to use for this. ASKJCTP3 (running the RC build of 2008 R2) and MySQLCluster (SQL 2008 running a Named Instance called SQL2K8).
SetSPN Details:
SPN's with TCP and NP enabled on Default Instance:
C:\>setspn -l sqlservice
Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:
MSSQLSvc/ASKJCTP3.dsdnet.local:1433
MSSQLSvc/ASKJCTP3.dsdnet.local
SPN's with only NP enabled on Default Instance:
C:\>setspn -l sqlservice
Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:
MSSQLSvc/ASKJCTP3.dsdnet.local
SPN's with TCP and NP enabled on Clustered Named Instance:
C:\>setspn -l sqlservice
Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:
MSSQLSvc/MYSQLCLUSTER.dsdnet.local:54675
MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8
SPN's with only NP enabled on a Clustered Named Instance:
C:\>setspn -l sqlservice
Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:
MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8
Lets look at what the client will do. When I say client, this could mean a lot of different things. Really it means an Application trying to connect to SQL Server by way of a Provider/Driver. NOTE: Specifying the SPN as part of the connection is specific to SQL Native Client 10 and later. It does not apply to SqlClient or the Provider/Driver that ships with Windows.
Service Principal Name (SPN) Support in Client Connections
https://msdn.microsoft.com/en-us/library/cc280459.aspx
MSSQLSvc/fqdn
The provider-generated, default SPN for a default instance when a protocol other than TCP is used.
fqdn is a fully-qualified domain name.
MSSQLSvc/fqdn:port
The provider-generated, default SPN when TCP is used.
port is a TCP port number.
MSSQLSvc/fqdn:InstanceName
The provider-generated, default SPN for a named instance when a protocol other than TCP is used.
InstanceName is a SQL Server instance name
Based on this, if I have a straight TCP connection, the Provider/Driver will use the Port for the SPN designation. Let’s see what happens when I try to make connections using a UDL file. For the UDL I’m going to use the SQL Native Client 10 OleDb Provider. Starting with SNAC10, we can specify which SPN to use for the connection. This provides us some flexibility when we control how the application is going to connect. Note: This is not available with the Provider/Driver that actually ship with Windows. I also will show what the Kerberos request looks like in the network trace. This will show us, what SPN is actually being used. All of these connection attempts were made using ASKJCTP3 which is a Default Instance.
Being this is a Default Instance, I added the Instance Name SPN manually.
C:\>setspn -l sqlservice
Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:
MSSQLSvc/ASKJCTP3.dsdnet.local:MSSQLSERVER
MSSQLSvc/ASKJCTP3.dsdnet.local:1433
MSSQLSvc/ASKJCTP3.dsdnet.local
MSSQLSvc/MYSQLCLUSTER.dsdnet.local:54675
MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8
Straight TCP with no SPN Specified:
![clip_image002[5]](https://msdntnarchive.blob.core.windows.net/media/TNBlogsFS/BlogFileStorage/blogs_msdn/psssql/WindowsLiveWriter/WhatSPNdoIuseandhowdoesitgetthere_765B/clip_image002%5B5%5D.jpg "clip_image002[5]")
58 1.796875 {TCP:7, IPv4:5} 10.0.0.3 10.0.0.1 KerberosV5 KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/askjctp3.dsdnet.local:1433
TCP with specifying an SPN for the connection:
32 1.062500 {TCP:11, IPv4:5} 10.0.0.3 10.0.0.1 KerberosV5 KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/ASKJCTP3.dsdnet.local:MSSQLSERVER
Forcing Named Pipes with no SPN specified:
68 1.828125 {TCP:21, IPv4:5} 10.0.0.3 10.0.0.1 KerberosV5 KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/askjctp3.dsdnet.local
The way the provider/driver determines which SPN to use is based on the Protocol being used. Of note, starting in SQL 2008 we allowed for Kerberos to be used with Named Pipes. If you have a Named Instance and you are using the Named Pipes protocol, we will look for an SPN with the Named Instance specified. For a Default Instance and Named Pipes, we will just look for the SPN with no port or Named Instance Name specified as shown above.
With the ability to specify the SPN from the client side, you can see how you can easily manipulate, or even see how we will determine what SPN will be used.
Now that we know all of the above, lets go back to the original question. Your company may or may not want to enable the Write permission for the Domain User Account. If your company is not willing to open up the permission on the service account, then their only recourse will be to set a static port for the Named Instance instead of letting the Named Instance use a dynamic port. This would also be my recommendation for Clusters. In this case, you will need to know exactly what SPN’s are needed and create them manually using SetSPN or tool of your choice.
Even though we don’t provide the ability to set your port during setup, you can still modify the port settings for the Instance through the SQL Server Configuration Manager. This will allow you to set your static SPN’s as well as assist you with Firewall rules.
Adam W. Saxton | Microsoft SQL Server Escalation Services