Requirement for a Master Key for Service Broker
I have read in various MSDN pages and SQL Server blogs that "usually" a Master Key is required in a Service Broker database.
Indeed, when trying to RECEIVE messages I get the following application event log message:
Service Broker needs to access the master key in the database 'MDR_REPLICATION_Z'. Error code:26. The master key has to exist and the service master key encryption is required.
What confuses me is why this is happening when all my CONVERSATIONs have ENCRYPTION = OFF.
Is there a way to make use of Service Broker internally within a single database where ENCYRPTION is OFF without having to create a Database Master Key?
Answer
From Service Broker Dialog Security:
Service Broker dialog security lets your application use authentication, authorization, or encryption for an individual dialog conversation (or dialog). By default, all dialog conversations use dialog security. When you begin a dialog, you can explicitly allow a dialog to proceed without dialog security by including the ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement. However, if a remote service binding exists for the service that the conversation targets, the dialog uses security even when ENCRYPTION = OFF.
In other words, make sure you don't have any matching remote service bindings.