SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Double hop error when using SQLCMD

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;

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.


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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...