As a part of my work, I also provide consultancy to fix the issues. It has three advantages, first one is that I earn money and the second one, more important one, is that I get to troubleshoot and learn, and the third one is I share it further via my blog. In this blog, I would share my learning about the situation where my client was not able to add a database in an availability group and it was failing with error: This database is encrypted by database master key, you need to provide the valid password when adding it to the availability group.
My client said that he can’t add a database to an existing Availability Group. It kept on asking for a password, but the database doesn’t have TDE enabled. He also checked and found that no encryption used. Interestingly, this database as part of an availability group and due to some reason, they have to take it out and trying to add it back. The error message is as follows:
Here is the text of error message:
This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.
Whenever I have such a situation, I always use a profiler to understand what the application is doing to get the details. In the profiler, I could see the below query.
SELECT c.create_date AS [CreateDate] FROM sys.symmetric_keys AS c WHERE (c.symmetric_key_id = 101)
When I executed the same query in SSMS, we got a row for this database.
The name of this key was ##MS_DatabaseMasterKey## which means that there was a database master key created in the database, which is causing this issue.
WORKAROUND/SOLUTION – Database Master Key
First, make sure that the database is not having TDE enabled and they are no other encryption used by this database. Here is the command which we used to fix the issue.
Use [DatabaseName] GO Drop Master Key
After this, they were able to add the database to AG.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Great! Thanks for the troubleshooting details regarding this issue.
You noted before removing the key to: Ensure TDE is not enabled and there is no other encryption being used by the database.
Checking if TDE is enabled is easy to check (In SSMS > Database Properties > Options> State > Encryption Enabled).
How do I confirm that “there is no other encryption being used by the database”? i.e. Where do I need to look to ENSURE the database master key is not being used before I drop it?
Unfortunately above solution did not for me.
Using this script works great:
— On Primary Node
ALTER AVAILABILITY GROUP [AGNAME] ADD DATABASE [DBNAME];
Thank you !
Security defeated by the simplest of solutions. I love it. Worked for me too.