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.