15 Jan 2009

SQL Server 2005 SPNs

When running SQL Server 2005 services under normal domain user accounts, there's some additional configuration that's needed to have SQL auto register it's SPN which is required for Kerberos authentication.

If SQL is unable to register an SPN upon startup you'll see "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15." in the SQL Error logs. If the SPN can't be registered then the authentication will fall back to NTLM instead of Kerberos.

Step 3 in the following article describes the steps that i performed on the SQL Server service account to enable the self registration of the SPNs. - http://support.microsoft.com/kb/319723

The following TSQL can be ran from SSMS to determine the current authentication method (Kerberos or NTLM).

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

Also more info can be found here - http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx