Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide

Microsoft has introduced inbuilt Database Backup Encryption Feature in SQL Server 2014. Starting SQL Server 2014, SQL Server will have the ability to encrypt the data while the backup is in progress. One can encrypt the database backup by specifying Encryption Algorithm and an Encryptor (Certificate or Asymmetric Key) while creating database backups.

This article is a step by step guide to implement Database Backup Encryption Feature in SQL Server 2014.

Database Backup Encryption feature is available in Standard, Enterprise, Developer and Business Intelligence Editions of SQL Server 2014. However, an encrypted database backup can be restored on all the editions of SQL Server 2014.

SQL Server 2014 supports the following encryption algorithms: AES 128, AES 192, AES 256, and Triple DES.

Why Database Encryption is Important?

Key Benefits of Database Backup Encryption are:-

• Implementing Database Backup Encryption feature of SQL Server 2014 can help you secure the data.
• Using Backup Encryption one can encrypt the data while creating a backup.
• Database encryption can be used for database which are encrypted using Transparent Data Encryption (TDE)
• Database encryption feature supports to encrypt backups performed by SQL Server Managed Backup to Windows Azure.
• Database encryption feature currently supports multiple encryption algorithms such as AES 128, AES 192, AES 256, and Triple DES.
• Moreover, you can also integrate encryption keys with Extended Key Management (EKM) providers.

Permissions Required to Backup and Restore Encrypted Database Backups

One must have VIEW DEFINATION permissions on the certificate or asymmetric key that is used to encrypt the database backup.

Demo: How to Encrypt Backups using Backup Encryption feature of SQL Server 2014

Let us now go thought an example to use database encryption feature of SQL Server 2014.

How to Create Database Master Key for Master Database

Step 1: Initial step will be to create a Database Master Key (DMK) for the master database. If you are already using Transparent Data Encryption then Database Master Key (DMK) is already created while TDE feature of SQL Server is enabled. Execute the below script to create Database Master Key for master database.

/* Below script creates a Database Master Key and the key is Encrypted using the Password */

USE Master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd';
GO

How to Create a Backup Certificate in SQL Server

Step 2: Create a backup certificate in the master database. Execute the below query to create a backup certificate.

Use Master
GO

CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'SQL Server 2014 Backup Encryption Certificate';
GO

If you wish to use use ASYMMETRIC Key the read the following article How to CREATE ASYMMETRIC KEY in SQL Server.

How to Backup Certificate Using TSQL Code in SQL Server

Once you have created the Backup certificate using the above script you must backup the certificate. If you don’t backup the certificate you will receive the below mentioned warning message.

Execute the below mentioned TSQL to Backup Certificate in SQL Server.

BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = 'X:\BackupCertificates\BackupEncryptionCert.bak'
WITH PRIVATE KEY ( FILE = 'X:\BackupCertificates\BackupEncryptionCertKey.bak' ,
ENCRYPTION BY PASSWORD = 'Certi%yC&mpl£xP@$$Wrd');

Click the below Number Buttons to continue reading the rest of the article.