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

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)