Requirement for a Master Key for Service Broker

Question

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 1

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.

Answer 2

Alternative would be creating a master key for the service broker.

First Check your service broker queue by right clicking the queue and View Transmission Queue or simply use this query

SELECT  *, casted_message_body = CASE message_type_name WHEN 'X' 
    THEN CAST(message_body AS NVARCHAR(MAX)) 
    ELSE message_body 
END 
FROM [DATABASE_NAME].[sys].[transmission_queue]

If you find any data in here then transmission_status column will have the reason for this.

If the broker is not playing its role, I would create NEW_BROKER with following query

USE [master] 
ALTER DATABASE [DATABASE_NAME] SET NEW_BROKER

Then Enable the BROKER with TRUSTWORTHY set to ON

ALTER DATABASE DATABASE_NAME SET ENABLE_BROKER;
ALTER DATABASE DATABASE_NAME SET TRUSTWORTHY ON;

Finally, dropping the master key and Creating New master key and encrypt by new password:

ALTER AUTHORIZATION ON DATABASE::DATABASE_NAME TO [SA];
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '79HGKJ67ghjgk^&*^fgj'
GO

Password could be a user defined mixture of alphanumeric and symbols.

If any of the above steps taking longer time to run, then I would suggest you to stop the query and reopen the SQL manager and try again. It should work well!!

Answer 3

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;