This second Kerberos Basic Troubleshooting post will try to provide some tips and knowledge when setting up Kerberos authentication for SQL services. For starters I will explain which Service Principal Names (SPN’s) can be registered and when they will be called for.

There are three known forms of SPN’s which can be registered for a SQL service account. This service account can either be a user (service account) or a computer (local system) (MSDN: Service Principal Name (SPN) Support in Client Connections)

• MSSQLSvc/FQDN
• MSSQLSvc/FQDN:InstanceName
• MSSQLSvc/FQDN:Port

And here is where the confusion starts: depending on the protocol used to to connect to SQL, other SPN’s are built by the client and being queried to AD. In my line of work I hardly ever encounter the use of “named pipes” or “shared memory”. In 100% of the cases (mostly SQL services for Infrastructure Apps) I see TCP/IP being used. Although I provided 3 options for SPN’s, only one will be used for TCP/IP:

• MSSQLSvc/FQDN:Port

This is also explained in the MSDN: Registering a Service Principal Name article:

User-specified SPNs are supported in client drivers. However, if an SPN is not provided, it will be generated automatically based on the type of a client connection. For a TCP connection, an SPN in the format MSSQLSvc/FQDN:[port] is used for both the named and default instances.

I would just forget the other formats, unless you are using named pipes/shared memory.

Whenever you are using a dynamic port you got a problem. Because each time SQL Services get started, the TPC/IP port can change and hence the SPN can be incorrect. There are two options for this problem, in my opinion the second option is the preferred one:

• Alter the permissions on the service account so it can update it’s own SPNs: grant “SELF” the “Write servicePrincipalName” permission.

• Configure a static port for the SQL Service. A static port can still be combined with the SQL Browser Service. So for applications which do not support entering a fixed SQL Port should be fine as well. An example is shown below: the FIM Portal & Service setup asks for a server and optional Instance, but no port is asked for. You could fool the setup perhaps by providing it anyway using a comma or double point. But having a static port configured and the browser service started will do just fine.

Although I just said to forget the other SPN’s, you might actually use them in some cases. The new SQL 2008 native client has a new feature which allows you to actually configure which SPN the client will query for during authentication. So the client it self will no longer just build the SPN by using FQDN:Port. In this case I does not matter which port SQL is using, you can simply register the SPN MSSQLSvc/FQDN:InstanceName (or something else you like) as long as you provide it in the ODBC connection settings.

I’m quit sure if the ODBC configuration allows this feature It won’t take long for the application installers to allow it. It would be nice to provide an optional SPN when configuring a SQL connection during a regular application setup. This would definitely provide more flexibility.

And now some diagnostic stuff. So you got your new shiny SQL configured, you registered the SPN and installed your application. But who can tell whether your using Kerberos or not? Here are some options:

• Wireshark: the wire never lies. However this requires you to install Wireshark, capture a trace, …
• Security event log: It’s all in there, however there is a lot in there…
• SQL query: It’s just easy!

Start the SQL Management Studio, select the SQL Server in the left pane and click New Query

Provide the following code, select it and press F5 (execute)

The code itself:

SELECT DB_NAME(dbid) AS DatabaseName, loginame AS LoginName, sys.dm_exec_connections.auth_scheme as AuthMethod
FROM sys.sysprocesses
JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
WHERE dbid > 0
GROUP BY dbid, loginame, spid,sys.dm_exec_connections.auth_scheme

And as a result the following information is provided: you can clearly see some of the connection are NTLM and some of them are Kerberos. The connections to the master/msdb being NTLM is just because those are connections on the machine itself. Remote connections will properly use Kerberos when the required SPN’s are registered.

Another reference on the topic: MSDN: How to Implement Kerberos Constrained Delegation with SQL Server 2008