2

I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains.

The problem is that I can login into the remote server only with a Windows Account from the remote domain. So the challenge is to pass on the credentials to the remote server, but here I am stuck.

  • Local Domain: dom8, Server: dom8\sql2008, SQL (!) Login: localuser
  • Remote Domain: dom14, Server: dom14\sql20148, Windows (!) Login: dom14\import

Server dom8\sql2008 wants to connect to dom14\sql2014 in order to pull some data.

Here is what I have tried on the local server: (logged into dom8\sql2008 as sa) :

-- Create a credential for the remote Windows login: create credential cred_import WITH IDENTITY= 'dom14\user14', SECRET = 'password' ; -- Alter the local SQL login and add the created credential: alter login local_user with credential = cred_import ; -- Create the Linked Server entry: exec sp_addlinkedserver @server='dom14\sql2014', @srvproduct='SQL Server' -- Add the credential to the linked server: exec sp_addlinkedsrvlogin @rmtsrvname ='dom14\sql2014', @useself = 'FALSE', @locallogin=local_user, @rmtuser = [cred_import], -- trying to pass on the credential @rmtpassword = NULL

However, it does not work. Whenever user localuser tries to connect to the remote server through the local server, then it gets an error login failed

The question is: How to properly pass on the credential to the remote server link? I am stuck.

Improve this question

1 Answer 1

Reset to default
1

You probably need to set up Kerberos to handle delegation. This requires setting up an SPN for your SQL Server as well. The following question and sole answer give more details:

SQL Bulk Insert Impersonation issue

The main documentation links in that Question and its Answer are:

Also, I'm not sure if this info will be useful or not, but it might help to have the Login and Password for the account on both domains to be exactly the same. I know that when you do not have Active Directory set up but want to share folders between two systems without having to log in, it works if you use the exact same Login with the exact same Password.

Improve this answer
1
  • 1
    Thank you for taking the time! The links are very interesting. I am going through it. I'm working on this problem now since two full days and I'm trying everything to get some light on it. Commented Dec 12, 2015 at 18:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.