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
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
- 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:
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 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.
information, see Help and Support Center at
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:
Windows Server 2003 Service Pack 1 Support Tools
You can use a command that is similar to the following to
register an SPN for an instance:
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
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
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
For more information, see the following topics in Microsoft SQL Server
2005 Books Online
- Registration of Service Principal Name
- How to enable Kerberos authentication including SQL Server
virtual servers on server clusters