The fundamentals of SQL Server Backup Encryption

By James
3rd May 2019

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, if you need assistance then give us a call on 0800 389 4051, and we’ll help you on your way.

 

James Newton Brady
James Newton Brady

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.

Share

<< Back to resources

Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
I have worked with the WellData team for many years now and they have always delivered. I would highly recommend them to any business looking for best-value outsourced Expert DBA’s. Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment. Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19