Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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

Posted by Simon Cho on 04/26/2011

http://support.microsoft.com/kb/909801/

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:

MSSQLSvc/FQDN:tcpport

NoteFQDN 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:

892777 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 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: