Double hop kerberos problem. (the dreadful impossible issue)

 

 

 

Double hop scenario that occurs usually when you have linked servers and / or try to execute a multi server command \ query from an external client machine as depicted in the diagram below.

 

Double hop simple

 

Like all demons of the past, once in  a while as  a DBA / Dev you would come across this issue where you want to execute a multi server command and your credentials do not carry over from one machine to the other causing the double hop anomaly.

This problem you should have no problem in admitting is a tough one at times as it involves AD, networking and SQL config i.e. linked servers which in itself is sometimes can be a bit of a mess.

Yoda

I show you what (has recently) work for me and “Find way your own, Must!”

 

First of all the kind of error message you will see will suggest that there is a “Anonymous logon” related issue.

Logon failed for null user

Logon failed for NT AUTHORITY\ANONYMOUS user

etc.

Now here is the summarised checklist which I will elaborate later,

  1. Turn on logging for both successful and unsuccessful login attempts on both servers to be able to diagnose issues if any.
  2. SetSPN for SQL services with the accounts.
  3. In AD, Both servers need to have delegation turned on. The way it worked for me was via “Trust this computer for delegation to any service(Kerberos only)”. Cant think of a reason it shouldnt work otherwise but since its a bit unpredictable at times I am sharing what I did yesterday. By all means try more secure methods.
    double hop kerb machine config delegation
  4. In AD, SQL Service accounts on both machines need to have delegation on. Next as follows. In the pane below labelled as “Services to which…” press add to include (Now this is really important so pay attention) the Other machine from the one you are configuring, i.e. if doing for machine A.. press “Add…” to search and put in here SPN’s for SQL Service on machine B
    double hop kerb account config
  5. Linked server configuration should exist based on Windows authentication
  6. One (or Couple) of reboots later of servers or atleast SQL Service recycles later and after (if need be) purging Kerberos tickets on client machine it should work and you should be able to  run multi server queries.
  7. Peek into server logs on both machines as well to confirm all is well.

 

To be continued…

Advertisements
About

IT professional with wider interest in technology

Tagged with: ,
Posted in sql, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: