Detailed answers to any questions you might have

Discuss the workings and policies of this site

Learn more about hiring developers or posting ads with us

By using our site, you acknowledge that you have read and understand ourCookie PolicyPrivacy Policy, and ourTerms of Service.

Q&A for work. A dedicated place to share your teams knowledge.

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?

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 dont have any matching remote service bindings.

Alternative would be creating amaster keyfor 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

Then Enable the BROKER with TRUSTWORTHY set to ON

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

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!!

Even though the target service specified in myBEGINDIALOG is contained in the same database, I needed to beexplicitabout the fact that thetargetservice was within the same database.

This is done by adding the optionalCURRENT DATABASEwhen specifying the target service:

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

By clicking Post Your Answer, you agree to ourterms of serviceprivacy policyandcookie policy

Announcing the arrival of Valued Associate 679: Cesar Manara

Data science time! April 2019 and salary with experience

Why does the Cisco show run command not show the full version, while the show version command does?

What is it called when you ride around on your front wheel?

My bank got bought out, am I now going to have to start filing tax returns in a different state?

Could moose/elk survive in the Amazon forest?

Does Mathematica have an implementation of the Poisson binomial distribution?

What is the term for a person whose job is to place products on shelves in stores?

Would reducing the reference voltage of an ADC have any effect on accuracy?

How to use @AuraEnabled base class method in Lightning Component?

Is it acceptable to use working hours to read general interest books?

Retract an already submitted recommendation letter (written for an undergrad student)

Rubric as meaning signature or personal mark — is this accepted usage?

Align column where each cell has two decimals with siunitx

site design / logo 2019 Stack Exchange Inc; user contributions licensed undercc by-sa 3.0withattribution required.rev2019.4.24.33422