Ola Hallengren: encrypt, but verify?

by Laurens Bakker   Last Updated June 19, 2018 15:06 PM

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?



Answers 1


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

Laurens Bakker
Laurens Bakker
June 19, 2018 14:54 PM

Related Questions





Striped Olga Backup Job Not Running

Updated February 08, 2018 09:06 AM

Ola Hallengren solution, backups not cleaning up

Updated March 28, 2018 07:06 AM