How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 (909801)

MORE INFORMATION

SQL Server 2005 supports Kerberos authentication indirectly through the Windows Security Support Provider Interface (SSPI) when you are using Windows integrated authentication instead of SQL authentication. However, SQL Server will only use Kerberos authentication under certain circumstances when SQL Server can use SSPI to negotiate the authentication protocol to use. If SQL Server cannot use Kerberos authentication, Windows will use NTLM authentication. For security reasons, we recommend that you use Kerberos authentication instead of NTLM authentication. Administrators and users should know how to make sure that they are using Kerberos authentication for remote connections.

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 domain

When 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: Note FQDN is the fully qualified domain name of the server. tcpport is the TCP/IP port number.

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
Date: Date
Time: Time
User: N/A
Computer: ComputerName
Description:
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. 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: You can use a command that is similar to the following to register an SPN for an instance:Note If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.

How to make sure that you are using Kerberos authentication

After 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

If SQL Server is using Kerberos authentication, a character string that is listed as "KERBEROS" appears in the auth_scheme column in the result window.