Kerberos

Question

Kerberos

adb2303

SSCertifiable

Points: 7588
More actions
April 26, 2012 at 2:21 am

#257109

Hi,
Anyone know of a DMV or an easy way to check using TSQL whether Kerberos double-hop is working? Although you've got the auth_scheme column of sys.dm_exec_connections, it only says kerberos/NTLM, etc. I can't see anything whether that connection is a single or double-hop.
I've got code that simulates a double-hop and catches errors, etc. but what I'm finding is, that under heavy server load, the double-hop check can fail, even though everything else seems to be okay. So, I set it up to try twice if the first attempt fails, but very occassionally, we still get errors raised.
I'm not entirely happy with the double-hop simulation I currently have, but if there's something already existing in SQL Server that says definitively one way or another...
Cheers,
Andrew

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 1

When you say you experience problems under heavy load, do you see some type of error message to indicate it's not working? Have you tried slowing down the load test to see if that changes the behavior in case it's not Kereros but rather the server load is actually the problem not the authentication mechanism / configuration? What is the application setup here, are you going from client > web server > SQL Server? What versions of Windows, SQL, and if IIS is involved are being used? To get delegation working it's important to step though the configuration and make sure you have everything setup correctly otherwise you could experience problems.

Make sure the SQL Server has the proper SPN's setup and configured for delegation. The application server delegating credentials to the SQL Server will also need to be configured properly to work.

http://msdn.microsoft.com/en-us/library/ee191523(v=sql.100).aspx

You could query the DMV sys.dm_exec_connections and look at the two columns:

[auth_scheme] - You want to see "KERBEROS"

and

[client_net_address] - If it's a double hop connection you will see the IP Address of your application / web server rather than the client machine running the application / browser.

You might also look at the connectivity ring buffer to see if you see connection errors:

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

adb2303 SSCertifiable Points: 7588 More actions · Answer 2

Hi,

Thanks for the reply

I have 2 servers; "ServerA" and "ServerB".

There is a linked server called "Link1" from ServerA to ServerB.

There is a linked server called "Link2" from ServerB to ServerA.

Each server has a copy of the same stored proc running on it that checks the kerberos double-hop

The code that checks the kerberos double-hop is (trimmed down):

(From ServerA) EXEC('SELECT * FROM Link2.master.sys.tables) AT ServerB

(From ServerB) EXEC('SELECT * FROM Link1.master.sys.tables) AT ServerA

99% of the time, this works. I check sys.dm_exec_connections and I can see that it's using kerberos, and I get results from the query. Occassionally though, it returns 'login failed for user 'nt authority\anonymous login'". My stored procedure catches this and raises an alert. However, I'd much rather find a dmv or something that one way or the other confirms that a connection is a double-hop... Hope this makes sense.

I am confident that the SPN's are correct and that delegation's set-up properly. Although I'm only checking between the 2 servers in the stored procedures, we have other client servers that pass their windows credentials over the linked servers without problems.... and as I say, this works 99% of the time.

I'm testing this on Win2k3 R2, SQL Server 2005 SP3

I'll look at the ring buffer link you sent.