In the past, when I had bare knowledge of Active Directory, I dealt with double hop queries using SQL Server Authentication.

What is the problem with SQL Authentication? Maintenance. The DBA team can literally spend hours maintaining usernames/passwords, linked servers, and of course giving appropriate grants/roles to a new user added to several database instances. There are also security concerns which is outside the scope of this post.

So what is a double hop query? It is a query ran from Machine A that requests data from machine B that at the same time requests data from Machine C.

For example:

If I execute a query on my local machine (A) connecting to Server1 (B) that requests data from Server2 (C):

The solution: Active Directory. A user can be added to the DBA Domain Group, and that group can be granted the sysadmin role on every production database server for example. Once that is defined, we can add/remove members of the group without even touching the database instances.

Now, there is a consensus of having each database instance use its own Active Directory Account as SQL Server Service user. Some shops have 1 account, others a few. It depends on the policy adopted.

Our challenge: Allow double hop queries between 2 database instances running different Active Directory Accounts.

The solution was similar to what we have done with 1 single account.

This is how me made it work for 1 account:

Prerequisites:

1. You need domain admin or access to Active Directory under the domain managed
2. The person in charge of making the change has access to the domain controller or has Remote Server Administration Tools installed locally.

Steps:

• Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.

• For clustered solutions, run it with and without port number. It does not matter if it is a named instance.

• This needs to be repeated for all the SQL Server Instances that you need to trust delegation
• Open Active Directory Users and Computers, search for the domain account just used (DOMAIN\srvc_sqlaccount) in the example, double click and open the properties window.
• Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the account used (e.g. srvc_sqlaccount), select the machine(s) you want to trust delegation, OK, OK.

This is how we made it work with 2 accounts:

You need to follow the same exact process, but when enabling Kerberos delegation, you need to open the properties of the first domain account and add Kerberos Delegation to the second domain account.

Assuming non-clustered instances:

Steps:

• For the first account and the first instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.

• For the second account and the second instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.

• Open Active Directory Users and Computers, search for the first domain account (DOMAIN\srvc_sqlaccount_1) in the example, double click and open the properties window.
• Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the first account used (e.g. srvc_sqlaccount_1)
• Select the machine you want to trust delegation (server1.domain.com in the example), OK, OK.
• Click Add again, User and Computers, type in the second account used (e.g. srvc_sqlaccount_2)
• Select the machine you want to trust delegation (server2.domain.com in the example), OK, OK.
• Wait enough time for the Domain Controllers to propagate the changes (15 minutes should be fine)

Verify Logins are Connecting Via Kerberos:

Run the following query, paying special attention to the auth_scheme column. If Kerberos authentication was successful, you should see the auth_scheme reflect Kerberos instead of NTLM:

Now we can have multiple Domain Accounts for multiple SQL Database Instances, each one delegating for double hop.

Hope this helps.