Encrypt database backup step by step guide

Recently I had the business need to put on a shared backup location some sensitive database backup. The backup location is protected but still there could be a case that somebody has access to the location or a tape.

This problem can be solved easily by encrypting backup files.

I know the procedure how to accomplish this task but still sometimes I have the need to remind me on some commands. So, to not google it every time I decided to write this article. Such tasks are rarely because if you setup a good backup probably you will not change it soon. Maybe the article will also help somebody else. Let’s start!

Creating encrypted database backups

We have a clean database server and a brand-new Test database on it. Let’s try to backup it.

  1. Open SSMS -> Connect to the server
  2. Right click on the database -> Tasks -> Back Up Database
  3. General
    1. Let’s specify a backup location (disk)
    2. Add a new backup file name
  4. Media Options
    1. Back up under a new media set, and erase all existing backup sets
    2. Specify new media set name – it is required to start with a new media set if you like to create an encrypted backup
  5. Backup Options
    1. Check Encrypt backup
    2. Choose the Algorithm (more complicated is better)
    3. Choose Certificate or Asymmetric key – ops, something is missing here

Backup - General

Backup - Media options

Backup - Backup options

Ok, to be able to create an encrypted backup we need a “Certificate or Asymmetric key” so let’s create one certificate.

CREATE CERTIFICATE BackupEncryptionCert WITH SUBJECT = 'BackupEncryptionCert';

The prerequisite to create a certificate is to create a master key in the database. So, the exception tells for itself. The Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

Msg 15581, Level 16, State 1, Line 23
Please create a master key in the database or open the master key in the session before performing this operation.

Let’s create a database master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomePassword12#';

Since it is always nice to have a backup, it is also nice to back up a created master key. We will back up the key on disk and encrypt it with a password.

BACKUP MASTER KEY TO FILE = 'C:\Temp\Master.key' ENCRYPTION BY PASSWORD = 'SomePassword12#';

Now when we are in a backup mood, let’s backup also the SQL instance service master key (SMK). The Service Master Key is the root of the SQL Server encryption hierarchy. The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key.

BACKUP SERVICE MASTER KEY TO FILE = 'C:\Temp\ServiceMaster.key' ENCRYPTION BY PASSWORD = 'SomePassword12#';

Ok, now let’s try to re-create the certificate.

CREATE CERTIFICATE BackupEncryptionCert WITH SUBJECT = 'BackupEncryptionCert';

And of course, let’s backup it since you will need it later to be able to restore the encrypted backup on another database server.

BACKUP CERTIFICATE BackupEncryptionCert 
TO FILE = 'C:\Temp\BackupEncryptionCert.cer' 
WITH PRIVATE KEY (
	FILE = 'C:\Temp\BackupEncryptionCert.key'
	, ENCRYPTION BY PASSWORD = 'SomePassword12#'
);

Here you can see all backed-up keys.

Let’s repeat our backup procedure and create the backup of our test database. In the backup options under “Choose Certificate or Asymmetric key” now the created certificate “BackupEncryptionCert” can be selected.

Encrypted backup created successfully!

Clear the environment

This is not a usual procedure but maybe you want to drop the created encryption certificate and the Database master key. This can be accomplished in the following way.

DROP CERTIFICATE BackupEncryptionCert;
DROP MASTER KEY;

The Service master key can be only regenerated and there is a possibility of data loss. More information you can find here.

Restoring encrypted database backups

Let’s try to restore the encrypted backup database file.

  1. Open SSMS -> Connect to the server
  2. Right click on the databases -> Restore Database…
  3. General
    1. Let’s specify a backup Source (Device)
    2. Add our encrypted database backup file (Test.bak)
    3. Ops, something is wrong here!!!

There is no information about our database we want to restore and there is an error.

Let’s try to read headers of our backup file.

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


 

This is the same error message seen in SSMS.

"Msg 33111, Level 16, State 3, Line 48
Cannot find server certificate with thumbprint '0x1C96BAF7D88553EE622724618775F934F71847A8'.
Msg 3013, Level 16, State 1, Line 48
RESTORE HEADERONLY is terminating abnormally."

The reason for this exception is that the backup file is encrypted, and the system can’t find a valid certificate. To be able to restore the encrypted backup file we must import the same certificate that was used to create it.

The first step is to create a Database master key on the restore location.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#';

Let’s import the certificate used for encryption from the drive (first copy the file from the first server).

CREATE CERTIFICATE BackupEncryptionCert 
FROM FILE = 'C:\Temp\BackupEncryptionCert.cer' 
WITH PRIVATE KEY (
	FILE = 'C:\Temp\BackupEncryptionCert.key'
	, DECRYPTION BY PASSWORD = 'SomePassword12#'
);

To be able to import the certificate you must have the certificate file, the private key and to know the password.

Now let’s see if we can read the headers of the backup file.

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

Yes, we can!

And finally, the restore of the database can be finished!

Hope that this article will help you when you plan to protect your data. And don’t forget to make regular backups of your databases!

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.65 kb)