Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
junior.carls
Starting Member
7 Posts |
 Posted - 2008-10-10 : 13:12:09
|
| 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_AImpersonate: TrueRemote User: NullRemote Password: NullFor 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 1The 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_AImpersonate: FalseRemote User: Domain\ACCT_BRemote Password: PassFor 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 1Login failed for user 'Domain\ACCT_B'. Does anyone what I am doing wrong? Cheers. |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-11 : 04:02:09
|
| Connect to SQL_B using domain account ACCT_A. Check if you can access the tables. If not give explicit permission/make it part of role that has permissions.Hope this helps |
 |
|
|
|
|
|
|
|
Linked Server Issue