Duplicate SPN: What is it really?


If you look at many of the references regarding Kerberos troubleshooting, one of the common recommendations is to verify duplicate service principal names (SPNs) do not exist. I mention this myself in the SQL Server Kerberos and SPN Field Guide here. But what do we really mean by that?

 

SPN is a String Attribute of an AD Object

First, it is important to understand what an SPN is. Essentially it is a string attribute(property) of an Active Directory object, such as a computer object or a user object (for a more detailed explanation see section What is an SPN? here). So, based on this definition:

  • An AD computer object, such as Server01.contoso.com, could contain an SPN attribute: “MSSQLSvc/SERVER01.contoso.com:1433
  • An AD user object, SqlSvcAcct01.contoso.com, could contain an SPN attribute: “MSSQLSvc/SERVER02.contoso.com:1433

 

What is a duplicate SPN?

Each SPN attribute should be a unique string. This is because when a Kerberos connection is made the client essentially queries active directory based on this string (for a more detailed explanation see section How is an SPN used? here) .  So, when a client wants to make a Kerberos connection to SERVER01, for example, it may query active directory for an SPN “MSSQLSvc/SERVER01.contoso.com:1433”. Problems arise if that same SPN string attribute is found on multiple active directory objects. So, if  an AD computer object, such as Server01, contains an SPN attribute “MSSQLSvc/SERVER01.contoso.com:1433” and another AD object, such as SqlSvcAcct01 also contains that same SPN string attribute “MSSQLSvc/SERVER01.contoso.com:1433” , then this is a situation where a duplicate SPN exists.

Look at the following output of the SETSPN -L command. If the startup account for SQL Server is CONTOSO\SqlSvcAcct01, which SPNs should remain and which should be removed? How many duplicate SPN’s are present below?

 C:\>setspn -L SERVER01
 Registered ServicePrincipalNames for CN=SERVER01,DC=contoso,DC=com:
        MSSQLSvc/SERVER01.contoso.com:1433
        MSSQLSvc/SERVER01.contoso.com
 

 

 C:\>setspn -L SQLSVCACCT01
 Registered ServicePrincipalNames for CN=SQLSVCACCT01,DC=contoso,DC=com: 
        MSSQLSvc/SERVER01.contoso.com:1433
        MSSQLSvc/SERVER01.contoso.com

 

Based on the output above:

  1. AD computer object Server01.contoso.com contains an SPN attribute: “MSSQLSvc/SERVER01.contoso.com:1433” (for tcp connections)
  2. AD computer object Server01.contoso.com contains an SPN attribute: “MSSQLSvc/SERVER01.contoso.com” (for named pipes connections)
  3. AD user object SqlSvcAcct01.contoso.com contains an SPN attribute:  “MSSQLSvc/SERVER01.contoso.com:1433” (for tcp connections)
  4. AD user object SqlSvcAcct01.contoso.com contains an SPN attribute:  “MSSQLSvc/SERVER01.contoso.com” (for named pipes connections)

Since the startup account of the SQL service is the domain account SqlSvcAcct01, the SQL SPN attributes should exist for the AD user object SqlSvcAcct01.contoso.com and not for the AD computer object Server01.contoso.com. So #1 and #2 should be deleted and #3 and #4 should remain. The output shows there are two duplicate SQL SPNs present (#1 is a duplicate of #3, and #2 is a duplicate of #4)

You can also find a duplicate SPN by running SETSPN -X. In the scenario described here, it would return the following output:

 MSSQLSvc/SERVER01.contoso.com:1433 is registered on these accounts:

 CN=SERVER01,DC=contoso,DC=com — should be removed

 CN=SQLSVCACCT01,DC=contoso,DC=com –this is ok

 

 

 MSSQLSvc/SERVER01.contoso.com is registered on these accounts:

 CN=SERVER01,DC=contoso,DC=com— should be removed  

 CN=SQLSVCACCT01,DC=contoso,DC=com –this is ok

 

To remove the invalid duplicate SQL SPNs, you would need to run the following commands (this removes the SQL SPN string attribute from the SERVER01 computer object):

 Setspn –D  MSSQLSvc/SERVER01.contoso.com   SERVER01
 Setspn –D  MSSQLSvc/SERVER01.contoso.com:1433   SERVER01
 


 

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services

Comments (0)

Skip to main content