SQL Server Database Backup Encryption

To manage the data security that has been backed up by the file system as a database backup files, SQL Server provides the backup encryption feature. In this article, we will talk about encryption options available in SQL Server for database backups. We will have a closer look at the usage details, benefits, and recommended practices for encrypting SQL Server database backup during the backup process.

What is database backup encryption?

The need to keep data secure grows dramatically. Along with data, you need to be sure your database backup files are also secured, especially those on a server file system. The data inside the native SQL Server backup files is stored as a plain text on the file system. You can read it with no effort using a text editor.
Depending on the data types utilized in your tables, some data is much easier to read than other. The following picture demonstrates the backup file opened in the Notepad text editor:

As you can see, the T-SQL code is visible and easy to read. However, once we create the backup with an encryption, no one will have a chance to get under the hood.

The following picture demonstrates the same AdventureWorks2014.bak with an encryption.

Beginning with SQL Server 2014, the database engine can encrypt the data while creating a backup file. You can specify the encryption algorithm and the encryptor, either a Certificate or Asymmetric Key, while creating a backup. The backup encryption feature improves security and works in any domain where SQL Server itself can be utilized.

What is required?

To encrypt the database backup, you need to specify an encryption algorithm and an encryptor. There are two supported encryption options:

• Encryption algorithm: AES_128, AES_192, AES_256, and Triple_DES_3Key
• Encryptor: A certificate or asymmetric key

If you accidentally lose the certificate or asymmetric key, you will never have a chance to restore the backup file. Thus, is is very important to keep the certificate or asymmetric key in a safe location.

Database backup encryption benefits

• Helps secure data.
• Can be applied to the databases that are encrypted with the help of Transparent Data Encryption (TDE).
• Supported for backups created by SQL Server managed backup to Microsoft Azure that provides additional security for off-site backups.
• Supports numerous encryption algorithms up to AES 256 bit. This allows you to select an algorithm that meets your requirements.
• Possible to integrate encryption keys with Extended Key Management (EKM) providers.

Changes in system tables

While creating the encrypted backup, some information is recorded into the MSDB system database: the key algorithm, encryption type, and encryption thumbprint used.
The backupset table contains an information for each backup set. A backup set contains the backup for a single, successful backup operation.
The following columns: key_algorithm, encryptor_thumprint, encryptor_type of this DMV store information on whether the backup is encrypted, the encryptor type, and the encryptor thumbprint.

SELECT TOP 5
    name,
    key_algorithm,
    encryptor_thumbprint,
    encryptor_type
FROM msdb.dbo.backupset AS backupset with (NOLOCK)
WHERE type IN ('D', 'I') AND database_name = 'AdventureWorks2014'
ORDER BY backupset.backup_start_date  DESC
GO

Here is what you will see:

To get more information about the backupset table, read the following MSDN documentation page: backupset (Transact-SQL)

The backupmediaset table contains an information for each backup media set. The is_encrypted column indicates whether the backup is encrypted or not. 0 – not-encrypted and 1 – encrypted. Initially, this value is set to NULL which indicates non-encrypted backupmediaset.

SELECT TOP 5
    media_set_id,
    is_encrypted, 
    is_compressed
FROM msdb.dbo.backupmediaset AS mediaset with (NOLOCK)
ORDER BY mediaset.media_set_id DESC
GO

The resultset:

Database backup encryption methods

1. With help of dbForge Studio for SQL Server

It is possible to create database backup encryption with the help of SSMS, but I personally preferdbForge Studio for SQL Server — a powerful IDE for SQL Server management, administration, development, data reporting, and analysis. This IDE is like a Swiss knife for database developers. The tool provides essential features that are wrapped up in a well-designed and intuitive GUI. To get acquainted with key features, see the dbForge Studio for SQL Server – Overview introductory video (04:03 min):

Once you have downloaded and installed the tool, you need to create the database connection.

Note that the backup encryption feature is introduced in SQL Server 2014. SQL Server Express does not support encryption during backup.

1. Once you have connected to a required SQL Server instance, in Database Explorer, click the server name to expand the server tree.

2. Right-click a required database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

In the Connection list box, check the connection name. You can optionally select a different connection from the list.

3. Click Next, to proceed with Media Options.

The encryption option is disabled if you select to append to existing backup set option on Media Options page of Backup Database wizard. Choose backup to a new media set, and erase all existing backup sets.
Create the certificate/key before starting the process. Certificate or asymmetric key created before initiating the backup database wizard will be listed in the drop-down.

Select the Back up to a new media set, and erase all existing backup sets option to create a new backup. Enter a name in the Media set name text box, and, optionally, describe the media set in the Media set description text box.

4. Click Next, to proceed with Backup Options.

On this page, select the Encrypt Backup option. Select the Algorithm and the Certificate or Asymmetric key. Click Back Up. The process takes several seconds in my case.

2. Using Transact-SQL statements

We will use the BACKUP DATABASE statement to create a database backup and the BACKUP LOG to create a transaction log backup file.

Full database backup

This type of backup backs up the entire database. It includes a part of the transaction log so that the full database can be recovered after a full database backup is restored.

The following code creates a Full database encrypted backup at the provided location utilizing the specified certificate and encryption algorithm.

BACKUP DATABASE AdventureWorks2014
TO DISK = 'D:\DBMSSQLX64\Backup\AdventureWorks2014.bak'
WITH NAME = N'AdventureWorks2014, Compressed, Encrypted, Full',
    FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5, COMPRESSION,
ENCRYPTION
(
    ALGORITHM = AES_256, SERVER CERTIFICATE = Cert1
)
GO

Note, If the certificate used for encryption has never been backed up, the following warning occurs when the backup completes. Make sure you take a backup of certificate along with the private key associated with the certificate:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Differential database backup

While creating this type of the backup, the database or file backup consists of only the portions of the database or file changed since the last full backup. Also, this type of the backup usually takes up less space than a full backup.

The following code creates a differential database encrypted backup at the provided location using the specified certificate and encryption algorithm.

BACKUP DATABASE AdventureWorks2014
TO DISK = 'D:\DBMSSQLX64\Backup\AdventureWorks2014_DIFF.bak'
WITH NAME = N'AdventureWorks2014, Compressed, Encrypted, Differential',
    FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 5,
 COMPRESSION, DIFFERENTIAL,
ENCRYPTION
(
    ALGORITHM = AES_256, SERVER CERTIFICATE = Cert1
)
GO

Database transaction log backup

SQL Server database contains one or more transaction log files, in addition to data files, that records all the transactions and database modifications made by each transaction. The transaction information gets collected only in the databases where database recovery mode is set to full recovery or bulk-logged recovery.

The following code creates database transaction log encrypted backup at the provided location using the specified certificate and encryption algorithm.

BACKUP LOG AdventureWorks2014
TO DISK = 'D:\DBMSSQLX64\Backup\AdventureWorks2014_LOG.bak'
WITH NAME = N'AdventureWorks2014, Compressed, Encrypted, TLog',
    FORMAT, INIT, SKIP, STATS = 5, COMPRESSION, ENCRYPTION
(
    ALGORITHM = AES_256, SERVER CERTIFICATE = Cert1
)
GO

3. Using PowerShell

SQL Server 2014 allows you to back up the database using Windows Powershell. The following code creates the encryption options and uses it as a parameter value in the Backup-SqlDatabase commandlet:

$encryptionOption=New-SqlBackupEncryptionOption –Algorithm Aes256 –EncryptorType ServerCertificate –EncryptorName “Cert1”
Backup-SqlDatabase –ServerInstance Server_name –Database “AdventureWorks2014” –BackupFile “D:\DBMSSQLX64\Backup\AdventureWorks2014_DIFF.bak” –CompressionOption On –EncryptionOption $encryptionOption

Comparing above mentioned approaches, there are no doubts that creating encrypted database backups is quite easy task when you have the right tool, like dbForge Studio for SQL Server from Devart.

Additional reading

My friend, Pinal Dave – SQL Server technology enthusiast, independent consultant, author of various SQL Server books, and Pluralsight courses, wrote an excellent article, that I would suggest you to further reading: SQL SERVER – A Practical Use of Backup Encryption