I'm sure there was or will be a time when one might have to configure Kerberos authentication between SQL servers to overcome the double hop issue. We had the pleasure of doing so last week. Let me give a brief description of our environment.
Client: A normal windows 7 machine with SQL server management studio.
SQL Server (Hop 1): A named instance of SQL server 2012.
Linked SQL Server (Hop 2): A named instance of SQL server 2008 R2.
We use only windows authentication on the SQL servers and being in an enterprise environment SQL servers run under an active directory service account. As we always use the named instances of SQL server so the servers always use a dynamics port. Now to configure Kerberos authentication all you have to do is perform the following four steps.
1. Allow the service accounts / SQL server computers to set SPN in the active directory.
2. Allow the server computers to be trusted for delegation in active directory.
3. Create the linked server.
4. Restart the SQL servers so that they can set the SPNs(The SPNs can be set manually as well).
The best resource that we could find for setting up Kerberos authentication is from this post on MSDN blog. Including the previously mentioned post, we followed almost every blog/white paper out there on the internet and after performing all the steps, none of it worked. We even used the newly released tool "Microsoft Kerberos Configuration Manager for SQL Server", which showed that everything is fine but still it was not working.
We could not figure out why it was not working until one of my colleague suggested to do the same setup between two SQL servers running same version. So we replaced the middle server (2012) with the one running SQL server 2008 R2 and it worked. so obviously we concluded that there is something wrong with our SQL server running 2012. But still we could not find what's wrong.
It was just by chance that my colleague suggested to change the port under which this SQL was running and it worked. Later investigation found that there was a duplicate entry in active directory for the SPN which was the cause of the issue. So a tip from us, if its not working consider changing the port of sql server.
This posting is provided "AS IS" with no warranties. Use code at your own risk.
