MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter






























Trending SQL Server Tips







Backup Encryption in SQL Server 2014

Read Comments


Introduction

Microsoft has introduced Database Backup Encryption feature in SQL Server 2014 (CTP2). 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.

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

First 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

Next step will be to 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');

Backup Database Using Encryption Feature of SQL Server 2014

Now that you have completed the prerequisites such as Database Master Key and Backup Certificate you can go ahead and create the encrypted database backups in SQL Server 2014.

/* Execute the below TSQL code to create encrypted database backup in SQL Server 2014 */

BACKUP DATABASE [HumanResource]
TO DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupEncryptionCert
),
STATS = 5
GO

Once the above TSQL script is executed successfully the backup file will be enrypted. Learn more about Database Compression Feature.

How to Restore an Encrypted Backup on the Same Instance

Execute the below script to restore encrypted backup of the database on the same instance of SQL Server 2014.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

How to restore Encrypted Backup on Different Instance of SQL Server were Certificate is not present

You will receive the below mentioned error message received while restoring encrypted database on the destination server when certificate is not found.

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ''.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In such scenarios you need to Restore Master Key and Certificate on Destination Server. Read the steps mentioned below to restore master key and certificate on another server.

Highly Recommended:- Database backups are very important for any organization and at the same time Database Monitoring is crucial for any business to avoid Performance Bottlenecks and Downtime. Hence MyTechMantra.com recommends the use "Database Performance Analyser." as a Database Monitoring & Performance Tuning Tool for SQL Server, Oracle or MySQL Database environments.

How to Restore Master Key and Certificate on Destination Server

Execute the below script to create master key on the destination server.

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

Execute the below script to create backup certificate on the destination server.

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

Once the Master Key and Certificate is created on Destination Server execute the restore database script.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

Conclusion

As a DBA you must importantly back up the certificate or asymmetric key, preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you will not be able to restore the backup, resulting in a non-usable database backup file. Hence before implementing this feature on a Production database test it thoroughly and document all the TSQL scripts in a Source Control.







Last Updated On: Sept 12, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: