Sql-server – Service Broker – Communication not happening between servers despite ssbdiagnose saying all is well

service-brokersql server

I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example:

http://blogs.msdn.com/b/sql_service_broker/archive/2008/08/26/securing-a-dialog-with-certificates.aspx?Redirected=true

A is SQL 2005, B is SQL 2012. It doesn’t work, and I have not been able to track down a good reason why. What I can see is:

The Profiler on the receiving end doesn’t even show any Service Broker events, indicating nothing is getting to it.

But using ssbdiagnose as below, it says congratulations, you have no errors:

C:\Users\me>ssbdiagnose -level info -E configuration from service ServerAServiceTest -S ServerA -d MyDB to Service ServerBServiceTest -S ServerB -d MyDB on Contract myContract

Microsoft SQL Server 11.0.2100.60
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

Also in the transmission queue, the only thing I see that seems particularly off is ‘to_broker_instance’ is null, though I explicitly specified that info when setting up the route.

Further, no errors are showing up in transmission_status.

Also, SQL Server error logs are shedding no light.

As for firewall issues, well, these are test servers not accessible from the outside so I tried turning the firewalls off altogether.

One thing that is bothersome, I will get these:

An exception occurred while enqueueing a message in the target queue. Error: 15581 State: 7. Please create a master key in the database or open the master key in the session before performing this operation.

I do open the key and these go away. But I shouldn't have to repeatedly open the key everytime I want to do something, should I? I suspect this is part of the problem even though as mentioned the errors go away.

Sorry for the somewhat open question – even some help identifying where to get more informative errors or debugging info would be great. This is new territory for me.

Best Answer

Is the database master key encrypted by the service master key? If not, the db engine has no way to automatically open it. To check, take a look in sys.asymmetric_keys, specifically the pvt_key_encryption_type_desc column. If you find that it's not, you can make it so by doing

alter master key add encryption by service master key