Ola Hallengren: encrypt, but verify? 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'\\&lt;SHARE&gt;\SQLBackup\Daily'
    ,@BackupType = 'FULL'
    ,@Verify = 'Y'
    ,@CheckSum = 'Y'
    ,@Encrypt = 'Y'
    ,@EncryptionAlgorithm = 'AES_256'
    ,@ServerCertificate = 'DBBackupEncryptCert&lt;SERVERNAME&gt;'
    ,@LogToTable = 'Y'


The back-up runs fine, but verification does not:

Command: RESTORE VERIFYONLY FROM DISK = N'\\&lt;SHARE&gt;\SQLBackup\Daily\&lt;SERVER&gt;\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'\\&lt;SHARE&gt;\SQLBackup\Daily\&lt;SERVER&gt;\master\FULL\&lt;SERVER&gt;_master_FULL_&lt;DATE&gt;_&lt;TIME&gt;.bak'


but this does:

use master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '&lt;PASSWORD&gt;';

RESTORE VERIFYONLY FROM DISK = N'\\&lt;SHARE&gt;\SQLBackup\Daily\&lt;SERVER&gt;\master\FULL\&lt;SERVER&gt;_master_FULL_&lt;DATE&gt;_&lt;TIME&gt;.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