How to pass credentials to a linked server in another domain?

Question

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.

Answer 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:

• How to Configure the Server to be Trusted for Delegation
• Register a Service Principal Name for Kerberos Connections
• SQL Bulk Insert Impersonation issue

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.