Linked server login problem when servers are in different domains

Question

I used to establish a linked server between two sql servers that are on different domain and it works for a few days. But suddently it fails with the following error message, and I have tried reapply the same way but could not get it resolved. Can someone gives help on this?

The way used to establish the linked server in server "domain1\abc" to server "domain2\xyz" is as follows:

Sql server "abc" is in domain1, sql server "xyz" is in domain2
1. Create a sql server login "testLogin" with password of "qwer1234" in sql server "xyz"
2. In sql server "abc", add a linked server login to "xyz" and do the following property config
      - In General, type linked server name "xyz" and choose SQL server type
      - In security, select "Be made using this security context" by supplying the remote login "testLogin" and its password

      or using script
     EXEC master.dbo.sp_addlinkedserver @server = N'xyz', @srvproduct=N'SQL Server'
     EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xyz',@useself=N'False',@locallogin=NULL,@rmtuser=N'testLogin',@rmtpassword='qwer1234'
3. Click ok.

The linked server created successfully and we can retrieve data by querying like this "xyz.testdb.dbo.table1" in server abc. It worked for a few days, then one day it failed with the following error message and I'm not sure the resolution.

-------------------- the error message -------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "xyz" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "xyz" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------

Answer 1

Anything changed in the servers? Port number, SP etc? what happens when you connect to the destination server from SSMS using the credentials above?

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 2

Hi Vidhya, thanks for your points here.

I have another server in the same domain as "xyz", there's no problem on creating linked server by using the same credentials.

I'm not sure what changes be made on servers (seems not), port number (how to check this?). If so, how can I get the linked server re-established in server "abc"?
But, yes, when I connect to the destination server "xyz" from ssms in source server "abc" using the credentials above, it gives the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to xyz.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 3

Error code 53 tells that the sql server is not reachable from the client machine.  
If you run the following two commands on the client machine, what do you get ?
telnet <serverMachineName> 1433

ping <serverMachineName>

---

Thanks, Leks

Answer 4

Hi Lekss, The two servers are in different domains. so in client,  I ping\telnet the server's IP address direclty instead of server name as the name could not get recognized.

The ping succeeded, but telnet failed with error of  "Could not open the connection to the host, on port 1433: connecton failed".

-
One udpates: I updated the script to create linked server by specifying its IP address direclty, but the linked server still not work.

EXEC master.dbo.sp_addlinkedserver @server = N'xyz', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'xx.xxx.xx.xx'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xyz',@useself=N'False',@locallogin=NULL,@rmtuser=N'testLogin',@rmtpassword='qwer1234'

Answer 5

Check whether your server xx.xxx.xx.xx has sql running in it . Try giving full DNS for the server.

---

Thanks, Leks