Service Broker needs to access the master key in the database - Error-28054

29 August,2019 by Jack Vamvas

Question : I found this logged in the SQL Server Error Logs, during some Service Broker troubleshooting.

Service Broker needs to access the master key in the database 'MyDatabase'. Error code:32. The master key has to exist and the service master key encryption is required.

Error: 28054, Severity: 11, State: 1.

What does it signify? Service Broker is working ok on the server, but this error appears on some days.

Answer:

As a first step , run this sql code , which will signify if there is a master key for the database

 

Select name, is_master_key_encrypted_by_server from sys.databases Where name =  'my_database'

 

'0' indicates there is no database master key. If there is no master key , the workarounds include:

1) Create a master key 

2)Disable dialog security - i.e encryption = off 

There's a few potential reasons for the error message

BEGIN DIALOG CONVERSATION

(From the Microsoft documentation)
Specifies whether or not messages sent and received on this dialog must be encrypted when they are sent outside of an instance of Microsoft SQL Server. A dialog that must be encrypted is a secured dialog. When ENCRYPTION = ON and the certificates required to support encryption are not configured, Service Broker returns an error message on the conversation. If ENCRYPTION = OFF, encryption is used if a remote service binding is configured for the target_service_name; otherwise messages are sent unencrypted. If this clause is not present, the default value is ON.

BEGIN CONVERSATION TIMER

Starts a timer. When the time-out expires, Service Broker puts a message of type https://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer on the local queue for the conversation.

 

The conversation starts without ENCRYPTION=OFF - therefore a db master key is required for storing session keys.  The BEGIN CONVERSATION TIMER doesn't send messages but it does logs the messages in the sql server error log

Read Troubleshooting SQL Server Service Broker    extra diagnostic queries

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Service Broker needs to access the master key in the database - Error-28054


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer