Day 4 – Backup Encryption with Backup-DbaDatabase

Now, we all take our db security very seriously (or at least I hope we do?). But a lot of people don’t think this through to their database backups

A database backup is a full copy of the database, by definition all of the data is in there

So if someone get’s their hands on it? Then they may as well have dbo on your production database, as they can just restore it on their own SQL Instance and go to town on the data.

That is, unless you’ve implemented some security around your backups

Backup Security

Before we get into the dbatools aspects, I’d like to cover some basic backup security best practices.

As mentioned, your backups are your data. You want to make sure that no one who doesn’t need access can get to them.

Consider if you want all SQL Server instances to be able to read all the backups from one location. If one Instance is compromised then it can be used to read all of those backups and then export the data. This can be overcome using Backup Encryption and having seperate keys/certificates on each instance or by granting Write Only permissions to the SQL Server instances.

Consider if you want all SQL Sever instances to write to one location. In these days of Ransomware, do you want a single compromised instance to cause ALL of you SQL backups to become useless? Backup Encryption won’t save you here unfortunately, as all you’ve have is double encrypted backup, and it has happened 🙁

All of these choices can make restores harder if you need to assign permissions first, but only you and your organisation can decide on your risk profile

Backup Encryption Setup

There are 2 ways to encrypt a SQL Server backup file:

  • Certificate
  • Asymmetric Key

Currentlty dbatools only support Certificate encrypted backups. Asymmetric keys will come at some point, just that they have an infrastructure overhead that we’d need to look in to for testing.

To encrypt a backup with a certificate we need to create 2 things, in this order:

  1. Database Master Key
  2. Database Certificate

Both of these need to reside in the Master database. Unsuprisingly dbatools has the functions to set this up.

First we need to generate a secure password object using a secure password (or some random text from a blog):

    $securePass = ConvertTo-SecureString "Unsupr1s1ngly dbat00ls has th3 funct10ns t0 s3t th1s up" -AsPlainText -Force

Keep a record of the password. Seriously! You do not want to loose that.

Which then we pass into New-DbaDbMasterKey to generate the key we’ll use to sign the certificate

    New-DbaDbMasterKey -SqlInstance server1\instance -Database Master -SecurePassword $securePass -confirm:$false

The $confirm:$false is just there to hide the ‘Do you want to’ prompt. Now we have a key we can use it generate a certificate:

    New-DbaDbCertificate -SqlInstance $script:instance2 -Database master -Name BackupCert

Now we have a certificate we can use to encrypt our backups, and more importantly we have a certificate we can use to decrypt our backups when we do a restore. That’s right, that certificate is now a CRITICAL part of your Disaster Recovery plan. Not only does it stop bad peeps using your backups, it seriously ruins the day of good DBAs if they loose it!

So what do DBAs do with data they want to keep safe? They back it up of course! Do this before> you take any backups using the cert

So lets do that, and this is why I told you to not loose that password earlier. First we backup up the master key to a file:

    Backup-DbaDbMasterKey -SqlInstance server1\Instance -Database Master -Path c:\master.key -SecurePassword $securePass

The we backup the Certificate:

    Backup-DbaDbCertificate -SqlInstance server1\Instance -Database Master -Path c:\master.cert -Certificate BackupCert -SecurePassword $securePass

Now, stash those 2 files and the password somewhere secure.

Backing up

Now you’ve got a certicate you’re good to go. You can use your normal Backup-DbaDatabase parameters, and just add the 2 new ones.

EncryptionAlgorithm – The Algorithms we offer are ‘AES128’, ‘AES192’, ‘AES256’ and ‘TRIPLEDES’. This is a subset of what SQL Server has supported, but these are the currently recommended Algorithms as of SQL Server 2016 (MS – Choose An Encryption Algorithm). Generally an encryption algorithm with a longer key length will be hard to crack or brute force, but it will require more CPU overhead to perform then encryption, so you’ll need to see what works for you.

EncryptionCertificat – This is the name of the certficate you created previously.

    $output = Backup-DbaDatabase -SqlInstance server1\instance -Database Master -Path c:\backups\ -EncryptionAlgorithm AES192 -EcryptionCertificate BackupCert

Will perform a standard backup of Master, just encrypted. If you ever want to check if the backup is encrypted then you can check the output like this:

    $output | Select-Object KeyAlgorithm, EncryptorType, EncryptorThumbprint

For my example above I get the following output when I run that:

    KeyAlgorithm          : aes_128
    EncryptorThumbprint   : 176 239 246 51 126 152 164 248 113 202 23 2 47 163 129 36 24 253 118 84
    EncryptorType         : CERTIFICATE


Your backup IS your database. If you’re securing your database you need to secure your backups. NTFS permissions will get you so far, encryption will get you the rest of the way. dbatools makes it easy to setup and use, so there’s very few reasons not to. Just remember to keep hold of the password and certificate backups!

All posts in this series can be found at 31 Days of dbatools Backup and Restores