Service Broker - Administratively Disabled

Question

Have implemented SQL Service Broker at one customer's site and it is up and running fine. Took the same scripts from that customer and ran it on a different customer's databases, changing the name of the target and initiator servers. Running into "The message has been dropped because the service broker in the target database is unavailable: 'The service broker is administratively disabled'." <== what does that mean? Here's the scenario: Initiator sends out message to Target. Target receives message properly, runs activation proc properly, sends back a confirmation message, ending the dialog. The return message doesn't make it back to the Initiator, conversation is in Disconnected_Outbound status. In profiler, nothing is showing up on the Initiator. It just keeps on sending message for acknowledgement, nothing else. In Target profiler, the message above is shown constantly once the activated proc sends the message back, ending the conversation. I have confirmed that firewall is not on. What is puzzling is that the dialog is initiated and connected, checked via sys.dm_broker_connections. The databases are able to communicate with one another. If the target machine sends out a message to the initiator, everything works fine, though errors may be masked, I am seeing a Disconnected_Inbound with the dialog on the Target machine doing the Initiating. Any insight into why this occuring is truly appreciated or what the heck the message is alluding to! Jay

Answer 1

"The message has been dropped because the service broker in the target database is unavailable: 'The service broker is administratively disabled'." <== what does that mean?

Hello Jay,

By default the Service Broker is disabled, you have to enable it o database level, see How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Olaf,

Thanks for your response, but I made sure the broker is turned on in the Initiator and the Target databases.

It's amazing once you ask for help, you see things from a different perspective.  Ok, here's the gist of it

If "a" GUID of the service_broker_guid column in sys.databases in the target database matches the initiator's database service_broker_guid, then the service broker tries to route the message to that database, even though you specify a return address which is not local and not guid specific.  In my scenario, I actually specify 'Transport' as the return address.  How and why this occurs, no idea.  That was why the message stating the broker was disabled did not make sense because the broker in the Initiator was up and running.  In hindsight, it makes sense because the broker in the local database was turned off.

Thanks!