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.
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.
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
Now here is the summarised checklist which I will elaborate later,
- Turn on logging for both successful and unsuccessful login attempts on both servers to be able to diagnose issues if any.
- SetSPN for SQL services with the accounts.
- 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.
- 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
- Linked server configuration should exist based on Windows authentication
- 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.
- Peek into server logs on both machines as well to confirm all is well.
To be continued…