Workaround for Adding Encrypted Databases by a Database Master Key on High Availability Groups without a password

Have you ever encountered this error when you are trying to add a database on your Always On Availability Groups?

"This database is encrypted by database master key, 
you need to provide valid password when adding it to the availability group."

You get this on the Wizard after warning you that a Password is required to continue.

I got stuck on this issue when I was trying to migrate a database from an old SQL Server to another and this database was the database of one of our third-party web solution.  I tried to drop the  master key but got this error:

"Cannot drop master key because certificate 'ZOHO_CERT' is encrypted by it."

I then asked the support team of the third-party solution  but never got a got a logical answer.

I was not happy because what I was asking in my opinion is quite simple but I was pointed to a different answer. So I used the power of Facebook posts on their wall, like some of their customers, they did answered so I thought there was light at the end of the tunnel but after the first response they never had bothered, I think support is not that important to them, the support personnel that got my request might not be empowered or trained properly hence cannot answer them technically. I find it ironic as the product we paid for was for supporting our users but they themselves cannot support us.

Anyways enough with the rant, I need a solution hence I tried to fix it myself.  Like I initially said I tried removing the master key but got that error message that started the support conversation but in the end found a way to do it and it’s quite simple. The problem! you cannot use the Wizard to add the database on your Availability Groups if you don’t have the password but it is still possible to using TSQL as long as you have the sysadmin rights.

Here are the steps to do it but before we start lets name some variables so its does not get confusing to you

• SQL Primary – Your Primary SQL Server
• SQL Secondary – Your Secondary SQL Server
• [Your DB] – The Encrypted Database
• [Your Availabilty Group] – Your Availability Group

Step 1 : Perform a full backup and transaction log back of [Your DB] on “SQL Primary”

 USE master
 GO
 BACKUP DATABASE [Your DB] TO DISK = 'The path you want to use\Your DB_full.bak';
 BACKUP LOG [Your DB] TO DISK = 'The path you want to use\Your DB_log.trn';

Step 2 : Add [Your DB] on “SQL Primary” to your [Your Availabilty Group]

 USE master
 GO
 ALTER AVAILABILITY GROUP [Your Availabilty Group] ADD DATABASE [Your DB]

Step 3 : Restore the full backup and the transaction log on “SQL Secondary” with no recovery option

 USE master
 GO
 RESTORE DATABASE [Your DB] FROM DISK = 'The path you want to use\Your DB_full.bak' WITH NORECOVERY;
 RESTORE LOG [Your DB] FROM DISK = 'The path you want to use\Your DB_log.trn' WITH NORECOVERY;

Step 4 : Join [Your DB] on “SQL Secondary” to [Your Availabilty Group]

 USE master
 GO
 ALTER DATABASE [Your DB] SET HADR AVAILABILITY GROUP = [Your Availabilty Group];

There you go! Now its working perfectly!