Password is required when adding a database to AG group if the database has a master key

Moved this post from my old blog.

 When you are trying add a database having a ‘master key’ to a AG group, you will see the message in SSMS.

You can’t move to next step until you put the correct password of the ‘master key’.

This is an requirement of SSMS to help you manage SQL Server database master key password in secondary replicas.

Why we have this requirement?

When a master key is created, it’s encrypted by both password and service master key.

create MASTER KEY ENCRYPTION BY PASSWORD = ‘Password1’;

This master key will be open automatically when it’s need for decryption or encryption. In this case, it is not necessary to use the ‘OPEN Master Key’ T-SQL statement.

However, when a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key.

Let’s go back to SQL Server Alwayson Availability group scenario. If the password is not provided, the database is able to restored in secondary replicas. However, the database master key can’t be opened automatically because it’s not managed by the Service Master key in the secondary replica. It may cause application fail.

To avoid this issue, SQL Server (since 2016) has this enhancement to open the database master key automatically in secondary replicas.

The wizard will call stored procedure sp_control_dbmasterkey_password in all replicas to create an credential containing the password of master key.

When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.

This is transparent to application. The application is able to use encrypt/decrypt in secondary replicas as it does in primary replica without modifying any code.

Here is an example:

create database dbtest
go
use dbtest
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';
---This database master key will be encrypted by service master key automatically.
go
CREATE CERTIFICATE [certTest] WITH SUBJECT = 'certTest2019'
---This certificate will be encrypted by database master key
go
CREATE SYMMETRIC KEY [symkey_Test] WITH ALGORITHM =AES_192 ENCRYPTION BY CERTIFICATE [certTest]
---this symmetric key will be encrypted by the certificate
go

Let’s create a stored procedure used to encrypt/decrypt. Assume application will call the stored procedure to do transactions.

create proc procTest
as
OPEN SYMMETRIC KEY [symkey_Test]  DECRYPTION BY CERTIFICATE [certTest]
declare @blob varbinary(1000)
declare @pt varchar(1000)
SET @blob = encryptbykey( key_guid( 'symkey_Test'), 'data' )
SET @pt = convert( varchar(1000), decryptbykey( @blob ))
SELECT @pt, @blob
close SYMMETRIC KEY [symkey_Test]
go

Once the database is stored to a different SQL Server instance. Running the ‘procTest’ will fails because the database master key can’t be opened by the SQL Server instance.

In your case, if master key is not used(encryption/description/service broker, etc ), cx can remove it.

An known issue: ‘sp_control_dbmasterkey_password’ is only executed if ‘full database and log backup’ option is checked in ‘Select Initial Data Synchronization’ section

‘Checking password of the database master key’ is skipped if you use other options. And ‘sp_control_dbmasterkey_password’ is not called.