April 4, 2008 at 5:32 am
I have very funny problem with linked server. I set up SQL 2005 instance called ServerX\SQL1. Then on server ServerY I run sp_addlinkedserver 'ServerX', before I defined alias on ServerY for ServerX specifying port number and instance name.
When I tried to run something on linked server I got:
Executed as user: DOMAIN\ServerY_Service. Could not connect to server 'ServerX\SQL1' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. . [SQLSTATE 42000] (Error 18483). The step failed.
There is only problem with named SQL 2005 instance. Ols SQL 2000 called ServerX can be easily accessed.
Any ideas?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
April 4, 2008 at 1:20 pm
Hello Berto,
You have to either run sp_addlinkedsrvlogin or use Management Studio, go to your linked server and specify the remote login that correspond the local login that is trying to connect to linked server.
So in your case, you have to specify a local user "DOMAIN\ServerY_Service"
and a remote user for this local user should be a login on the ServerX that has permissions to access whatever you are accessing on ServerX. If this is the same domain user, then check Impersonate next to the local login
Let us know if you need more clarification.
Regards,Yelena Varsha
April 7, 2008 at 12:53 am
Finally it works. I used ap_addlinkedserver instead of sp_addserver. Strange thing that the last one works fione for default instance but doesn't for named. thanks for help.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply