Creating a linked server with integrated authentication

Recently I have been approached by some colleagues from OPS with the following task:

"Create a linked server with integrated authentication"

The source system was SQL2016 SP2 single instance Standard Edition on VM the target system was SQL2012 (not latest SP but somehow close) Enterprise Edition WSFCL on bare metal. Using integrated authentication is a best practice. SQL Logins default to NTLM while connections using Windows Authentication (or better: Integrated Authentication) use Kerberos. Troubleshooting Kerberos is awkward, especially when one does not have access to the Eventlogs of the DCs (like me...). Many articles that deal with this task omit a little but crucial fact that I want to write about here in order to save others the hours I needed to get this linked server running.

Before we get into the SQL config there are some prerequisites that we need to check and make sure they are configured properly for the setup to work. When working with linked servers that take advantage of integrated authentication and we want the linked server to be accessible from any remote SSMS we run into what I learned is called the "Double Hop Problem". We need to configure the AD properly to ensure that SQL1 (source) can pass the credentials to SQL2 (target) via Kerberos. This topic is described in this great article:

So before we start we need to make sure that the firewall ports are open (try to connect to the target from a SSMS installed on source or do a telnet test), that the service account (which should be a domain account, not the local service account NT Service\MSSQL$InstanceName) is trusted for delegation - you do that here:

Then this option under Account must not be set:

Some options are only visible when "Advanced Features" are enabled in the dsa.msc. Once the preparation is done the rest is (or better: should be...) straight forward. The service account of SQL1 needs to have CONNECT on SQL2 (thus the login needs to be created), then the linked server object is created in the following way:

Leave the Server Options as they are and press OK - this should normally work - but it didn't for me. I always got this error:

What did the trick for me: instead of the service account (which at the time of creation was sysadmin on both instances) I entered the data of my personal account (which is sysadmin on all SQL Servers). Once I did this the Linked Server worked like a charm. I changed the account back to the services account and it still worked. I tried to reproduce the error, I purged the Keberos tickets on the ADMIN box and on the source (I didn't dare tough on the target, there is this little bit of fundamental respect I have for production systems :) ) I could not reproduce the error. It seems to me like Kerberos got kickstarted when I entered my User data.

I hope this helps someone when facing the challenge of creating a linked server with integrated auth.