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.

enter image description here


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
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]
 EXEC xp_cmdshell @sql

enter image description here

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
  • 1
    From the US SQL server, can you perform a nslookup <IP Address of European DB>? If that is successful and you're still getting the DNS lookup failed error in your service broker messages, try running an ipconfig /flushdns command locally on the US database server in case you've got some bad DNS routes cached. See if that helps. If that works, I'll convert this to an answer, but if it doesn't, we will at least guarantee it's not a bad DNS route. – John Eisbrener Jun 24 '19 at 16:36
  • 1
    is the service broker endpoint being granted connect permission for the login that it is using ? - grant connect .. – Kin Shah Jun 24 '19 at 17:36
  • at the moment bad DNS seems to be the case, I will escalate to management and tomorrow hopefully post an update. end points have been granted permissions yes. – Marcello Miorelli Jun 24 '19 at 17:51

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]
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')

WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,  
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server

--source server     
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.