October 9, 2008 at 8:26 am
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.
October 10, 2008 at 11:15 am
There was a typo in the previous post. It should have said:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'Domain\ACCT_B'.
October 10, 2008 at 2:15 pm
This might be some help: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
Chad
October 10, 2008 at 2:36 pm
I also think that the Username/Account that you supply to log in to the remote server has to be a SQL Account, not a Windows Account. If you have the linked server set up to pass the current login's security context, you can log into serverA as Domain\LoginA and access ServerB as Domain\LoginA (given appropriate rights). You can also set it up so that you log into serverA as Domain\LoginA and access ServerB as LoginB (a SQL, not windows account). But I don't think you can set up the linked server such that you log in to ServerA as Domain\LoginA and access ServerB as Domain\LoginC.
I don't know this for sure though.
Chad
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply