A brand new & long awaited feature “Backup Encryption” along with SQL Server 2014. Microsoft reveled this upcoming feature in PASS summit & from then users are waiting for this. Currently backup encryption supporting four encryption algorithms: AES 128, AES 192, AES 256, and Triple DES (3DES).

Currently, User are using TDE or 3rd party software only to encrypt there database backups. This feature is going to reduce your complexity.

Points to Remember :-

1) TDE(Transparent Data Encryption) is different from backup encryption

2) Native backup encryption available only in Standard, Business Intelligence and Enterprise Editions. Web and Express edition does not supported this feature

3) VIEW DEFINITION permission required on the certificate or asymmetric key that used to encrypt the database backup

4) SQL Server Express and SQL Server Web do not support backup encryption. But restoring encrypted backup to an instance of SQL Server Express or SQL Server Web is supported

5) Backward compatibility, Previous versions of SQL Server cannot read encrypted backups

6) Server on which Encrypted backup going to restore, Master key & Encryption certificate need to exists

7) Appending new backup file to an existing backup set is not supported for encrypted backups. If you try, you will receive below error.

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.

Msg 3095, Level 16, State 1, Line 1

The backup cannot be performed because ‘ENCRYPTION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘ENCRYPTION’ or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Benefits of Backup Encryption :-

1. Database Backup Encryption secure the data. SQL Server provides the option to encrypt the backup data while creating a backup.

If we try to open backup file in notepad, we can see actual data is visible & that can lead to data theft threat.

But data in Encrypted backup files is not readable.

2. Database Backup Encryption can also be used for databases that are encrypted using TDE. But I feel, if you enabled TDE, you are good enough
3. Database Backup Encryption supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups
4. Database backup encryption supporting four encryption algorithms: AES 128, AES 192, AES 256, and Triple DES (3DES). This gives you the option to select an algorithm as per your requirements.

How to check which backup set is encrypted ? :-

1) You need to look into BACKUPSET in MSDB about encryption

2) No change in RESTORE HEADERONLY, so you will not get any information about encryption in HEADERONLY

Steps to take encrypted backup :-

1) Create a Database Master Key for the master database on the instance

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pass@word1′

2) Create an encryption certificate

Use Master

GO

CREATE CERTIFICATE DBBackupEncryptCert

WITH SUBJECT = ‘Backup Encryption Certificate’;

3) Backup Master key & encryption certificate

Use Master

GO

BACKUP CERTIFICATE DBBackupEncryptCert

TO FILE = ‘C:BackupDBBackupEncryptCert.cert’

WITH PRIVATE KEY

(

FILE = ‘C:BackupDBBackupEncryptCert.cert’,

ENCRYPTION BY PASSWORD = ‘Pass@word1′

);

GO

BACKUP MASTER KEY TO FILE = ‘C:BackupMasterKey.key’

ENCRYPTION BY PASSWORD = ‘Pass@word1′;

4) Backup database with encryption option & required encryption algorithm

a. By Script

BACKUP DATABASE UserDB1

TO DISK = ‘C:BackupUserDB1_Encrypt.bak’

WITH

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = DBBackupEncryptCert

),

STATS = 10 , INIT

b. By GUI

Steps to restore encrypted backup :-

1. Scenario 1 – Server on which Encrypted backup going to restore has Master key & Encryption certificate. No Change is restore steps either from script or GUI required.

RESTORE DATABASE [UserDB1] FROM DISK = N’C:BackupUserDB1_Encrypt.bak’

2. Scenario 2 – Server on which Encrypted backup going to restore, Master key & Encryption certificate does not exists

It’s not possible, You will get below error.

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint ’0x49FB256B3F7F586205178E1D26C28E724F432F59′.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)