7

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 (local network infrastructure): dom8, Server: dom8\sql2008, SQL (!) Login: localuser
  • Remote Domain (Microsoft Azure): 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.

By the way, I can connect to the remote domain by using SQL Server Studio as follows:

runas /netonly /user:dom14\user14  "C:\...\Ssms.exe"

So obviously I can create a credential of the remote side on the local side. Only a remote server link does not work.

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • I don't have at hand a SQLServer in different domain, but I would try recipe http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx . Azure servers also work in separate AD Domain. I don't quite understand why they're creating ODBC sources for remote servers. Maybe that's a key point. – Alex Yu Dec 20 '15 at 20:12
  • @Ingaz Aaaa that's interesting. Thanks! We have indeed an Azure Server, forgot to mention that. Will read and try that article tomorrow ! – SQL Police Dec 20 '15 at 22:36
  • @Ingaz Just a quick comment: It seems that in this article, they are using SQL logins, and not WinAuth logins. They are also using `sp_addlinkedsrvlogin`. I will read that tomorrow more in detail, it's late at my place. – SQL Police Dec 20 '15 at 22:42

1 Answers1

0

You will need a trust between the two domains in order to log into a domain A machine with a domain B password.

Once the trust is in place, you can then go to the domain A machine and grant permissions to the domain B user ID the permissions needed to do what your wanting to do.

However, it looks more like your trying to use local accounts, not domain accounts based on your script. I suspect the way you have the domain referenced, versus the server it's self may be the cause.

Looking at lines:

-- Create a credential for the remote Windows login:
create credential cred_import WITH IDENTITY= 'dom14\user14', 
       SECRET = 'password' ;

Is DOM14 the name of your domain? Or the name of the server your trying to connect to?

exec sp_addlinkedserver 
    @server='dom14\sql2014', 
    @srvproduct='SQL Server'

A server in another domain would be XYZ.DOMAIN.COM where XYZ is the actual server name, and domain.com is the domain that server is in. Also, you will need to make sure the server has access to read the other domains DNS tables.

From server A, you should be able to go to a CMD prompt and ping the other server by name (FQDN may be needed depending on Suffix Search list). Not concerned if the ping fails, as long as the IP address for the server is returned. If you don't get an IP address back, you have a more basic problem of name resolution between domains (you may need a forwarder in Domain A's DNS server pointing Domain B's name to a DNS server in Domain B), and vice versa (A to B, B to A, etc).