Requirement for a Master Key for Service Broker

service-broker, sql-server

Solution

I found the solution.

Even though the target service specified in my BEGIN DIALOG is contained in the same database, I needed to be explicit about the fact that the target service was within the same database.

This is done by adding the optional `CURRENT DATABASE` when specifying the target service:

BEGIN DIALOG @dlg_handle 
FROM SERVICE CheckpointAndLogInitiatorService 
TO
SERVICE 'CheckpointAndLogTargetService', 'CURRENT DATABASE'
ON CONTRACT
CheckpointStart_CheckpointStartReply
WITH ENCRYPTION = OFF;

Problem

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?

Original source