To use Kerberos authentication, you must make sure that all the following conditions are true:
- Both the server and the client computers must be members of the same Windows domain or members of trusted domains.
- The server's service principal name (SPN) must be registered in the Active Directory directory service.
- The instance of SQL Server 2005 must enable the TCP/IP protocol.
- The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client's protocol order. Or you can add the prefix "tcp:" in the connection string to specify that the connection will use the TCP/IP protocol.
How to register an SPN in a domainWhen you register an SPN for a SQL Server service, you essentially create a mapping between an SPN and the Windows account that started the server instance service.
You must register the SPN because the client must use a registered SPN to connect to the server instance. The SPN is composed by using the server’s computer name and the TCP/IP port. If you do not register the SPN, the SSPI cannot determine the account that is associated with the SPN. Therefore, Kerberos authentication will not be used.
When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN in the following format when the instance starts:
Because the TCP port number is included in the SPN, SQL Server must enable the TCP/IP protocol for a user to connect by using Kerberos authentication. The same rules apply for clustered configurations. Additionally, if the instance automatically registered an SPN when the instance started, the SPN will be unregistered automatically when the instance stops.
Only a domain administrator account or the local system account has the required permissions to register an SPN. Therefore, if the SQL Server service is started under a non-administrator account, SQL Server cannot register the SPN for the instance. This behavior will not prevent the instance from starting. However, the following message will be logged in the Application log of the Windows event log: If this message is logged, you must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication. To register the SPN, you can use the SetSPN.exe tool that is included with the Microsoft Windows 2000 Server Resource Kit. This tool is also included with the Microsoft Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Microsoft Windows Server 2003 Service Pack 1 (SP1).
For more information about how to obtain the Windows Server 2003 Service Pack 1 Support Tools, click the following article number to view the article in the Microsoft Knowledge Base:
How to make sure that you are using Kerberos authenticationAfter you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
- Registration of Service Principal Name
- How to enable Kerberos authentication including SQL Server virtual servers on server clusters
Article ID: 909801 - Last Review: Mar 29, 2017 - Revision: 2