none
Linked Server Issues

    Question

  • Hi All,

     

    We are having a problem with Linked Servers that I simply cannot figure out.  Here is the set up, we have two databases running on two seperate SQL Server instances (different physical servers as well).  Let's call the servers SQL_A and SQL_B.  Each SQL Server has a database, let's call them DB_A and DB_B.  Each server and database are accessed using an Active Directory domain account, Domain/ACCT_A and Domain/ACCT_B, each with db_reader and db_writer permissions.  So, we have created a linked server for SQL_B and SQL_A.  Under the security tab, we have added a local server mapping as follows:

     

    Local Login: Domain/ACCT_A

    Impersonate: True

    Remote User: Null

    Remote Password: Null

    For a login not defined in the list above, connection will: Be made using the login's current security context.

     

    When we right click the linked server an click "Test Connection", we get "The test connection to the linked server succeeded".  But, when we try to select from the reponses table using using fully qualified name:

     

    select * from SQL_B.DB_B.dbo.responses

     

    We get:

     

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI" for linked server "SQL_B" does not contain the table ""DB_B"."dbo"."responses"". The table either does not exist or the current user does not have permissions on that table.

     

    Additionaly, when we connect to SQL_A as ACCT_A, we can see the linked server, but cannot see any of the tables.  I think this is a permissions issues.  In Oracle you need to make a public synonym for the database, but there does not seem to be a facility to do that.

     

    Some additional information.  When we changed the local server mapping to the following:

     

    Local Login: Domain/ACCT_A

    Impersonate: False

    Remote User: Domain/ACCT_B

    Remote Password: Pass

    For a login not defined in the list above, connection will: Be made using the login's current security context.

     

    We get a different error message for the query above:

     

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'TEST\insight_app_ext'.

     

    Does anyone what I am doing wrong?

     

    Cheers.

    Thursday, October 09, 2008 2:19 PM

Answers

  • I'm sorry - I just read through this thread again and realized that I've been having a little mental flatulence on this one 

    You can't - unless I'm terribly mistaken - create a linked server login to map to a remote windows login.  Windows authentication in SQL Server validates the credentials using the cleint's operating system - not stored usernames and passwords.  Either use a SQL Login on your remote server, or grant Domain\UserA permissions on ServerB, and remove any other remote server logins you have setup.  The local login on ServerA will pass it's credentials through to ServerB, and logon using UserA.

    Apologies for the confusion - hope this helps.
    Tuesday, October 14, 2008 3:33 PM

All replies

  • Is TEST\insight_app_ext ACCT_A or ACCT_B?  Have you explicitly assigned permissions to ACCT_A on SQL_B?
    Friday, October 10, 2008 3:40 AM
  • Sorry, that was a typo.  It should have said:

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'Domain/ACCT_B'.


    Also, in our production environment, each account would belong to a separate domain.

     

    Friday, October 10, 2008 5:06 PM
  • OK - hopefully the domains are trusted so the SQL Servers can still authenticate the logins?

    Have you explicitly assigned permissions to the accounts on the SQL Servers?
    Friday, October 10, 2008 6:02 PM
  • Yes we have.

     

    Tuesday, October 14, 2008 2:18 PM
  • If you log into server b directly using the credentials (username and password) you set up in the linked server login, are you able to log in?  Use SQL Management Studio to check.
    Tuesday, October 14, 2008 2:45 PM
  • I'm sorry - I just read through this thread again and realized that I've been having a little mental flatulence on this one 

    You can't - unless I'm terribly mistaken - create a linked server login to map to a remote windows login.  Windows authentication in SQL Server validates the credentials using the cleint's operating system - not stored usernames and passwords.  Either use a SQL Login on your remote server, or grant Domain\UserA permissions on ServerB, and remove any other remote server logins you have setup.  The local login on ServerA will pass it's credentials through to ServerB, and logon using UserA.

    Apologies for the confusion - hope this helps.
    Tuesday, October 14, 2008 3:33 PM