Thursday, February 26, 2015

Backup Encryption with SQL Server 2014

You’ve been able to encrypt data in SQL Server for several versions using Transparent Data Encryption and, if you backup encrypted data, the backup is encrypted. The downside of this approach is that Transparent Data Encryption has a performance hit and so there was no straightforward method to have an encrypted backup of non-encrypted data. This meant that if you didn’t want your data encrypted then your backup, possibly held off-site, could be restored to another server and your data would be compromised.

SQL Server 2014 introduces encrypted backups and they’re very easy to implement:

1. First, create a certificate and keep it very safe. The following code demonstrates how to create a certificate (use your own name and password):

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘VeryStr0ngP@ssw0rd’

GO

CREATE CERTIFICATE BackupEncryptionCertificate

WITH SUBJECT = ‘This is a certificate to encrypt backups’

2. Now encrypt your backups. You can use the WITH ENCRYPTION clause like this:

3. SQL Server 2014

4. Or you can specify encryption graphically like this:

5. SQL Server 2014

Note that it must be a new backup set and cannot be added to an existing set.

Now you have an encrypted backup without having the performance hit of encrypted data.

3 comments:

Sam Maron said...

We often lose data simply because we do not have proper workflow procedures and backup strategies. More efficient procedures for saving our work and making backups regularly will definitely save a lot of time for us.
vdr virtual data room

Sathya G said...

This is really a very great blog. the information present in this blow will be very useful for us. thank you for sharing with us.
ROI Services in Chennai

Toby Valentine said...

Security is a major concept of virtual data room and there are programs that are able to deal with any potential threats.
virtual data rooms review