Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
Article ID: 909801 - View products that this article applies to.
This article describes how to make sure that you are using Kerberos authentication as a Microsoft Windows authentication method when you create a remote connection to an instance of Microsoft SQL Server 2005.
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:
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:
MSSQLSvc/FQDN:tcpportNote 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
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:
892777You can use a command that is similar to the following to register an SPN for an instance:
(http://support.microsoft.com/kb/892777/ )Windows Server 2003 Service Pack 1 Support Tools
SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>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 authenticationAfter you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:
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.
For more information, see the following topics in Microsoft SQL Server 2005 Books Online:
Article ID: 909801 - Last Review: October 2, 2006 - Revision: 2.3