Step by Step Guide to SQL Server Backup Encryption

When is it Necessary to Perform SQL Server Backup Encryption?

As database administrators, securing our data is one of our most important tasks. We certainly don't want someone to view our database at will without authorization, simply by restoring the backups to their own hardware, especially when we have sensitive data stored in there.

Therefore, in order not to leak the data, it’s almost a necessity to perform SQL Server Backup Encryption. I strongly suggest you to perform SQL Server Backup Encryption under these circumstances:

• You have sensitive data in your database
• You are going to store your database backups offsite, or on the cloud
• You want to restrict access to only those who have authorization

SQL Server Versions Backup Encyption Supported?

Before SQL Server 2014, Transparent Data Encryption (TDE) was commonly used to encrypt data at rest, however, it will significantly reduce query performance and increase CPU consumption.

Luckily since SQL Server 2014, the new Backup Encryption option allows us to encrypt the backup while creating, only after we specified an encryption algorithm and an encryptor to secure the encryption key. 

However, Backup Encryption is still not avaliable on SQL Server Express, SQL Server Web, and versions before SQL Server 2014. Therefore, if you are using SQL Server Express, Web, or you want to perform backup encryption in SQL Server 2012, 2008, etc, I suggest you choose the professional backup software which supports all versions.

How to Encrypt Backup in SQL Server 2014 and Later Versions

Before we start, there are 2 things we need to know.

1. Encrypted backups cannot be appended to an existing backup set. Therefore we need to back up to a new backup set.

2. We need to specify an encryption algorithm and an encryptor to secure the encryption key first. Here are the suppoted encyption algorithm and encryptors. In general we choose AES 256 and certificate for SQL Server Backup Encryption.

• Supported encryption algorithm: ES 128, AES 192, AES 256, and Triple DES.
• Encryptors: a certificate or an asymmetric key.

Prerequisites to SQL Server Backup Encryption

To encrypt the SQL Server backup while creating, we need a SMK, a DMK and a certificate, and back up them first. 

First let me explain you what are they and how SQL Server Backup Encryption works.

• Service Master Key (SMK): SMK is automatically generated and stored in the system master database when we install SQL Server, unique for each instance. It will be used to encrypt the Database Master Key.
• Database Master Key (DMK): DMK is unique to each system master database for each instance. DMK is used to protect the certificate or asymmetric key.
• Certificate: Certificate can contain a private key that is protected by the Database Master Key, or an asymmetric key (Better not choose it). It is used to encrypt the database backup.

✦ Step 1. Check the Existence of DMK

Launch SSMS and connect to your instance. Click New Query on the upper bar and input the following statements.

SELECT * FROM master.sys.symmetric_keys

Click Execute. In results you can check the presence of DMK. If there’s no, then we need to create one.

✦ Step 2. Create A DMK

Input the following statements and click Execute.

-- Create the Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'

✦ Step 3. Create A Certificate

-- Create Backup Certificate
USE master
GO
CREATE CERTIFICATE Certificate
WITH SUBJECT = 'SQL Backup Certificate';
GO

✦ Step 4. Backup the SMK

-- Backup the Service Master Key
USE master
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'filepath\SMKfilename.key'
ENCRYPTION BY PASSWORD = 'SMKpassword';
GO

✦ Step 5. Backup the DMK

-- Backup the Database Master Key
BACKUP MASTER KEY
TO FILE = 'filepath \DMKfilename.key'
ENCRYPTION BY PASSWORD = 'DMKpassword';
GO

✦ Step 6. Backup the Certificate

-- Backup the Certificate
BACKUP CERTIFICATE Certificate
TO FILE = 'filepath \CertificateFilename.cer'
WITH PRIVATE KEY(
FILE = 'filepath \CertificateKeyFilename.key',
ENCRYPTION BY PASSWORD = 'CertificatePassword');
GO

Now we’ve created 4 files used for SQL Server Backup Encryption.

3 Common Ways to Encrypt SQL Database Backup

This part introduces how to enrypt SQL database backup. Basically, after you’ve created a DMK and a certificate, backed up them, you only need to do 2 more things, which is to select the Backup Encryption option, and Back up to another media set in the normal backup procedure. The rest steps are all the same.

✦ Way 1. SQL Server Backup Encryption using T-SQL

1. Launch SSMS and connect to your instance. Click New Query on the upper bar to input the following statements.

-- backup the database with encryption
BACKUP DATABASE databasename
TO DISK = 'filepath\filename.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = certificatename)

2. Then click Execute to create an encrypted backup.

✦ Way 2. SQL Server Backup Encryption with SSMS GUI

1. Launch SSMS and connect to your instance. Right-click the database name you want to back up and select Tasks > Back Up…

2. Select the Backup type and Destination in General page.

3. Then turn to Media Options page. Select Back up to a new media set, and erase all existing backup sets. Name the new set and write some description.

4. Turn to Backup Options page, check Encrypt backup, select an Encryption Algorithm and a Certificate or Asymmetric key.

5. Then click OK to start the backup with encryption.

✦ Way 3. SQL Server Backup Encryption using Maintenance Plan

1. Launch SSMS and connect to your instance. Expand Management menu and right-click Maintenance Plans, select New Maintenance Plan…

2. Click Toolbox next to Object Explorer, double-click Back Up Database Task to create a new backup task.

3. Double-click the task in the right blank to Edit it.

4. Select Backup type and single or multiple Databases in General page, and select a Destination.

5. Check Backup encryption in Options page, and select the Algorithm and Certificate or Asymmetric Key.

6. Then click OK to save the changes. Now you can save the plan and run it in SQL Server Agent jobs.

One Effortless Way to encrypt SQL backup (all versions supported)

While SQL Server Backup Encryption is convenient, it is are not available in SQL Server Express and versions before 2014. Therefore, you may need a third-party software that supports all versions to do it for you. I recommend you to apply AOMEI Centralized Backupper Database . Here are the reasons:

• Effortless Procedure: AOMEI Centralized Backupper Database simplifies the procedures of SQL Server database backup into 3 main steps. With its intuitive interface, you can encrypt database backups of all versions while creating, by simple clicks and password settings.
• Remotely Backup Another Computer within LAN: It allows you to centrally control all laptops, workstations, and servers within LAN from one controlling computer. You can back up single or multiple SQL databases remotely, then you don’t have to take the risk of data leakage for moving the backups.
• Remotely Restore to Another Computer: You can also directly restore the SQL backup to another controlled computer within LAN.

Here's a 30-day free trial for you: 

To properly use Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.

For those computers which download client program manually, you need to request control over them for further operations.

Click Computers > Uncontrolled Computers, select single or multiple client computers and click Request Control on the upper bar.

A permission request window will pop up on their computers, once all permissions are received, you can manage them at any time.

How to Create an Encrypted SQL Server Backup?

1.Click Tasks > New Task and select SQL Server Backup to create a new backup task.

In pop-up window, you can change the task name by moving your cursor to the task name and click it.

Then follow the 3 steps beneath the task name.

2. Step 1, click Add Computers to add the controlled client computer you want to back up with.

3. Step 2, click Add to select single or multiple SQL instances and databases you want to back up.

4. Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

5. Then click Settings. Check Enable encryption for backups in General page, Type and Confirm Password.

You can enable email notification and select a Compression level in Settings.

Click Schedule next to Settings, you can also specify days and time to run this task automatically.

Click Advanced, you can choose to perform full backup or SQL database differential backup.

6. Now you can click Start Backup to select Only create the task, or Create and execute the task.

Once created, the tasks will be listed in Tasks separately for further edit or restore. When restoring, it will list all the backups, and distinguish them by the backup type and dates in their names.

You can choose to restore the backups to the local server, or restore SQL database to another server within LAN.

How to Check if A SQL Server Backup is Encrypted?

Launch SSMS and connect to your instance. Click New Query on the upper bar and input the following statements.

RESTORE HEADERONLY
 FROM DISK = N'filepath\Filename.bak'; 
GO

Click Execute, and check the values in EncryptorThumbprint and EncryptorType columns. If it is not encrypted, they will be NULL.

Summary

Backup Encryption is a useful feature to avoid data leakage. In this article I analyzed when should you perform SQL Server Backup Encryption, and introduced 3 ways to implement it.

However, it is only started from SQL Server 2014, and is not available on Express version. So, if you are using Express or versions before 2014, I suggest you to choose AOMEI Centralized Backupper Database Edition to perform backup encryption in SQL Server. It can save you a lot of effort.

Except for SQL Server Backup, this software centralized many other practical features such like File, System, Disk, and Partition Backup. All of them can be encrypted effortlessly.