Sending message via SQL Server Service Broker from one machine to another.
Sender - Windows Server 2012 R2 with SQL Server 2014 12.0.4213.0 Standard over NAT that has port forwarding for Service Broker from public IP:port to local SQL Server IP:port. Let's say, for example, the port is
26223
and it forwards to SQL Server4022
.Receiver - CentOS 7 with SQL Server 2017 RC2 Express as web server with the same specific port opened for Service Broker. (updating to latest SQL Server CU - 14.0.3030.27 did not help)
I sent message from Sender to Receiver and firstly got error in sys.transmission_queue
about wrong alghoritm, then I found out that I have encryption key alghoritms different on sender's and receiver's endpoints and changed receiver endpoint key encryption alghoritm to match sender's.
After that, the message I sent appeared in sys.transmission_queue
with another error - Service Broker/Database Mirroring network protocol error occurred.
In SQL Profiler I found more details:
Broker:Corrupted Message - A corrupted message has been received. The binary message preamble is malformed.
On the Receiver side SQL Profiler shows that:
Audit Broker Login: 4 - Negotiate Failure - An error occurred while receiving data: '996(Overlapped I/O event is not in a signaled state.)'.
Audit Broker Login: 5 - Authentication Failure
Sometimes I also got that error at Receiver side in addition to previous:
Audit Broker Login: 4 - Negotiate Failure - An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.
I have only one message for that instance waiting in sys.transmission_queue
on Sender side.
UPDATE: I used ssbdiagnose utility and here is what it says:
D 29835 MainServer MainDatabase Messages on conversation handle 13c07ab1-6b8d-e711-80c5-002590cb2a25 (conversation id 3a7d0dd0-c957-4fe8-a98e-1cb56713023c) from service sbSenderService to service sbReceiverService cannot be transmitted dues to the following problem: Service Broker/Database Mirroring network protocol error occurred.
An internal exception occurred: No such host is known
D 29933 WebServer WebDatabase The routing address TCP://[NAT ip address was here]:26223 for service sbSenderService does not match any of the IP addresses for MainServer
An internal exception occurred: Value cannot be null.
Parameter name: value
An internal exception occurred: No such host is known
P 29945 Cannot parse the routing address: unknown
So, the new error came into play: The routing address TCP://[NAT ip address was here]:26223 for service sbSenderService does not match any of the IP addresses for MainServer
As I understand it, my MainServer (Sender) does not know the NAT IP Address that is in TCP headers and marks that TCP package malformed, right? How to fix it?
UPDATE2: we set VPN tunnel between servers and the same errors still exists. The test CentOS server had all ports opened and no Selinux. For now I assume that Service Broker does not work between CentOS and Windows.
UPDATE3: updating to latest SQL Server CU on CentOS - 14.0.3030.27 did not help