tl;dr; SQLCMD v2014 and up has special requirements for Kerberos.

One of the problems with linked servers (no rude noises please) is that frequently you will see a double hop error.

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

Let’s start with a brief explanation of a double hop. If you already know what it is, feel free to skip down to the next section.

Double Hop

There are two parts to this. First You connect from one machine to another, then that machine connects to a third. So:

Workstation -> Server1 -> Server2

This is the double hop part of the problem. Each of the arrows is a hop. This why you see the error when querying a linked server from your workstation but if you remote into Server1 you don’t. In that case, there are only two machines with a single hop involved.

Server1 -> Server2

But that’s not all of it. Otherwise, you’d see the error all the time. Next is the security component. I’ll be honest, I don’t understand this part 100% myself but I’m going to do my best.

This error is seen when using a Windows Authenticated id and NTLM or an untrusted Kerberos connection. From what I understand, with Kerberos the client (SQL in this case) gets a ticket containing your credentials, it can then pass this ticket on to other servers that trust it to have authenticated you to be sure you are who you say you are. Note the mention of trust. This means that even if you are using Kerberos if the two servers aren’t in a trusted relationship then you will still see the error. And NTLM doesn’t have the option to do this at all (again, not sure why).

At this point it’s important to note that this is not a SQL Server error. You will see this error in other places where you bounce across multiple machines and aren’t using Kerberos with a trusted relationship. I’ve seen it when trying to access files before (it was a weird situation admittedly).

Ok. Now that I’ve spent all this time going over the background information it’s finally time to get to the point.

SQLCMD is failing, but SSMS works

We ran into this problem at work the other day. The query across a linked server worked in SSMS, but not SQLCMD. From the same machine! I knew Kerberos was set up because the command worked in SSMS (and in the end, I double checked the connection). I was thoroughly confused for a while. Eventually, I checked the connection from SQLCMD.

-- Run this from a SQLCMD session
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid;
GO

And low and behold the result was NTLM!

Ok, so now I know why I’m getting the double hop error, but why is this happening and how do I fix it? Fortunately one of my co-workers had already seen this post: SQLCMD 2014 files to authenticate via Kerberos.

The first thing it mentions is checking the version of SQLCMD as this only affects 2014 and greater. FYI, the post mentions later that this is by design so I don’t expect this to change in the future.

SQLCMD /?

Unfortunately for the solution, I’m going to have to just snapshot the MS blog post. I just don’t understand it well enough to generate my own version, and I don’t want to risk the other post going away (even though it’s an MS post). I will include a link to SETSPN which is the tool used, more information on SETSPN and SPNs themselves and of course the link to the full post is just above.