I've been using Ola Hallengren's scripts for a few years now, and a recent upgrade to MSSQL2017 has allowed me to start testing encrypted back-ups. Before anyone asks: yes, master keys and encryption certificates have been backed up and stored in our password manager.
I'm running into an issue with the following parameter combination:
EXECUTE [OlaHallengren].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES' ,@Directory = N'\\<SHARE>\SQLBackup\Daily' ,@BackupType = 'FULL' ,@Verify = 'Y' ,@CheckSum = 'Y' ,@Encrypt = 'Y' ,@EncryptionAlgorithm = 'AES_256' ,@ServerCertificate = 'DBBackupEncryptCert<SERVERNAME>' ,@LogToTable = 'Y'
The back-up runs fine, but verification does not:
Command: RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\... Process Exit Code 1. The step failed.
It seems to be an issue with Ola Hallengren's script not being able to open the master key, because this doesn't work:
RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak'
but this does:
use master; OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PASSWORD>'; RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak' CLOSE MASTER KEY
Am I trying to have my cake and eat it, too? Or is there a way to have both encryption and verification with Ola's scripts?
I've found the root cause and solution.
Ola Hallengren couldn't get to the back-up encryption certificate because doesn't know the Database Master Key (DMK) encryption password. The DMK can also be encrypted using the Service Master Key (SMK), which is automatically opened for a connection with sufficient privileges.
I've added SMK encryption to the DMK, which allows Ola's scripts to access the back-up encryption certificate.
I guess it only works if it's turtles all the way down. :-P