July 15, 2013 at 1:33 pm
Greetings,
I'm trying to establish a linked-server connection from our Regional SQL Server 2008 R2 (R1) to the Division SQL Server 2008 R2 (D1).
Both our server (R1) and the Division server (D1) reside on the same NT domain. All users who would be accessing the linked server (D1) have permissions to do so - this has been verified by connecting directly to D1 via SSMS. At this point, we're certain it's not a permissions issue, per se.
That said, we're having some issues, and I'm hoping someone here has solved these before and can help me out - Googling and searching the forums hasn't turned up precisely what we're dealing with, so that's why I'm posting here.
Scenario One:
I connect to R1 via SSMS on my laptop. I then execute a simple SELECT statement against D1 using the linked server connection:
SELECT * FROM [D1].[USERS].[DATA].[USER_DATA]
At which point, I get the following error message:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
After reading up on linked server connections and permissions, I execute the following, though technically I shouldn't have had to:
EXEC sp_addlinkedsrvlogin 'D1', 'true';
Again, I try the simple SELECT statement above, and get the same error message.
Scenario Two:
I use Microsoft's Remote Desktop (RDP) program to connect to R1. I then open SSMS on the R1's remote desktop, and attempt the simple SELECT:
SELECT * FROM [D1].[USERS].[DATA].[USER_DATA]
And I get valid data from D1
The question is this: Why is R1 not passing the NT credentials - despite being configured to do so in the D1 linked server definition - when a user connects to R1 via SSMS, and yet does pass the NT credentials of the RDP user when the query is executed through a RDP session with SSMS?
Fundamentally: is there a way to change this behavior? We need to be able to execute queries against the linked server within our local SSMS sessions on our local workstations without having to RDP every time; furthermore we need to replicate data from this linked server.
July 17, 2013 at 1:35 am
It sounds to me you have a "double-hop" issue at hand. Most of the time it's related to KERBEROS authentication and Active Directory. Your credentials are passed on from the first machine (desktop) to the server. But it won't be passed on from the server to the next.
Take a look at these links to help you
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
http://www.sqlservercentral.com/blogs/sqlsandwiches/2011/06/20/double-hop-of-doom/
July 19, 2013 at 4:32 pm
HanShi (7/17/2013)
It sounds to me you have a "double-hop" issue at hand. Most of the time it's related to KERBEROS authentication and Active Directory. Your credentials are passed on from the first machine (desktop) to the server. But it won't be passed on from the server to the next.Take a look at these links to help you
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
http://www.sqlservercentral.com/blogs/sqlsandwiches/2011/06/20/double-hop-of-doom/
That's actually the same conclusion I reached. Oddly enough, I had to search for the error message itself - which seems pretty damn generic - before finding any posts with the same issue and bringing up the double-hop problem.
Unfortunately it doesn't look like I'll be able to fix the issue if it's going to require IT to make any Active Directory changes, which from doing a brief reading of most of the articles seems to be the case.
The only other solution - and I'm not sure it would work - would be for the D server to create an SQL Server login, instead of a NT Login, that we could use to connect - and simply require that any connections via that linked server use those credentials. However, I'm not even sure if I can get them to do that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply