Please create a master key in the database or open the master key in the session before performing this operation

Question

I get the following error on secondary replicas when trying to restore an encrypted backup even though the replica has the master key (dmk), service master key, certificates and private keys restored from the originating/primary server that generated the backup.

Msg 15581, Level 16, State 7, Line 137
Please create a master key in the database or open the master key in the session before performing this operation.
Msg 3013, Level 16, State 1, Line 137
VERIFY DATABASE is terminating abnormally.

To circumvent the error I open and close the master key around the operation like such. However, on the primary, I don't need to open and close the master key to do the operation.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyTest!M4st3rPass';
RESTORE VERIFYONLY FROM DISK = '\\FS1\SqlBackups\SQL01\SystemDbs\msdb_backup_2017_09_22_171915_6346240.bak' WITH FILE = 1, NOUNLOAD, NOREWIND;
CLOSE MASTER KEY ;

I believe this is because the primary has the backup history with the encryption thumbprint, but I am wondering if I am missing something else related to the secondaries.

However, after all, since the cert is restored on the secondaries I assign it to the SystemsDB Backup Maintenance Plan options for Backup Encryption, yet the job fails if I keep the Verify option checked for the same reason.

Source: Back Up Database Task
Executing query "BACKUP DATABASE [master] TO  DISK = N'\\FS1\SqlBac...".: 50% complete
End Progress  
Error: 2017-09-22 17:08:09.28
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
**Description**: Executing the query "declare @backupSetId as int  select @backupSetId =..." 
failed with the following error: "Please create a master key in the database or open the master key in the session before performing this operation.
VERIFY DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error 

Answer 1

Fixed.

Referenced: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

This paragraph gave it away:

The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

Ran the following on my secondary nodes.

1. Drop Certificate...
2. Drop master key
3. Create master key...
4. Create certificate from file...

Arrived at the solution after checking this.

--on primary, output: master 
select name from sys.databases where is_master_key_encrypted_by_server=1

--on secondary, output: nothing...
select name from sys.databases where is_master_key_encrypted_by_server=1

So I figured if I could get the master key to be encrypted by default by the service master key then this would automate the decryption.

--on secondary
drop certificate [BackupCertWithPK]
drop master key

--Skipped restore master key from file.
--Instead, I ran create master key with password.
create master key encryption by password = 'MyTest!Mast3rP4ss';

--verify by open/close.
open master key decryption by password = 'MyTest!Mast3rP4ss';
close master key;

--proceed to restore/create cert from file.
create cerfiticate [BackupCertWithPK] 
from file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.cer' 
with private key (file = '\\FS1\SqlBackups\SQL1\Donot_delete_SQL1-Primary_BackupCertWithPK.key' , decryption by password = 'key_Test!prim@ryP4ss') ; 

After this ran the above select again.

--on secondary, output: master, now there was hope again!
select name from sys.databases where is_master_key_encrypted_by_server=1

Finally, I re-ran my backup job with options set for Verify and Encryption successfully. Verify step did not fail nor prompted to open/close the master key.

The following simply worked as intended without needing to open/close the master key.

RESTORE VERIFYONLY FROM DISK = '\\FS1\SqlBackups\SQL01\SystemDbs\msdb_backup_2017_09_22_171915_6346240.bak' WITH FILE = 1, NOUNLOAD, NOREWIND;

Wohooo! Mission accomplished.