Service Broker Demystified - Encryption Weirdness

Encryption is one of those things that makes Service Broker difficult to learn.  To set up encryption correctly is a lengthy process.  There are ways to safely not use encryption.  In this post I'll show you all of the issues with encryption in SSB and how to avoid and fix the problems.  

Do you find Service Broker confusing?  I started a blog series called Service Broker Demystified because SB really isn't that tough if you don't get lost in the weeds.  Today we are going to cover the first thing that trips up noobs...issues with encryption.  

Service Broker requires communications to be encrypted by default.  This makes perfect sense to even the newest noob.  When transmitting data over-the-wire it should always be encrypted.  Of course if I'm communicating with other services I should encrypt those communications.  However, most noobs model their first SB designs as simple intra-database/intra-instance asynchronous workflows.  For example, most people first learn of SB when they want to model an asychronous trigger to do some near real-time ETL processing.  In this case no data is communicating over-the-wire so why should encryption be needed?

Before answering that question, let's see the problem in action.  You can download the repro script to play along at home.:

We first create a database and SET NEW_BROKER.  We then build two services and two queues, one with be the Initiator and the other the Target.

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:  

IF EXISTS(
   SELECT *
   FROM sys.databases
   WHERE name = DB_NAME()
   AND is_master_key_encrypted_by_server = 0)
BEGIN
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '8uY75%jj5k5#%#'
END

This creates an encrypted database master key.  If you run the demo again you should see the message properly enqueued.  

A Workaround

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:  

• the default ENCRYPTION option is ON which I think is wise...better to be safe than sorry.  The side-effect is you have to deal with the encrypted database master key errors.  
• If the two services are intra-db then communications are NEVER encrypted, regardless of the default setting.  Why?  Because the "communication" occurs as an IPC (in-memory) and no database traffic traverses the wire.  So why is encryption needed in this scenario?...see the previous bullet point...that's the default!!!
• If ENCRYPTION is OFF but you are communicating to a remote service and the proper certificates are configured, encryption will be used anyway.  So using ENCRYPTION = OFF simply avoids the error message when the proper certificates are not available.  Very wise.  

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.