Geek Answers Handbook

Primary key requirement for a service broker

I read on various MSDN pages and SQL Server blogs that usually contain a master key in the Service Broker database.

In fact, when I try to RECEIVE messages, I get the following application event log message:

The service broker must access the master key in the database 'MDR_REPLICATION_Z. Error code: 26. A master key must exist, and encryption of the service master key is required.

What bothers me, why does this happen when all my CONVERSIONS have ENCRYPTION = OFF.

Is there a way to use Service Broker inside a single database where ENCYRPTION is turned off without having to create a primary database key?

+4
source share
3 answers

I have found a solution.

Despite the fact that the target service specified in my BEGIN DIALOG is contained in the same database, I need to be explicit that target strong> in 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;
0
source

From the Service Broker Dialog Box :

The brokerage security service allows your application to use authentication, authorization or encryption for an individual dialogue (or dialogue). By default, all dialogs in dialogs use dialog box protection. When you start a conversation, you can explicitly allow the conversation without securing the conversation by including the ENCRYPTION = OFF clause on the START DIALOGUE STATEMENT. However, if a binding to the remote service exists for the service, that dialogue purposes, the dialog box is used even if ENCRYPTION = OFF.

In other words, make sure that you do not have the appropriate remote service bindings.

+3
source

An alternative would be to create a primary key for a service broker.

First check the broker service queue by right-clicking the queue and view the transfer queue or just 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 here, then the transfer_stat column will be specified for this.

If the broker does not fulfill its role, I would create NEW_BROKER with the following query

 USE [master] ALTER DATABASE [DATABASE_NAME] SET NEW_BROKER

Then turn on the BROKER with TRUSTWORTHY set to ON

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

Finally, discarding the master key and creating a new master key and encrypting the new password:

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

A password can be a custom mixture of alphanumeric characters and characters.

If any of the above steps takes longer to run, I suggest you stop the query and reopen SQL Manager and try again. It should work well.

+3
source

More articles:


All Articles