“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:

Error Message:
“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”

Reason:

The error message is self explanatory one as this database does not meet a requirement to add AG as this database is protected with a master key. We may need to provide the master key while configuring the database into AG.Please note that this is not due to TDE, but due to master key.

One observation is that this is recently added as a validation in SQL Server 2016 version. Earlier version, you would be able to add seamlessly.

Solutions:

There are two solutions to add these databases into AG:

1. Use T-SQL to add the database into AG


ALTER AVAILABILITY GROUP AGName ADD DATABASE DBName

2. Provide the password and use GUI with few extra steps

In GUI, this is not so clear for the user that the password can be provided directly and configure AG.

Double click in the Password column
Type the password in the column
Click Refresh (To enable check box for the database)
Check the checkbox for the database.
Click Refresh again (To enable the Next button)
Click Next to progress

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s