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 

Lucic

Answer 1

Fixed.

Referenced: https://learn.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.

Lucic

Answer 2

I had the same situation, but instead of recreating the MDK I ran the following to fix the issue: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Hammons

Caution: I tried this solution first, and now I no longer have access to my database – Metcalfe

This worked fine for me. but how is it fixing? When I run SELECT d.is_master_key_encrypted_by_server FROM sys.databases AS d I just see value is 1 for just master database – Segalman

Answer 3

I am not certain if this is exactly what you are looking for, but the OPEN MASTER KEY remarks had something that seemed relevant.

You will 100% want to test this not in production, but it seems that once the master key has been opened, you have the option to not require that with the ALTER MASTER KEY REGENERATE command.

If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

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 (SMK).

When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

https://learn.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql

Hilltop

Thx for the help CK, I tried the Alter master key regen but didn't work, I still get the same error. – Lucic

Answer 4

One thing I noticed is if you recreate a DB (example restore on new database or new server) the name of DB is different, so you have to drop and create again Certificates and Symmetric keys, I lost time for this problem but fixed recreating them.

Mcleroy

Answer 5

In my case the issue was that the service master key on the secondary replica was different to the service master key on the primary. So when I failed over to the secondary, I would get the "Please create a master key in the database..." error. If I ran the command ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY on the server, then it would resolve the issue. But when I then did a failover back to the original server, I would get the "Please create a master key in the database..." error again!

My solution was to do the following:

1. In PowerShell, backup the service master key from the primary server using the Backup-DbaServiceMasterKey command. Something like this:

$backupPath = '\\path\to\backup\location'
$encryptionPassword = ConvertTo-SecureString -AsPlainText 'myStrongPassword' -Force
Backup-DbaServiceMasterKey -SqlInstance MyPrimaryServer -Path $backupPath -
SecurePassword $encryptionPassword

2. In SSMS, connect to the secondary server and run this:

RESTORE SERVICE MASTER KEY 
    FROM FILE = '<enter the path to your backed up key>'   
    DECRYPTION BY PASSWORD = '<use the same password as the previous step>' FORCE
GO  

Rhodarhodamine