Linked Server not working correctly

Question

Post reply

Linked Server not working correctly

dwilliscp

SSCrazy Eights

Points: 9270
More actions
October 14, 2013 at 1:10 pm

#284501

I am trying to set up a linked server (2008R2 to 2008R2).. where the name of the linked server is not the name of the server (we have only one instance per server). But it fails with a login error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The SQL Auth Login does exits, from below, and I can log in using Management Studio
The SQL I am using:
EXEC master.dbo.sp_addlinkedserver
@server = N'Willis02',
@srvproduct=N'MSDASQL',
@provider=N'SQLNCLI',
@provstr=N'DRIVER={SQL Server};SERVER=R2D2;UID=LinkedServer_Test;PWD=Help;'
EXEC sp_serveroption 'R2D2', 'rpc out', 'true';
EXEC sp_serveroption 'R2D2', 'rpc', 'true';
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 1

I ran a trace.. and it added the following:

Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Isn't your linked server called Willis02, not R2D2?

EXEC sp_serveroption 'Willis02', 'rpc out', 'true';

EXEC sp_serveroption 'Willis02', 'rpc', 'true';

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 3

mister.magoo (10/14/2013)
Isn't your linked server called Willis02, not R2D2?
EXEC sp_serveroption 'Willis02', 'rpc out', 'true';
EXEC sp_serveroption 'Willis02', 'rpc', 'true';

Willis02 is the name I am using to describe the link to the server called R2D2.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Yes, that's my point.

Although it's not clear from the help on Technet, I am pretty sure (without a setup to test it on at the moment) that the server name being passed to sp_serveroption should be the linked server name, not the name of the host to which the linked server is pointing.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

May help http://blogs.msdn.com/b/chrissk/archive/2009/04/07/how-to-create-a-sql-server-link-server-with-a-different-name.aspx