Adding DB to Always On Availability Group “Password required”

It is just one of those days which you are getting ready confidently to add another database into AlwaysOn Availability Group just like you did countless times since 2014, maybe you work on a version upgrade project and finished a migration from earlier version to newer version just like me. You open SSMS, launched “Add Database” wizard but there is a strange error which you have never seen before. Wizard says it is not possible to add database to AlwaysOn Availability Group because “Password required”. But what password? You then start to wonder and confuse, because this database is not encrypted, at least not on your watch!

What is it Doc?

I’d like to explain what is going on behind the curtain for curious, passionate database administrator before providing easy and quick solution. If you are willing to just fix it and move on, you can jump to last part.

Before moving forward, I’d like to explain my scenario. I migrated a database, which was already in availability group, from 2014 to 2016 and tried to add database to availability group, then I saw this error. At that moment, I didn’t know that the database is encrypted.

Now, let’s understand why we are required to enter a password nto be able to make database highly available. When you click on “Password required” hyperlink, it shows a dialog which clearly states that this database has some keys which needs to be opened via password.
“This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.”

That’s right. Database is encrypted. But how and when?

After checking carefully, I noticed that application database has a symmetric key since big bang, okay cool but this database was added to Availability Group in SQL Server 2014 without any issue, why is it an issue now?

Because Microsoft introduced a new check step while adding a database to an availability group in SSMS 2016 :’Checking password of the database master key’. It turns out this is a known issue with SSMS when you migrate a database into a new server and choose “Full database and log backup” method for initial synchronization. According to this blog, you may skip it by choosing another initialization method.

That’s why it was just fine on SQL Server 2014 but suddenly not possible in SQL Server 2016. It was not SQL version but Management Studio version that was causing this issue.

For those who likes to reproduce issues

Here is the code block for you to reproduce this issue on test box.

--First let's create an Empty Test DB to reproduce the scenario
CREATE DATABASE EncryptedDB;
GO
--Now create master key under database
USE [EncryptedDB];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Db@RuL3z!'
GO

--Take a full backup to meet requirements
BACKUP DATABASE [EncryptedDB] TO  DISK = N'\\BackupPath\EncryptedDB.bak' ;
GO

--Then open "Add Database" wizard under Always On High availability

Just tell me how to fix this

To solve this problem and go back to our peaceful, calm daily dba life(heavy sarcasm), you have simply two options.

• Provide the password for database key and click “Refresh”
• Drop the key

If you choose the first approach, it may be harder to find where to put password than finding the password itself since it is not very obvious in the wizard. There is a vague, hidden text box which you can enter password under “Password” column as shown in below screenshot.

If you prefer the latter, then just drop it. Here is MS documentation to Drop master key.

USE [EncryptedDB];
GO
DROP MASTER KEY; 
GO

Until next weirdness, au revoir!