Double hop error when using SQLCMD

Kenneth Fisher, 2018-02-26

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads