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

SQL
1 Comment

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.

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 database-master-key-800x266

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:

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 ao-wizard-pwd-01

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.

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 ao-wizard-pwd-02

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)

, , , , ,
Previous Post
SQL SERVER – Always On Listener Not Coming Online – Failed to Create New NBT Interface, Status 1450
Next Post
SQL SERVER – Unable to Allocate Enough Memory to Start ‘SQL OS Boot’. Reduce Non-essential Memory Load or Increase System Memory

Related Posts

1 Comment. Leave new

  • 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?

    Reply

Leave a Reply

Menu