June 16, 2014 at 6:42 pm
Hey guys, I've got an interesting issue. I've got a server that is a distributor and has subscriber as a linked server as well. When I try to run a remote query such as the following
exec ('select 1') at SERVER01
I get a message saying that "Could not connect to server 'SERVER01' because 'login' is not defined as a remote login at the server. Verify that you have specified the correct login name." When I check both servers they have correct login names, so I have to drill further. What I find then is if I run EXEC sp_helpremotelogin, I get a list, where subscriber exists and distributor_admin is listed as the login. If I try to run the same remote query on a different server that's a linked server but not on this list, the query executes perfectly fine. If I try to add remote login by doing "EXEC sp_addremotelogin 'SERVER01', 'login', 'login'", I see it being added to the table, but I still can't execute the remote query. I'm kinda stuck now. I'm afraid to delete thedistributor_admin from remote login, because I'm afraid that it will screw up the replication, but I'm pretty certain that having distributor_admin in remote logins is what's blocking me from being able to run remote query under a different login. Can anyone suggest anything please?
June 17, 2014 at 12:04 am
Check the permission for linked server for specific login which you are using to access data.
Are you able to execute any other command for that linked server?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 17, 2014 at 10:57 am
free_mascot (6/17/2014)
Check the permission for linked server for specific login which you are using to access data.Are you able to execute any other command for that linked server?
When you say check the permissions, you mean go to the linked server, check what kind of permissions the login has? If that's the case, the login is a sysadmin.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply