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\sql2008, SQL (!) Login:
- Remote Domain:
dom14\sql20148, Windows (!) Login:
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
-- 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
The question is: How to properly pass on the credential to the remote server link? I am stuck.