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.
Best Answer
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.