Had a few issues with Kerberos recently. So put some posts together to summaries a few quick and easy things to work through.

Query to check if the SQL Server is using Kerberos:

SELECT
auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid;

Search SQL Server Error Log to see if it’s using Kerberos:

If Kerberos isn’t working you will see something like:

 3-08-07 11:14:22.56 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

2013-08-07 11:14:22.67 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/INT-SQL.wellcomeit.com ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2013-08-07 11:14:22.67 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/INT-SQL.wellcomeit.com:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

If Kerberos is working you will see something like:

2013-08-07 11:22:38.47 spid10s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-08-07 11:22:38.47 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

 

ADSI Edit

You can check if the user account that your using as the service account has any SPNs attached to it via ADSI edit.

Navigate to the service account in ADSI Edit, right click on the account and go to Properties. You will see the below:

adsi adsi2

SETSPN:

To find all the SPNs for a Server (Below query is looking for a server called DEV-SQL:

Setspn -L DEV-SQL

setspn

To find any SPNS registered to a service account (Below query is looking for a server called DEV-SQL_SQL)

Setspn -L dev-sql_sql

setspn2

Advertisements