Archive for April 8th, 2014

SQL Server 2014: Encrypted Backups April 8th, 2014

Vinod Kumar

The learning on SQL Server 2014 continues and in this blog, I will walk through TSQL on what are the steps involved in creating an Encrypted backup using SQL Server 2014. The steps involved are:

  1. Create or identify your database (with data)
  2. Setup a Master Key
  3. Create a Certificate which we will use
  4. Backup your Certificate as a best practice
  5. Take the backup using the Certificate

Step 1: Create or identify your database (with data)

In my example, I am just creating a dummy database for testing.

CREATE DATABASE [MySecureDB];

If you want, go ahead and create a table with some values for later reference when we use restore scripts.

USE MySecureDB

GO

CREATE TABLE client_data(name NVARCHAR(30), CreditCard CHAR(20))

GO

INSERT INTO client_data VALUES(‘Pinal’, ‘123-01-1234’)

          ,(‘Balmukund’, ‘123-01-1235’)

          ,(‘Vinod’, ‘123-01-1236’);

GO

SELECT * from client_data

GO

Step 2: Setup a Master Key

The next logical step is to create a Master Key.

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyComplexPassword-2014’

GO

Step 3: Create a Certificate which we will use

Next step is to create a certificate which we will use for encryption requirements.

USE [master];

GO

CREATE CERTIFICATE BackupCert

   WITH SUBJECT = ‘My Backup Encryption’;

GO

Step 4: Backup your Certificate as a best practice

This is an important and critical step, always DONOT forget to backup your certificates.

Use [master]

BACKUP CERTIFICATE BackupCert TO FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’

    WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,

    ENCRYPTION BY PASSWORD = ‘MyCertPassword-2014’);

GO

Step 5: Take the backup using the Certificate

The next step is the easiest step of backing up your database using an certificate.

BACKUP DATABASE [MySecureDB]

TO DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’

WITH

  NOFORMAT, NOINIT,

  NAME = N’mySecureDB – uncompressed, encrypted’,

  SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION,

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  STATS = 10

GO

As a bonus step, you can also compress an encrypted backup. From the above statement, just change from NO_COMPRESSION to COMPRESSION to check its effect on your backups. To look at a UI option to enable the same from my friend Balmukund’s post on the same topic – SQL 2014 Learning Series # 3 – New Feature – Backup Encryption.

In future posts, I will walk through some more scenarios of Restore, Failures and troubleshooting while restoring encrypted backups.

Continue reading...