SQL Server 2014 Backup Encryption

Problem

Information security is one of the top priorities of a DBA and that includes backup security. In previous versions of SQL Server in order to encrypt a backup you needed third party tools. Many of these tools offered some interesting features. In this tip I will show you how to issue encrypted backups, a new feature in SQL Server 2014.

Solution

SQL Server 2014 has many new and exciting features and amongst them is the capability to perform encryption of data at backup time. In other words, when data goes outside the scope of the Database Engine it is encrypted.

SQL Server 2014 Backup Encryption Considerations

This feature is supported on the Enterprise, Business Intelligence and Standard versions of SQL Server 2014, but an encrypted backup can be restored on the Web and Express editions of SQL Server.

To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key). So if you don’t have a Database Master Key defined you need to create one.

Asymmetric keys used to encrypt backups must be from a cryptographic provider in Extensible Key Management.

Something to keep in mind is that encrypted backups cannot be appended to an existing backup set.

An encrypted backup can be restored on another server as long as the server has the appropriate certificate.

You should perform a backup of the encryption certificate and keys then keep them safe because an encrypted backup cannot be restored without the certificate used for encryption.

In the next section of this tip, I will show you how to create an encrypted backup with the SQL Server Management Studio Backup Database Wizard and Transact-SQL code.

Setting up a sample SQL Server database and encryption configurations

First we need to create a new database to perform a backup of it. You can omit this step if you have a database you want to use.

CREATE DATABASE SampleDB
ON PRIMARY
  (NAME = SampleDB_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.mdf',
          SIZE = 100MB,          
          FILEGROWTH = 10%),
FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
  ( NAME = SampleDB_MemoryOptimized,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_MemoryOptimized')
LOG ON
  ( NAME = SampleDB_log_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.ldf',
          SIZE = 100MB,          
          FILEGROWTH = 10%)
GO

As I previously mentioned, in order to create an encrypted backup we must create a Database Master Key.

USE SampleDB
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MSSQLTips.com';
GO

We must create a certificate to act as an encryptor.

Use SampleDB
GO
CREATE CERTIFICATE SampleDB_Backup_Certificate
   WITH SUBJECT = 'SQL Server 2014';
GO

Now we are ready to create encrypted backups.

Creation of Encrypted Backup with the SSMS Back Up Database Wizard

To access the Back Up Database Wizard in SSMS, navigate to SQL Server Management Studio | expand the root node | expand the “Databases” folder | right click on the database you want to backup | select the “Tasks” option and select the “Back Up…” option. On the first page of the wizard we choose the backup type and destination as shown below.

Since encrypted backups cannot be appended to an existing backup set on the “Media Options” page of the wizard we must choose “Back up to a new media set, and erase all existing backup sets”. New media set name and description are optional.

The last step of the wizard enables us to encrypt the backups by checking the “Encrypt Backup” option. After we check this option we must select an encryption algorithm and a certificate or asymmetric key as shown below.

Creation of Encrypted SQL Server Backups with Transact-SQL

To create an encrypted backup you must add the ENCRYPTION option to the BACKUP statement.

Here is the statement to backup the SampleDB database with encryption using a certificate.

USE master
GO
BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH FORMAT,
 INIT,
 SKIP,
 ENCRYPTION (
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
  SERVER CERTIFICATE = [SampleDB_Backup_Certificate]
  )
GO

To use an asymmetric key instead of a certificate the statement is as follows.

USE master
GO
BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH FORMAT,
 INIT,
 SKIP,
 ENCRYPTION (
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
  SERVER ASYMMETRIC KEY = [Key_Name]
  )
GO

Restoring an Encrypted SQL Server Backup

The restore process is as usual. You don’t have to specify any extra options, but the certificate or key used to encrypt the backup must be available on the instance.

USE master
RESTORE DATABASE [SampleDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH REPLACE
GO

Next Steps

To get a deeper explanation about the Backup Wizard you can read: How to create a simple database backup using SQL Server Management Studio (SSMS).
In this tip Greg Robidoux explains the importance of backup encryption: Encrypt and safeguard your SQL Server database backups.
Go to Backup Tips Category to learn more about backups.
Get more information about Certificates on: SQL Server 2005 Encryption Certificates Overview.
If you don’t know what a symmetric key is then this tip is for you: SQL Server Encryption Symmetric vs. Asymmetric Keys.

Resources

Community

Subscribe