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)