We’ve seen in the previous posts that TDE is a simple way of protecting your at-rest data. There may however be times where you can’t or don’t want to use TDE. The main scenario for this is where you are on a version of SQL Server before 2019 (when TDE was made available in standard edition) and you don’t want to pay for the enterprise version which has a high price tag associated with it.

When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.

From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.

Backup Encryption has a lot in common with TDE in terms of the objects required. The encryption hierarchy is the same; you require a DMK and a certificate with a public/private key pair. In theory you can use an asymmetric key instead of a certificate, but this has the disadvantage that you can’t export the asymmetric key – which means you will struggle to restore your database backup to a different server. As such, for the sake of the examples that follow, we’ll just look at the certificate option.

You may however choose to use an asymmetric key if you wish to use Extensible Key Management (EKM) and store the key externally to your SQL Server. We’ll look at EKM in a later post.

Setting Up Backup Encryption

As mentioned, the prerequisites for Backup Encryption are the same as for TDE. We’ll go over creating them again here, but a little more briefly this time. Refer to Setting up TDE for more information.

Creating a Test Database

We’ll start with creating a sample database that we want to backup. You can skip this step if you just want to work with an existing database. We’ll use basically the same database we used for the TDE examples, just with a different name. The following SQL creates the database and populates it with test data:

CREATE DATABASE TestBackupEncryption;
GO
USE TestBackupEncryption;
CREATE TABLE dbo.SomeData(Id INT IDENTITY(1,1), SomeText VARCHAR(255));
GO
INSERT INTO dbo.SomeData (SomeText)
SELECT TOP 1000000
('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c
CROSS JOIN sys.objects d;
GO 100

Create the Database Master Key (DMK)

You must have a DMK, which resides in the master database, and you can create it with the following code:

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';

You should also backup the DMK using the following command:

BACKUP MASTER KEY TO FILE = 'C:\Test\MyDMK'
ENCRYPTION BY PASSWORD = 'UseAnotherStrongPasswordHere!£$7';

Creating the Certificate

You also require a certificate in the master database which has an associated public/private key pair. Unlike TDE, in the case of Backup Encryption, this key pair will be used to directly encrypt the backup using asymmetric encryption. There is no separate Database Encryption Key required. You create the certificate with this SQL:

USE master;
CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'Certificate used for backup encryption';

You should take backups of the certificate and private key and keep them safe if you ever want to be able to restore your backups to another server. Here is the SQL to backup these objects:

BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = 'C:\Test\BackupEncryptionCert.cer'
WITH PRIVATE KEY
(
FILE = 'C:\Test\BackupEncryptionCert_PrivateKeyFile.pvk',
ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7'
);

Permissions

It’s possible that the account you generally use for taking backups doesn’t have sysadmin permissions on the server. If that is the case, then there are some additional permissions required. The account needs the db_backupoperator role in each database being backed up, but that should already be in place. The only additional permission required is that the account must have the VIEW DEFINITION permission on the certificate. You can assign that permission with this SQL:

USE master;
GRANT VIEW DEFINITION ON CERTIFICATE::BackupEncryptionCert
TO [MyBackupAccount];

That’s all we need to do before we are ready to start encrypting our backups.

Working with Encrypted Backups

Now that we have all the objects in place to encrypt our backups, we can look at how you take a backup with encryption enabled and how you restore an encrypted backup.

Taking an Encrypted Backup

It is possible to encrypt any of the backup types – FULL, DIFFERENTIAL, or LOG. In practice if you are using Backup Encryption, you are likely to want to make sure all are encrypted. The syntax is the same in each case though, so we’ll just look at FULL backups. This is the backup command with encryption specified:

BACKUP DATABASE TestBackupEncryption
TO DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak'
WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert);

You can see we specify the algorithm. As with TDE, AES_256 is recommended (Advanced Encryption Standard with a 256-bit key). We also specify which certificate to use.

We can view data about the backup using the RESTORE HEADERONLY command, which will include information about encryption. Here is the code for that:

RESTORE HEADERONLY
FROM DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak';

This returns us a lot of information, so I won’t include the full set of columns. Relevant to encryption though, we will see the following:

KeyAlgorithm – aes_256

EncryptorThumbprint – 0xA2E4A2A29182054B2F97FCD9954FA9349B4351EC

EncryptorType – CERTIFICATE

You can use this if you need to be able to check whether a particular backup is encrypted or not.

Restoring an Encrypted Backup

Restoring an encrypted backup is the same as restoring any other backup – as long as the certificate used to encrypt the backup exists on the server.

If you are restoring to a different server, you will need to restore a copy of the certificate and private key from the backup taken before you can restore the encrypted database (the server must also have a DMK before you can do this). This is the same command we covered in Migrating or Recovering a TDE Protected Database:

USE master;
CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = 'C:\Test\BackupEncryptionCert.cer'
WITH PRIVATE KEY
(
FILE = 'C:\Test\BackupEncryptionCert_PrivateKeyFile.pvk',
DECRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7'
);

Then you can simply restore the database as normal with the following command:

RESTORE DATABASE TestBackupEncryption
FROM DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak';

If you get an error, it is likely to be because the certificate doesn’t exist – for instance, if you have restored the wrong one:

Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint '0xA2E4A2A29182054B2F97FCD9954FA9349B4351EC'. Msg 3013, Level 16, State 1, Line 25

RESTORE DATABASE is terminating abnormally.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.