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
nslookup <IP Address of European DB>
? If that is successful and you're still getting theDNS lookup failed
error in your service broker messages, try running anipconfig /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.grant connect ..
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.