SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

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 128AES 192AES 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.

Starting SQL Server 2014 there is no need to invest in third party tools to implement Database Backup Encryption as long as you are using Standard, Enterprise, Developer and Business Intelligence Editions of SQL Server 2014.

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.

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.

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.

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips