How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
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:
Event Type: Information
Event Source: MSSQL$InstanceName
Event Category: (2)
Event ID: 26037
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
For more information, see Help and Support Center at http://support.microsoft.com.
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: 10/02/2006 15:32:17 - Revision: 2.3
- kbsql2005connect kbinfo KB909801