October 10, 2008 at 3:44 pm
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 'Domain\ACCT_B'.
Does anyone what I am doing wrong?
Cheers.
October 14, 2008 at 9:04 am
Can you directly login to SQL_B using Domain\Acct_A and query DB_B? Are you sure you have granted rights to Domain\Acct_A to SQL_B.DB_B?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 10:09 am
Jack Corbett (10/14/2008)
Can you directly login to SQL_B using Domain\Acct_A and query DB_B?
No, Domain_A\Acct_A only has permissions on SQL_A.DB_A. We understood that this is the way it should be becaseu Domain_A\Acct_A was mapped to Domain_B\Acct_B when we linked SQL_B.
Are you sure you have granted rights to Domain\Acct_A to SQL_B.DB_B?
As per the above, no. Acct_A is in a different (and less trusted) domain. Domain_A is all the way inside while Domain_B (which Acct_B belongs to) is in our DMZ.
Any thoughts?... It seems no one has ever had this set up before.
Cheers,
Dave
October 14, 2008 at 10:20 am
Based on how I understand the Linked Server Security section in BOL, you cannot MAP to a different Domain Login. You would need to either grant Domain\Acct_A access to SQL_B.DB_B or create SQL Server login with only the needed rights to SQL_B.DB_B and use that as the remote user in the mapping. Here is the section from BOL:
The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.
Note:
When possible, use Windows Authentication.
If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication. Therefore, you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server.
I'm pretty sure my understanding is correct.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply