Kerberos – getting it to work.

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Sometimes you want to do double-hop querying in SQL Server, for example if you add a linked server connection ot your server and you want to query objects on that server from your client. Now, if you run integrated security (native mode) and you want your credentials to validate you from the client over the server and to the linked server, you will have to enable Kerberos.

I think that many have had problems with this (as I frequently see special accounts set up in the linked server definition). Here I put together a short recipe for those of you to try at home (or at work).

Step 1: Active Directory settings (http://support.microsoft.com/kb/917409):

  1. All accounts (users and computers) must belong to the same directory/forest.
  2. The user account must NOT be specified as a sensitive account (not for delegation).
  3. The user account must be specified as “trusted for delegation”.
  4. Also, all involved computers must be “trusted for delegation”.

Step 2: Run cmd command “setspn” (assuming you are running the listener on the standard port 1433) (The following may look like the same lines repeated, but trust me, you need all of them!!)

  1. SETSPN -A MSSQLSvc/<NetBIOS servername> <sql service account>
  2. SETSPN -A MSSQLSvc/<NetBIOS servername>:1433 <sql service account>
  3. SETSPN -A MSSQLSvc/<FQDN servername> <sql service account>
  4. SETSPN -A MSSQLSvc/<FQDN servername>:1433 <sql service account>

In my home network NYDOMAIN where my sql server is named NYSTROMSQL.nydomain.local and the sql service account is NYDOMAINsqlservice the commands would thus be as follows: (run in CMD shell)

  1. SETSPN -A MSSQLSvc/NYSTROMSQL NYDOMAINsqlservice
  2. SETSPN -A MSSQLSvc/NYSTROMSQL:1433 NYDOMAINsqlservice
  3. SETSPN -A MSSQLSvc/NYSTROMSQL.nydomain.local NYDOMAINsqlservice
  4. SETSPN -A MSSQLSvc/NYSTROMSQL.nydomain.local:1433 NYDOMAINsqlservice

So now you´re done! Everything is working…or is it?

Well, you can easily verify that Kerberos validation is active by executing the following query:

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

Answer:

  • “KERBEROS” <= YES, you´re all set!
  • “NTLM” <= NO, it´s still not working.

I will return next week with what to do if you receive “NTLM”, good luck!

//Joakim Nyström

Läs liknande inlägg