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:

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

https://twitter.com/awsaxton

Comments

• Anonymous
  March 09, 2010
  Hi Adam We do use the "Write servicePrincipalName" privilege on our clusters and have multiple DCs. So I'm a bit concerned about your caveat. What's the recommendation for creating SPNs for the clusters then? All manual at server build? Traditionally we've configured them to use dynamic port allocation, but I've recently changed the standard build port to be a static 1433 on all instances so we can use database aliases to provide indirection. Essentially it means the manual step is less of a problem now, but if we're exposed to a potential issue I'd like to fix it now rather than have it happen. Inceidentally, we regularly get KRB_AP_ERR_MODIFIED kerberos events on the clusters supposedly from identical host names, which isn't the case. Hsan't caused any failures, but is this a symptom of the WriteSPN? Thanks Ewan

• Anonymous
  March 10, 2010
  Thank you so much for the detailed information on the new formats of SPN's in SQL 2008. Saved a lot of my time.

• Anonymous
  March 11, 2010
  @Ewan The issue that we've seen with clusters is that sometimes you can end up with no SPN when doing Automatic SPN Registration.  Which will result in connection failures.  I haven't been able to repro it internally and it is not an issue that is always there.  Purely a timing issue based on AD Replication.  So, your configuration may work perfectly fine, other times it could result in a ping pong of the cluster nodes, or just an inability of clients being able to connect to the cluster. My recommendation is to set everything statically to avoid the situation.  But, as I mentioned above, your server may be perfectly fine and never see a problem with it.  We have had several calls on it though. Thanks, Adam

• Anonymous
  June 24, 2010
  Excellent post. I have created a free tool to download that helps you document the information that you suggest above for various BI products (SharePoint, PerformancePoint, SSRS, SSAS, ProClarity, ...).  You can download the tool at: http://futuresults.com   Not only can the tool help keep track of this information, but it can also suggest SPNs, check for duplicate SPNS, ... in a constrained delegation approach. I also have created multiple blog posts that go through the process that you list above for the various BI products.  You can find the index for the blog posts at: http://bit.ly/bD5ylD Finally, you may also have additional client checks or troubleshooting needs for Kerberos.  I have a series of blog posts on this that you can find at: http://bit.ly/b1f4jR   In general, I have some additional information on Kerberos and SPNs that you can check out at futuresults.spaces.live.com   You can follow me at: http://twitter.com/FUTURESULTS

• Anonymous
  February 21, 2011
  Hi Adam, If I have a Win 2K8R2 with a SQL 2K8R2 FailOver Cluster should I register my SPNs for both the clustered name and the node names? Should I register the SPNs for both nodes or just the active one? Is it recomended that I enable Auto Registering of SPNs for a FailOver Cluster? Kind Regards, Gabriel