Service Broker behind NAT and port forwarding results in "corrupted message has been received" error

1.4k views Asked by At

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 Server 4022.

  • 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

0

There are 0 answers