Understanding Service Principal Names (SPNs) in SQL Server
A Service Principal Name (SPN) is a unique identifier assigned to a specific service instance. SPNs play a crucial role in Kerberos authentication by linking a service instance to a service logon account. This mechanism enables client applications to request authentication from the service even if the client does not directly know the service account name.
When multiple instances of a service are installed across different computers in a network (forest), each instance must have its own unique SPN. Additionally, a single service instance can have multiple SPNs if clients may connect using different names or aliases. For instance, since an SPN always includes the host computer's name where the service runs, it’s possible for one service instance to register SPNs for all host names or aliases associated with that machine.
For more detailed information on SPN formats and how to create unique SPNs, refer to Microsoft’s documentation:
Service Principal Names
SPN Formats for SQL Server Instances
-
Named Instance SPN Format:
MSSQLSvc/<FQDN>:[<port> | <instancename>]
Where:
MSSQLSvcis the service being registered.<FQDN>is the fully qualified domain name of the server.<port>is the TCP port number.<instancename>is the name of the SQL Server instance.
-
Default Instance SPN Format:
MSSQLSvc/<FQDN>:<port> OR MSSQLSvc/<FQDN>
Where:
MSSQLSvcis the service being registered.<FQDN>is the fully qualified domain name of the server.<port>is the TCP port number.
Managing SPNs for SQL Server Connection Issues
If you encounter connection problems with SQL Server related to SPNs, you can manage SPNs using the SetSPN command-line utility:
-
To list SPNs for a particular domain account:
SetSPN -L domain\account
-
To create or add a new SPN:
SetSPN -A MSSQLSvc/<SQL Server FQDN>:<port> domain\account
Useful Tool for Troubleshooting Kerberos Issues
Microsoft provides the Kerberos Configuration Manager for SQL Server, a diagnostic utility designed to help identify and resolve Kerberos authentication problems with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services.
You can download the tool here:
Microsoft Kerberos Configuration Manager for SQL Server
