Recently I received an email from a client experimenting with Availability Groups for the first time in a development environment that had run into an issue with adding one database out of fifteen to the Availability Group in the UI. The database in question had Password required beside it in the UI instead of the Meets prerequisites link all the other databases had. Clicking the Password required link produced a non-informative popup dialog saying “This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.”
This was something I ran into a couple of years ago, and I did an Insider Demo video of this UI behavior for our SQLskills Insider Newsletter to demonstrate how the UI changed in SSMS 2016 to support encrypted databases and databases with encrypted content not using TDE as well. This is not caused by TDE, but by a database master key (DMK) that is protecting something like a certificate or an encrypted column in a table. The UI actually has a Password column next to the Status column, though it’s not very intuitive or clear that there are text boxes available for each database that you can click on to provide the password for the database master key.
In the screenshot above, the SSISDB requires a password to be added to the Availability Group, and the password has been provided in the Password column of the UI. To do this, actually requires a series of steps that aren’t exactly intuitive.
- First, double click in the Password column and it will become a text box that is editable.
- Type the password in.
- Click Refresh at the bottom of the screen which will make the check box for the database enabled.
- Check the checkbox for the database.
- Click Refresh a second time, which will enable the Next button at the bottom.
- Click Next to progress to the next screen in the wizard.
This took me 20 minutes to figure out in the back of the room during one of our Immersion Events after SQL Server 2016 released so I could answer a question for someone that wasn’t even related to this UI functionality about the SSISDB catalog and how it behaved in an Availability Group if an failover occurs during package execution.
7 thoughts on “Database Master Keys and Availability Groups”
Greaat article and this is something I have run into with a client. As you say the GUI is far from intuitive.
Another solution, that works for SQL 2016 only, is to make use of the Automatic Seeding option which does not have the requirement to enter the password. Since the GUI has the Automatic Seeding option on the following step you can’t use the Wizard but scripting it using T-SQL to configure and add the database does work.
Have you validated the failover and availability of the master keys for those databases? The credential for the master key password still has to be created on each of the secondary replica’s using sp_control_dbmasterkey_password, or the key can’t be opened and re-encrypted by the new SMK after a failure.
When TDE and DB encryption are not configured this still occurs, is this just a bug in SSMS? I can still add the SSISdb to the AG with;
ALTER AVAILABILITY GROUP MyAG ADD DATABASE SSISDB;
is the above masterkey creation on the secondary required if your not using encryption as it still ask for password in SSMS???
what a strangeness we have stumbled upon.
SSISDB uses encryption and it requires the master key password to create the credential for failover. Just adding a database to the AG doesn’t mean it will work completely when you fail it over. You need to test it entirely.
Thanks, Jonathan, this is very helpful. The other issue is that if TDE is enabled, you are unable to select the database. I had to disable encryption at the database level before I was able to select Next to progress.
Thanks Jonathan, what a lifesaver once again. Just the information I was needing. Though I’m not using TDE but rather have encrypted data with Symmetric Keys. Worked just the same.