Kerberos Hell
-
Let me set up my scenario.
Server A: Win2008r2 std, SQL2008r2 default instance started with generic domain service account. Server A has been allowed delegation rights to any kerberos service via AD, as has the generic domain service account as well.
Server B: Win2008 std, SQL2008, default instance started with domain admin service account. Server B has been allowed delegation rights to any kerberos service via AD, as has the domain admin service account as well.
Workstation: Win7 ent, SSMS 2008r2
From my workstation to server A, my connection is made via TCP using Kerberos as per sys.dm_exec_connections. This is also verified via KerbTray.exe and seeing that the MSSQLSvc ticket was created.
From my workstation to server B, my connection is made via TCP using Kerberos as per sys.dm_exec_connections. This is also verified via KerbTray.exe and seeing that the MSSQLSvc ticket was created.
I create a linked server on Server B going to Server A using current logon credentials. Testing the connection yields success.
I create a linked server on Server A going to Server B using current logon credentials. Testing the connection yields the following error: "Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(Microsoft SQL Server, Error: 18456)
Running profiler adds a little more info to the error: Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
I have absolutely no idea why i'm getting the failure when using the Server A to Server B link.
I understand that the underlying issue is the double hop authentication, but i'm being authenticated via kerberos to begin with, so it shouldnt be an issue.
If I remote into server A, fire up SSMS on that machine, connect locally, and then test the Linked Server, it tests successfully and i can see via kerbtray.exe that the ticket is indeed created. After that, I can go back to my workstation and connect via ssms to server A and the Linked Server will continue to test successfully until the ticket that got created while i was remoted in expires, at which point it goes back to the anonymous logon error.
I hope someone has some suggestions out there, cause i'm all out of options.
One thing i should note is this: due to legacy support of previous business practices, the accounts that start up sql on both server A and server B cannot be modified from their original configuration (ie, i cant start up sql on Server A using the domain admin service account or Server B using the generic domain service account.
-
Seeing that there are no additional comments I will add one...
Today I just setup Kerberos authentication to allow a linked server to work between sql 2008r2 and a legacy sql 2000 sp4 server.
I followed the directions in article http://www.sqlservercentral.com/articles/Security/65169/.
Everything just worked for me without any issues.
I did have to wait (I assumed that something needed to replicate through AD Forest), I also had to disconnect (shutdown managment studio) from the SQL server and reconnect before I was able to authenticate with Kerberos.
If you run the following
SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.auth_scheme
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
You can check if SQL Connections are being made with Kerberos authentication.
Blog: http://crazyemu.wordpress.com/
Twit: @crazySQL -
what is the serverity and state of the 18456 error, that will then all depend on the action/s to take
-
Error: 18456, Severity: 14, State: 38.
-
//crazyEmu
As i already stated in my original posting, i've verified (using sys.dm_exec_connections) that my connection to both ServerA and ServerB is using kerberos for authentication.
-
abbottl (2/29/2012)
Error: 18456, Severity: 14, State: 38.state 38 is database doesn't exist/no longer exists or login doesn't have access to the requested database.
I know this is basic but the db's do exist and access is granted to all accounts bein used in Kerberos authentication?
can you post the screen shots of the linked server security config page?
think for some reason the impersonation isn't happening over the linked server and it's defaulting to the local system account which doesn't have rights.
-
I know this is basic but the db's do exist and access is granted to all accounts bein used in Kerberos authentication?
Db's exist and I do have access. As I stated originally, if i remote into Server A, the link works perfectly fine. Its only the dreaded "double hop" when connecting from my workstation to Server A via SSMS that i get the errors.
can you post the screen shots of the linked server security config page?
Its as basic as it comes, but here ya go.
-
does the computer account for server A definitely have delegation enabled
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (2/29/2012)
does the computer account for server A definitely have delegation enabled100% sure that both the service account and the computer account both have delegation enabled.
-
Have you tried using the same service account for both instances of SQL? We set the delegation at the AD account level for our double hops.
-
As stated originally, changing the service accounts on either server is not acceptable due to the need to support legacy systems that require the use of those accounts. Aside from that, MS best practices state that all sql servers should be started up with different accounts for security. So that shouldn't be the issue at all.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply