SQL Server Backup Encryption, SQL 2014 CTP2

Dear Friends,

SQL Server 2014 CTP2 introduced SQL Server backup encryption using which you can encrypt database backups. A certificate or asymmetric key is used with encryption algorithm to achieve this.  Previously you would have used any third party tools to encrypt your native backup files but this feature is now part of SQL Server 2014 CTP2. You have variety of encryption algorithms to choose from, to name them AES 128, AES 192, AES 256 and Triple DES and can control the process using T-SQL, PowerShell, SSMS or SMO.

This feature is available in Standard, Enterprise or BI edition of SQL Server 2014. Pre-requisites for this feature are to have either a certificate or asymmetric key and to choose from a range of encryption algorithm. Let’s us try this out step by step using T-SQL statements for this demo.

To start with at first we will create a master key;

1_SQL_Server_Backup_Encryption_SQL_2014_CTP2

We will now create an encryption certificate;

2_SQL_Server_Backup_Encryption_SQL_2014_CTP2

   

We are set to create our encrypted backup file by specifying the certificate and encryption algorithm. Syntax is pretty straight forward as shown below;

4_SQL_Server_Backup_Encryption_SQL_2014_CTP2

You might have observed the warning message, which says certificate used for encryption is not backed up as such it is very much recommended that the certificate or key used for encryption must be backed up to a different location than the backup files.  Without this certificate or key the backup file you cannot restore databases.

This feature is amongst one of the long waited ones which will eliminate dependency on third party tools thus help organizations in saving costs and great to have inbuilt with 2014 CTP2.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.