Registering a Service Principal Name
A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. The Kerberos authentication service can use an SPN to authenticate a service. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.
Note
The information that is provided in this topic also applies to SQL Server configurations that use clustering.
Windows Authentication is the preferred method for users to authenticate to SQL Server. Clients that use Windows Authenticated are authenticated by either using NTLM or Kerberos. In an Active Directory environment, Kerberos authentication is always attempted first. Kerberos authentication is not available for SQL Server 2000 and SQL Server 2005 clients using named pipes.
Permissions
When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.
The KB article, How to use Kerberos authentication in SQL Server, contains information about how to grant read or write permission to an SPN for an account that is not a Domain Administrator.
Additional information is available at How to Implement Kerberos Constrained Delegation with SQL Server 2008
SPN Formats
Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances are as follows.
Named instance
MSSQLSvc/FQDN:[port**|**instancename], where:
MSSQLSvc is the service that is 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
MSSQLSvc/FQDN:port**|**MSSQLSvc/FQDN, where:
MSSQLSvc is the service that is being registered.
FQDN is the fully qualified domain name of the server.
port is the TCP port number.
The new SPN format does not require a port number. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos.
Note
In the case of a TCP/IP connection, where the TCP port is included in the SPN, SQL Server must enable the TCP protocol for a user to connect by using Kerberos authentication.
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.
Manual SPN Registration
To register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. These tools are included in Windows Server 2003 Service Pack 1 (SP1). For more information, see the Windows Server 2003 Service Pack 1 Support Tools KB article.
Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account's default SPNs, and add or delete supplemental SPNs.
The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection.
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
Note If an SPN already exists, it must be deleted before it can be reregistered. You do this by using the setspn command together with the -D switch. The following examples illustrate how to manually register a new instance-based SPN. For a default instance, use:
setspn –A MSSQLSvc/myhost.redmond.microsoft.com accountname
For a named instance, use:
setspn –A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname
Client Connections
User-specified SPNs are supported in client drivers. However, if an SPN is not provided, it will be generated automatically based on the type of a client connection. For a TCP connection, an SPN in the format MSSQLSvc/FQDN:[port] is used for both the named and default instances.
For named pipes and shared memory connections, an SPN in the format MSSQLSvc/FQDN:instancename is used for a named instance and MSSQLSvc/FQDN is used for the default instance.
Using a service account as an SPN
Service accounts can be used as an SPN. They are specified through the connection attribute for the Kerberos authentication and take the following formats:
username@domain or domain\username for a domain user account
machine$@domain or host\FQDN for a computer domain account such as Local System or NETWORK SERVICES.
To determine the authentication method of a connection, execute the following query.
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
Authentication Defaults
The following table describes the authentication defaults that are used based on SPN registration scenarios.
Scenario |
Authentication method |
|---|---|
The SPN maps to the correct domain or built-in account. For example, Local System or NETWORK SERVICE. .gif "Note") Note
Correct means that the account mapped by the registered SPN is the account that the SQL Server service is running under.
|
Local connections use NTLM, remote connections use Kerberos. |
The SPN is the correct domain or built-in account. Note
Correct means that the account mapped by the registered SPN is the account that the SQL Server service is running under.
|
Local and remote connections use Kerberos. |
The SPN maps to an incorrect domain or built-in account |
Authentication fails. |
The SPN lookup fails or does not map to a correct domain or built-in account, or is not a correct domain or built-in account. |
Local and remote connections use NTLM. |
Comments
The Dedicated Administrator Connection (DAC) uses an instance name based SPN in SQL Server 2008. Kerberos authentication can be used with a DAC if that SPN is registered successfully. As an alternative a user can specify the account name as an SPN.
If SPN registration fails during startup, this failure is recorded in the SQL Server error log, and startup continues.
If SPN de-registration fails during shutdown, this failure is recorded in the SQL Server error log, and shutdown continues.
See Also