Linked Server Issue - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server Issue

Author

Topic

junior.carls
Starting Member

7 Posts

Posted - 2008-10-09 : 10:33:50

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-09 : 11:08:46

For Windows account to work in Linked server you have to setup Security Account Delegation and should be trusted. We use SQL Login for all linked servers . Try with it.

junior.carls
Starting Member

7 Posts

Posted - 2008-10-09 : 12:26:54

quote:
Originally posted by sodeep

For Windows account to work in Linked server you have to setup Security Account Delegation and should be trusted. We use SQL Login for all linked servers . Try with it.

Does Security Account Delegation work accross domains? I ask because in our production environment each account will belong to a different domain:

Example: Domain_1\ACCR_A and Domain_2\ACCT_B

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources