DNS lookup failed with error: '11001(No such host is known.)'

Question

I have a server in the USA that should connect to one of our servers in Europe through service broker.

The server in Europe is an Availability group, whose listener is called sqlst.

when I run the following command in the USA server:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'

 EXEC xp_cmdshell @sql

I get a wonderful message saying that all the connectivity is fine as you can see on the picture below: The Ip addresses have been masked to protect the innocent.

However,

when I go to my USA Server in the correct database, and I run the following query after I have been told that the broker has not worked:

-- Error messages in the queue
SELECT * 
FROM sys.transmission_queue

where service_contract_name = 'ORCACOLAContract'
order by enqueue_time desc

I get the following reason as why it has failed:

DNS lookup failed with error: '11001(No such host is known.)'.

The service account in the Europe Server has been added to Kerberos and all linked servers work fine.

Looking at the sql error log I don't see any related error messages.

I am not sure this is related to service broker and alwayson

I have done all these - linked server working fine.

I have no access outside of sql server in the USA machine. Inside SQL Server I am sysadmin.

As I said above when I run the following I get a good response:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'
 EXEC xp_cmdshell @sql

When I replace the server name by its IP Address and run it:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.xxx -port 4022'
 EXEC xp_cmdshell @sql

I also get a positive return.

However, when I run nslookup using the IP address of sqlst - 200.1.0.xxx I get the below error:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command $ns = (nslookup.exe 200.1.0.xxx )[-4..-3]
$ns'
 EXEC xp_cmdshell @sql

Basically that says, domain controller cannot find the sqlst ip address

I have no way to run ipconfig/flushdns but I have run the following command instead:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Clear-DnsClientCache'
 EXEC xp_cmdshell @sql

score 0 · Accepted Answer · answered Apr 12 '20 at 16:26

this problem was actually caused by a particular way the trusted domains US-Europe was configured, (beyond the scope of this question) however, using the scripts that you can see on the original question, then working with the sysadmins, and then finding server names that could be authenticated on both ends (source and destination) resolved the case - after altering the routers as per this example:

--targer server 
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]   
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,  
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server     
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]   
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,  
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,  --BROKER_ID OF COLA  
       ADDRESS = N'TCP://mytargetserver.mycompany.com:4022'--IP address of target server
GO

DNS lookup failed with error: '11001(No such host is known.)'

1 Answers1