5

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?

  • Ola has his scripts on GitHub. If you'd like to request this as a feature, that's the best place to do it. – Erik Darling Jun 19 '18 at 14:17
  • That's why I posted here first. I didn't see anything in the docs there saying "you can't verify if you encrypt", so I hoped I was making a mistake. – Laurens Bakker Jun 19 '18 at 14:20
  • Gotcha. Yeah, it may be an oversight in the docs. Looking through the code, I don't see anything that checks for encryption when the verify string is built up. – Erik Darling Jun 19 '18 at 14:27
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.