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
