If you are experienced with SB you can probably just look at code and, knowing the setup, realize that it will generate an error. But noobs will look at this and think that this looks like valid code...and it is, but it will still error. We can query the queue and see the message failed and then look in sys.transmission_queue and see the error:
"The session keys for this conversation could not be created or accessed. The database master key is required for this operation."
So, in the case of a simple intra-database/intra-instance SB design, you still need to setup encryption.
The fix is to run code similar to the following:
WHERE name = DB_NAME()
AND is_master_key_encrypted_by_server = 0)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '8uY75%jj5k5#%#'
This creates an encrypted database master key. If you run the demo again you should see the message properly enqueued.
There is a workaround that's a bit easier. You can download the repro script here.
All I did was add
WITH ENCRYPTION = OFF.
There may be other workarounds but this is the workaround I use when I don't have the permissions available to create encrypted database master keys and I know my SB design will ALWAYS be intra-db/intra-instance.
In the case where no data is communicating over-the-wire, why is encryption required?
This starts to get REALLY confusing. Here are some rules:
Hopefully encryption is a little less baffling now.
You have just read "Service Broker Demystified - Encryption Weirdness" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.
Dave Wentzel CONTENT
sql server service broker service broker demystified