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:
- AD computer object Server01.contoso.com contains an SPN attribute: "MSSQLSvc/SERVER01.contoso.com:1433" (for tcp connections)
- AD computer object Server01.contoso.com contains an SPN attribute: "MSSQLSvc/SERVER01.contoso.com" (for named pipes connections)
- AD user object SqlSvcAcct01.contoso.com contains an SPN attribute: "MSSQLSvc/SERVER01.contoso.com:1433" (for tcp connections)
- 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
- Anonymous
March 15, 2017
On all of my browsers, the right half of the page is chopped off.