October 14, 2008 at 7:01 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 from seperate domians, Domain_A/ACCT_A and Domain_B/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 'Domain\ACCT_B'.
Does anyone what I am doing wrong?
Cheers.
October 26, 2008 at 12:49 am
if your remote server is an oracle server then use the OLE DB provider for the ORACLE provided by the Microsoft.not the native SQL server provider. make sure that your servers are delegated at the domain level before you link them.
October 30, 2008 at 4:30 am
Hello:
Please refer to http://www.databasejournal.com/features/mssql/article.php/3691721 for details on linked server. This is a very good read.
Regards,
Vishnu Modi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply