SQL SERVER – Error 15580 – Cannot Drop Master Key Because Dialog “GUID” is Encrypted by It

Sometimes I get amazed by the speed at which the search engine crawls and gets the right results. A few days ago, I wrote a blog about a UI error in Always On availability group. Someone found that blog, followed it, got another error and contacted me for assistance. In this blog, I would share my knowledge about how to fix error 15580 – Cannot drop the master key because dialog “GUID” is encrypted by it.

This is the blog that I wrote a few days ago. SQL SERVER – Always On Error: This Database is Encrypted by Database Master Key, You Need to Provide Valid Password When Adding it to the Availability Group

One of my blog readers was getting the same error while using the wizard so he ran below command.

USE [DatabaseName];
GO
DROP MASTER KEY
GO

But we received the error:

Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because dialog ‘0BAF40FB-6CE1-4F73-A6FB-0E57EA4D5B6F’ is encrypted by it

WORKAROUND/SOLUTION – GUID

We went further to figure out where the GUID was coming. Executed below to find if the service broker is enabled.

SELECT is_broker_enabled, name
FROM sys.databases

But found that the service broker was not enabled for the database having a problem. Executed below

SELECT *
FROM sys.conversation_endpoints

and found the conversation endpoint has the entries with conservation_id ‘0BAF40FB-6CE1-4F73-A6FB-0E57EA4D5B6F’. This means there were some leftover entries and it would need a cleanup. This should be done when you are sure that there is no encryption used in the database and server.

USE [DatabaseName]
GO
ALTER MASTER KEY FORCE REGENERATE 
WITH ENCRYPTION BY PASSWORD = 'EnterStrongPasswordAndRememberIt'
GO

After this, we were able to drop master key (same command as earlier)

USE [DatabaseName]
GO
DROP MASTER KEY
GO

And, as mentioned in my other blog, now they were able to add a database to the availability group. Let me know if you have ever faced this issue before and if you have, how did you solved the problem. Please leave your feedback in the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)