Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 R2 to SQL Server 2008 R2 Linked Server Issues Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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.
Post #1473870
Posted Wednesday, July 17, 2013 1:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:56 PM
Points: 2,397, Visits: 2,936
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/


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1474412
Posted Friday, July 19, 2013 4:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:54 AM
Points: 8, Visits: 35
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.
Post #1475711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse