Understanding Database Backup Encryption in SQL Server

Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. The data literally resides on someone else’s infrastructure. Data security has to be thought of from a different perspective now. If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware. What good, then, is it to lock the doors of your own equipment, when the soul has already departed?

Security best-practices are in place in order to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

Earlier versions of SQL Server had a limitation on this security feature; we had to use third-party solutions to encrypt and compress the backup files. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability—now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms!

In this article, I will discuss backup encryption application internals, how this feature is applied with the latest versions of SQL, the importance of security, how to recover/restore the certificate and the database in case of system/database failure, asymmetric key and EKM provider, and give step-by-step examples of the process of demonstrating the encryption and the recovery of a backup.

In this article:

Key challenges in securing data
What database encryption is, and why it’s critical in today’s data environment
The two classifications of database backup encryption
The benefits of implementing a database backup off-site or on the cloud
The impact of database backup and restore/recovery

Prerequisites

SQL Server 2014+ Enterprise or Standard Edition
Write access to a local/remote file system
Storage with adequate space to create a backup of the database

Benefits

Encryption for native SQL Managed Backup
Security and integrity of the backup
It can also be used for databases that are encrypted using TDE
Additional security for off-site backups
Use of various encryption algorithms, which provides you with flexibility in selecting an algorithm that aligns with your requirements
Use of asymmetric key to manage and integrate security with EKM providers

Feature Support – SQL [2014-2016]

Feature	Enterprise	Standard	Web	Express with Advanced Services	Express
Encrypted backup	Yes	Yes	No	No	No

Database Backup with Encryption

Almost every organization has a challenge of protecting the data. It is almost impossible to run a business without protecting the sensitive data. This feature comes handy where backups are managed and handled at a remote site or cloud. In order to encrypt the backup, we will need a certificate. And, in order to create a certificate, we will need a master key for the database.

Demonstration

Let’s see how we can create a secure backup for off-site storage. We’ll do that by creating a new database. Let’s call it “SQLShack”. I’m going to switch into the SQLShack database to create a sample table to hold dummy data which are needed to demonstrate the Tail Log backup process. A stored procedure is created to generate the sample data. On execution of the stored procedure, the data will be fed to the table.

-- create a new database for this example

CREATE DATABASE SQLShack;

USE SQLShack;

-- insert some data

CREATE TABLE SQLShackTable (

ID int IDENTITY(1,1000) PRIMARY KEY NOT NULL,

value int

);

CREATE PROCEDURE InsertSQLShackTable

DECLARE @i int = 1

WHILE @i <100

BEGIN

INSERT SQLShackTable (value) VALUES (@i)

Set @i +=1

END

EXECUTE InsertSQLShackTable;

SELECT * FROM SQLShackTable;

Generate Master Key

Now, I’ll change the current context of the database to Master. Next, we’re going to create a master key and the certificate on our server.

USE MASTER;

-- create master key and certificate

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@Api1401@2015!!';

Create Certificate

The certificate “SQLShackDBCert” will be created with a subject line, which’ll just appear in the metadata for the certificate, of SQLShackDBCert Backup Certificate. I’ll execute the below line to create the certificate.

Now, since we’re working in the master database, I can find that certificate created in the System database. Browsing the Object Explorer, expanding master, going down to Security, and then opening up the Certificates folder tells that the certificate is created.

In order protect the data from failure event; make sure that back-up the certificate to an external file. Let’s backup the certificate using the BACKUP CERTIFICATE command, the name of the certificate is SQLShackDBCert, and we’ll export it to a file to remote secured location

CREATE CERTIFICATE SQLShackDBCert

WITH SUBJECT = 'SQLShackDB Backup Certificate';

The name of the certificate file is “f:\Program Files\SQLShackDBCert.cert”. We’d also want to export the private key file, “f:\Program Files\SQLShackDBCert.key” which will encrypt the certificate file. Finally, we’ll protect the entire thing with a strong password. Let’s create the backup of the certificate at a secured offsite location

-- export the backup certificate to a file

BACKUP CERTIFICATE SQLShackDBCert TO FILE = 'f:\Program Files\SQLShackDBCert.cert'

WITH PRIVATE KEY (

FILE = 'f:\Program Files\SQLShackDBCert.key',

ENCRYPTION BY PASSWORD = 'Api1401@2015!!')

If you get an error message at this point stating that your password doesn’t meet Windows’ policy requirements, it simply means that your installation of Windows is configured to require stronger passwords. Make sure you enter a strong password.

Database Backup

Next, I’m going to back-up the SQLShack database with the encryption protocols enabled. The AES_256 encryption is a very strong and a recommended encryption algorithm. We’re going to encrypt this backup with the server certificate that we just created.

-- backup the database with encryption

BACKUP DATABASE SQLShack

TO DISK = 'g:\Program Files\SQLShack.bak'

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = SQLShackDBCert)

Database Recovery

This section discusses the demonstration of the Tail Log backup using database encryption. The data is inserted after a full backup of SQLShack. We’re going to drop the database SQLShack, and the certificate file, just to simulate movement to a new, clean instance of SQL Server.

-- insert additional records

USE SQLShack;

EXECUTE InsertSQLShackTable;

Let’s demonstrate the recovery process.

Insert sample data
Simulate database corruption by detaching the database
Delete the file from the drive
Bring the database back online
Refresh the database
Initiate the Tail Log backup with database encryption

-- take SQLShack offline

ALTER DATABASE SQLShack SET OFFLINE WITH ROLLBACK IMMEDIATE

-- delete .mdf data file from the hard drive

-- attempt to take TailLogDB online

USE master;

BACKUP LOG SQLShack

TO DISK = 'g:\Program Files\SQLShackTailLogDB.log'

WITH CONTINUE_AFTER_ERROR,ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = SQLShackDBCert)

Check the backup meta-data

If you use encryption during the backup you wouldn’t be able to append the backup to an existing media set. The restoration just works like normal restoration steps, except ensuring the corresponding certificates are created and configured on the destination server.

--Check for the backup

SELECT

b.database_name,

key_algorithm,

encryptor_thumbprint,

encryptor_type,

b.media_set_id,

is_encrypted,

type,

is_compressed,

bf.physical_device_name

FROM msdb.dbo.backupset b

INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id

INNER JOIN msdb.dbo.backupmediafamily bf on bf.media_set_id=b.media_set_id

WHERE database_name = 'SQLShack'

ORDER BY b.backup_start_date DESC

We have four new files that have just been created. We have the backup certificate, the encryption key file, as well as the full backup and the tail log backup of our database. Let’s go back into SQL Server Management Studio and see how we can restore this backup.

-- clean up the instance

DROP DATABASE SQLShack;

DROP CERTIFICATE SQLShackDBCert;

DROP MASTER KEY;

After running the commands above, if I right-click on the Security folder and say Refresh, you’ll notice that it is no longer inside of our Certificates folder. And, if I go back up and refresh databases, you’ll notice that SQLShack is no longer available either. Now, let’s attempt to restore the database from the backup. I’ll specify that I want to restore the database, SQLShack, from the disk. When I execute the command, I get an error message, saying that it couldn’t find the server certificate required in order to restore the backed-up database.

Without configuring the certificate, any attempt to restore would result in the following error:

--Use RESTORE FILELISTONLY to get the logical names of the data files in the backup. This is especially useful when you’re working with an unfamiliar backup file.

RESTORE FILELISTONLY FROM DISK='g:\Program Files\SQLShack.bak'

Recreate the master key and the certificates

In order to restore this encrypted database, we first need to restore the certificate. But this time, instead of creating it based off of the master key for the database, we’re going to restore it from the file. I’ll specify FROM FILE and the path to that file that we exported. We’ll also specify the private key file. Finally, we’ll enter DECRYPTION BY PASSWORD and we’ll re-specify the password that we established earlier when we created that key. After execution, if I go back into System Databases -> master, -> Security, and back to Certificates, we should see the SQLShackDBCert certificate back there.

-- recreate master key and certificate

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@Api1401@2015!!';

-- restore the certificate

CREATE CERTIFICATE SQLShackDBCert

FROM FILE = 'f:\Program Files\SQLShackDBCert.cert'

WITH PRIVATE KEY (FILE = 'f:\Program Files\SQLShackDBCert.key',

DECRYPTION BY PASSWORD = 'Api1401@2015!!');

Database Restoration

Now, go ahead and try the restore again, the RESTORE DATABASE SQLShack from the disk file. Execute the command, and voila, it processed successfully this time! We see that the database, SQLShack, came online

--Use RESTORE WITH MOVE to move and/or rename database files to a new path.

RESTORE DATABASE SQLShack FROM DISK = 'g:\Program Files\SQLShack.bak'

WITH NORECOVERY,

MOVE 'SQLShack' TO 'f:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLShack_Data.mdf',

MOVE 'SQLShack_Log' TO 'g:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLShack_Log.ldf',

REPLACE, STATS = 10;

-- attempt the restore log again

RESTORE LOG SQLShack

FROM DISK = 'g:\Program Files\SQLShackTailLogDB.log';

--Data validation

SELECT * FROM SQLShackTable;

-- clean up the instance

DROP DATABASE SQLShack;

DROP CERTIFICATE SQLShackDBCert;

DROP MASTER KEY;

Database backup Using Asymmetric Key

In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature that gives SQL Server the ability to store the encryption key used for TDE on hardware specifically designed for key security and management. Such devices are called High Security Modules (HSM), whose vendors are referred to as EKM providers. A good key management vendor should supply you with software libraries that easily add and implement it in SQL Server encryption.

SQL Server stores encryption keys separately from the database server on a secure key manager, in order to meet various compliance requirements. Encryption is supported for backups done by SQL Server Managed Backup, which provides additional security for off-site backups. For example, a database backup file placed on the cloud.

In asymmetric encryption, two different keys are used: A “public key” for encrypting and a “private key” for decrypting. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI) or Public-key Cryptography.

Summary

In an environment that relies on SQL-managed native backup methodology, this would be a great feature to secure the data. Given that the backup files are encrypted, we can be confident that they’ll be unusable even if they fall into the wrong hands, while still taking advantage of an off-site storage strategy.
While the process of recovery is no different from restoring the normal database backup file, there’s no “backdoor” to recover the database access if you lose all access to the keys. So keep the keys safe.
The restore operation validates the thumbprint of the certificate during the restore operation. Therefore, the certificate used to create the backup must be retained in its original state.
If restoring a database from an encrypted backup file is performed on the same SQL Server instance, the restore operation is performed as usual, since the keys and the certificate are already contained in the master database. They’d be opened automatically during the process of decryption
You won’t be able to append the backup files to the existing media set in case of encrypted backups; this is not the case with normal backup methodology.
Even after several attempts, I didn’t notice a significant difference in the usage of system resources during the process of encryption of database backup, when compared to the normal database backup operation. However, AES is a block cipher, and requires the input to be a multiple of the block size (16 bytes, a.k.a. 128 bits). This means that padding schemes are used. Most of the time, the padding is negligible, which may be a reason why it doesn’t increase the size of the backup.
Use a combination of hardware- and software-supporting AES with a 256-bit key to ensure that the encryption doesn’t impact your backup or recovery time objectives.

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021