Consider the following situation, you create a database backup on a AWS virtual machine EBS disk and then move the backup file to Blob storage e.g. S3. However, you have switch on the Encryption option for your S3 storage. Is your database backup file fully protected? Yes, well, sort of.

There is that chance, a very small one in deed, that a Cloud provider employee could gain access to the S3 storage and copy the backup file elsewhere. If they have the encryption key for the S3 storage then no problems, they can restore backup file.

Fortunately, the about to be released SQL Server 2014 (April 1) comes to the rescue!

The new backup encryption option is exactly the remedy we need. You can encrypt your backup first using the Backup command extension and then copy it to the S3 storage which is also encrypted. Problem solved. By the way, Backup Encryption is available in Standard Edition. Phew!

Of course if you have the luxury of SQL Server Enterprise Edition (from 2008 and above) you could implement Transparent Data Encryption to protect your backups.  In fact not only does it secure your backup file (you need a copy of the certificate and associated password), it also secures the database files at rest.

Back to the SQL Server 2014 backup encryption option, below is the typical example you see around the traps.

You need to first create the symmetric key to encrypt the backup certificate.

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@ssw0rd’;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘p@ssw0rd’;
GO
CREATE CERTIFICATE BackupEncryptCert WITH SUBJECT = ‘EncryptionTesting’;
GO

Next Backup the certificate before using it

BACKUP CERTIFICATE BackupEncryptCert
TO FILE = ‘C:\temp\BackupEncryptCert.cert’
WITH PRIVATE KEY
(    FILE = ‘C:\temp\BackupEncryptCert.key’,
ENCRYPTION BY PASSWORD = ‘p@ssw0rd’
);

Run the backup command with the Encryption clause

BACKUP DATABASE [AdventureWorks2012] TO DISK = N’C:\SQLBackup\AW2012.bak’
WITH COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupEncryptCert
), STATS = 10

If you try to restore on another server without the certificate you get.

Msg 33111, Level 16, State 3, Line 3
Cannot find server certificate with thumbprint ‘0x2A0A9DD92185C316D7C97FEF08D4B8AE913F7256’.
Msg 3013, Level 16, State 1, Line 3

RESTORE DATABASE is terminating abnormally.