The fundamentals of SQL Server Backup Encryption

Most companies would agree that their data is one of the most important components of their business.

From customer information to internal application records, it’s vital for business data to be protected.

Setting a backup

This is certainly the very first step to take in order to protect your data. It’s likely that you have backups included in your file system backups or VM snapshots on longer retention periods, which are particularly useful if you need to restore further back than the past few days. The backups may also be copied to cloud storage or stored off-site, which are incredibly important as they allow you to restore data even in the event of a site disaster.

These are all positive measures, but they come at a risk: you might end up having backup copies of your invaluable data in various locations. The problem is that while the database server may be secured, it is unlikely that you have full control over those who have access to the VM snapshots or cloud storage; could drives potentially be lost when in transit off-site? If that happens, anyone who gets hold of your backups can restore them to gain access to your business’s data without your consent or even your knowledge.

That’s where backup encryption can be invaluable. Database backup encryption prevents unauthorised access to business data even if the backup file is stolen. There are a number of reliable third-party tools that perform backup encryption, but since SQL Server 2014 native backup encryption has been available in both Enterprise and Standard editions – so no additional licenses for third-party tools are required in order to encrypt your database backups.

Interestingly though, even though backup encryption has been available in the past three versions of SQL Server, it’s not a feature that we see being widely used. The problem might be that it appears a complex procedure, yet that is not necessarily the case.

Three easy steps

By following these 3 easy steps you will be able to configure backup encryption. We would encourage you to test this out in your test instance and consider including backup encryption as a default element in your backup strategy

1. Creating a master key
2. Creating a certificate (and backing up that certificate)
3. Executing a database backup with encryption

There are many articles that explore these backup encryption steps in more detail, but the aim of this section is to provide the commands necessary as concisely as possible.

Please test this out in your test environment first, use stronger passwords than in these examples (and store them in a password vault) and amend file paths were necessary. This example only considers a standalone instance of SQL Server.

Configuring SQL Server Backup Encryption

1. Create a Master Key

USE MASTER;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘masterkeypassword’;

It is recommended that the master key is backed up as soon as it has been created. This is done by using the following T-SQL:

BACKUP MASTER KEY TO FILE = ‘C:\temp\mk2.bak’ ENCRYPTION BY PASSWORD = ‘masterkeybackuppassword’;

2. Create a Certificate

CREATE CERTIFICATE backup_certificate
WITH SUBJECT = ‘backup encryption certificate’;

Important: it is vitally important to back up this certificate. The certificate can be backed up using the following T-SQL:

BACKUP CERTIFICATE backup_certificate
TO FILE = ‘C:\temp\backupcertificate.cer’
WITH PRIVATE KEY (FILE = ‘C:\temp\backupcertificatepk.pvk’, ENCRYPTION BY PASSWORD = ‘privatekeypassword’);

This will create two files, the certificate backup and the private key (the private key is encrypting your certificate backup file).

Important:
• These two files are as important as the database backup file. Without these two files, you would not be able to restore the encrypted database backup to a new instance (including the scenario where the current instance required a rebuild).
• Ensure the certificate backup file and private key are stored in a separate location to the database backup file, i.e. on another server or separate storage area and off-site.
• It is best practise to renew the certificate. By default, a certificate will expire after one year. It is still possible to use an expired certificate to restore an encrypted database backup, but new database backups will report failure due to the expired certificate. To resolve this error, simply create a new certificate and amend the certificate name in the next step.

3. Executing a backup with encryption (using the ‘test_encryption’ test database as an example)

BACKUP DATABASE [test_encryption]
TO DISK = N’C:\temp\test_encryption.bak’
WITH FORMAT, INIT, SKIP, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [backup_certificate]);

That’s it, we have now successfully created an encrypted database backup.

Restoring an Encrypted Backup to another Instance

If we attempted to restore this encrypted database backup to a new instance (i.e. an instance that does not have the certificate we used to encrypt the backup), then we would get an error similar to this:

RESTORE DATABASE [test_encryption]
FROM DISK = N’C:\temp\test_encryption.bak’
WITH RECOVERY;

Msg 33111, Level 16, State 3, Line 20
Cannot find server certificate with thumbprint ‘0x9D8F11B623D5C7B94E64D1889ECCAD61C52A025D’.

Msg 3013, Level 16, State 1, Line 20
RESTORE DATABASE is terminating abnormally

To resolve this error, we need to restore the certificate using our certificate backup file and private key:

CREATE CERTIFICATE backup_certificate
FROM FILE = ‘C:\temp\backupcertificate.cer’
WITH PRIVATE KEY(FILE = ‘C:\temp\backupcertificatepk.pvk’, DECRYPTION BY PASSWORD = ‘privatekeypassword’);

(If the new instance does not have a master key, then run the command in the Create a Master Key step above.)

Now we can successfully restore the encrypted database backup:

RESTORE DATABASE [test_encryption]
FROM DISK = N’C:\temp\test_encryption.bak’
WITH REPLACE, RECOVERY;

Hopefully, this article has shown that enabling backup encryption is a simple process and highlights the key factors to be aware of.

Good luck encrypting your backups, and if you need SQL Server Support then give us a call on 0800 389 4051, and we’ll help you on your way.

WellData’s consultant database administrator, James, works closely with clients and the rest of the WellData team. A certified Microsoft technical specialist, he continually strives to provide the best customer service and technical advice in a friendly and approachable manner.