Post reply

Linked server confusion....

  • February 12, 2009 at 11:03 am

    #126381

    Setting: sql 2005:

    I'm triyng to link SevrerA to ServerB. This works fine when run on ServerA

    EXEC sp_addlinkedserver @server= 'ServerB',

    @srvproduct='SQL Server'

    -- This one gives an error:

    EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',

    @useself=N'False',

    @locallogin='ServerASqlAccount',

    @rmtuser='ServerBSqlAccount',

    @rmtpassword='ServerBPassword'

    >>>

    Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

    An error occurred during decryption.

    Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

    There is no remote user 'ServerBSqlAccount' mapped to local user 'ServerASqlAccount'

    from the remote server 'ServerB'.

    >>>

    I thought that addlinkedsrvlogin would "map" database access

    requests from ServerA (via account ServerASqlAccount) to the remote server ServerB (via the

    account ServerBSqlAccount). Incidentally, ServerBSqlAccount has read-only access to a specific database.

    Help!

    Barkingdog

  • February 12, 2009 at 11:11 am

    #943054

    Make sure the account specified in the linked server has required credentials to access the database on server B

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • February 12, 2009 at 11:26 am

    #943063

    I'm trying to understand what you meant. Here's my understanding:

    The account on ServerA, of course, does not have direct access to the databases on remote server B. (I am using sql authentication). I thought the purpose of the mapping was to associate the sql account on ServerA with a sql account on ServerB. That way "selects" on ServerA to data on ServerB are actually fulfilled on ServerB by ServerB's local acount.

    TIA,

    barkingdog

  • February 12, 2009 at 11:37 am

    #943071

    If the linked server is on ServerA then the security should be set as 'Be made using this security context ', here specify the login and password that exists on server B and has access to the database.

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • February 12, 2009 at 1:25 pm

    #943155

    I am now trying to link the servers from Management Sutdio,

    In the Linked Server box I type "ServerB', Server type: SQL Server. On the Security tab I select "Be made using this Security Context" then enter the name of the Remote login account and pwd (for the sql user on ServerB). Hitting OK gives:

    "An error occurred during decryption:

    There is no remote User ' SQLServerBAccount' mapped to local user '(null') from the remote server Server B"

    Incidentally, I can successfully log into sql on ServerB using the SQLServerBAccount account and password. My problem appears to be some sort of connection issue.

    Barkingdog

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

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