SQL 2008 SP2 - Kerberos Double Hop - Only working from one node

Question

We have a two node SQL 2008 SP2 cluster that has been setup to use Kerberos delegation. 

Everything seems to have been working fine, however we lost access to the witness disk due to a network failure which trigger an failover. 

We are now running on the other node, but we are now using NTLM and not kerberos for delegation. I have checked all the SPNs and delegation options but i cannot see why this is not working. Both nodes are configured the same way but it just does not seem to want to work from this other node. I have found a similar issue here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a308837d-afb9-4e68-9df9-1c550c652a4a/ 

But this does not apply to our setup as we are using 2008 R2 SP1. I am sure that this has worked previously as we did a DR test no so long ago to ensure we had a plan B. 

Any ideas why this would have stopped working? Or where i can look?

Answer 1

is kerberos not working full stop?  Or is it just the double-hop that's not working?

I guess you may have corrupt SPN's somehow.  You could try deleting them and recreating them using the setspn utility.  Or, if able to, i would just delete the SPN's for then node that's not working, restart the services with a privileged account that can auto-register SPN's in AD.  Then double-check the delegation tab for the service account running SQL Server to make sure the correct details are there.

If able, can you post the format of the SPN's for the failing node?

You could use something like kerbtray to look at the tickets associated with the account more closely

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi, Thanks for pointing me in the direction of Kerbtray. not something i have seen before. 

I managed to see that the working servers had stored the kerberos token and were not due to release it for another week or so. I assume the node that fell over had this ticket expire, and of course when i have rebooted, we have cleared the other token. 

So anyway, I also found an article on enabling KERBEROS event loggin (http://support.microsoft.com/kb/262177). Once i had enabled this i could see that the port that the service was using had changed:

So i had originally: MSSQLSvc/{SERVER}.{DOMAIN}:55711

Now the KERBEROS logging was telling me it was trying to get to: MSSQLSvc/{SERVER}.{DOMAIN}:55012 

This is new to me, so i added the SPN to the user account, then used KERBTRAY to purge the current tickets and then it started working. 

Not sure why that port would have changed, will have a dig about. 

Thanks again.