Post reply

Linked Server - Non SysAdmin Login - Access to the remote server is denied

  • February 24, 2011 at 7:43 am

    #247258

    Hi All,

    I have set up a linked server connection between two of our database servers, both use SQL Server 2005 Standard Edition.

    I have specified a certain SQL Server Login Account on the local server to map to a login SQL Server Login Account on the remote server.

    If i test the connection (within SSMS) it works fine as long as the user on the local server is a member of the sysadmin fixed server role; if they are not then i get the following error:

    "Access to the remote server is denied because no Login Mapping exists".

    I've seen a few similar issues posted online but none of them seem to have been resolved. Does anyone have any sugggestions as to how i can resolve this issue. I do not want to leave the local user with "sysadmin" permissions if this ia at all avoidable.

    Thanks in advance.

  • February 24, 2011 at 9:54 am

    #1291085

    do you same login for both the servers?

    if you have same login defined in both the servers then does the user has database access in both the servers?

  • February 24, 2011 at 9:59 am

    #1291087

    Thanks for the response.

    No the login account on the local server is mapped to a login account with a different name on the remote server.

  • February 24, 2011 at 10:18 am

    #1291104

    when you have created a linked server in the local system

    there will be an option security in the wizard select the tab

    in the tab you should add the local user which you want to use the linked server and then do not impersonate the user instead select the remote user and type the password and click ok

    now open the ssms with the credentials of a local user and enjoy using linked server facility.

    i hope this should work.

  • February 24, 2011 at 10:34 am

    #1291118

    Thanks but this is the process that i used when i set up the linked server user mapping in the first place; the problem is that if i now test the connection i get the error message unless the local user is a member of the sysadmin server role.

  • February 24, 2011 at 10:58 am

    #1291136

    i have done the same process and then only i have told you while i have tried to connect ssms with different user than that mentioned in the linked list and executed the query i got the error message which you have mentioned. please check with your credentials(remote username and password are correct or not) then use 4 part naming convention to execute the query.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply