SQL Server 2014: Encryption for Backups

The next version of Microsoft’s main stream
relational database management system (RDBMS), SQL Server 2014, has the ability
to encrypt data in the database while a backup is created. The pleasant
surprise is that this encryption feature for native database backups is
available in Standard, Enterprise, and Business Intelligence editions of SQL
Server 2014. Moreover, encrypted backups performed using these editions can be
restored to the Web and Express editions of SQL Server 2014.

The database master key (DMK) for the
master database and certificate or asymmetric key is a prerequisite for
encrypting a backup. To encrypt backup, you must specify an encryption
algorithm, and an encryptor (a Certificate or Asymmetric Key) to secure the
encryption key. The supported encryption algorithms are: AES 128, AES 192, AES
256, and Triple DES. Furthermore, asymmetric keys used to encrypt the backups
must reside in an Extended Key Management. For more information about Extended
Key Management, see MSDN resource here.

Demo

In the following section, I will describe
the basic steps to create an encrypted backup both via TSQL and SQL Server
Management Studio:



Note: For purpose of demonstration, I will
be encrypting the backup of SQL Server 2012 sample database AventureWorks2012

The first step to encrypt a native database
backup is to create a database master key (DMK) and a server certificate in the
master database. To do this, run the following code in SSMS New Query window:

        USE [master]
GO
-- Create the DMK.
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '$tr0ngPa$$w0rd1'
GO
-- Create the server certificate.
CREATE CERTIFICATE [CertifcateToEncryptBackup]
WITH SUBJECT = 'Certificate to encrypt backup';
GO

For more information about CREATE MASTER
KEY or CREATE CERTIFICATE, see MSDN references here and here
respectively.

You can query sys.symmetric_keys and sys.certificates, to
view information about database master key (DMK) and server certificate. For
example, as follow:

        SELECT * FROM sys.symmetric_keys
WHERE [name] = '##MS_DatabaseMasterKey##';
SELECT * FROM sys.certificates
WHERE [name] = 'CertifcateToEncryptBackup'

Using Transact-SQL to Create Encrypted
Database Backups

The following is the Transact-SQL script
that I use to back up a whole AdventureWork2012 database with encrypting using
a certificate. As you can see, the below BACKUP DATABASE command
includes encryption option, where I specified an encryption algorithm, and the
server certificate we created earlier.

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'\\ServerLocation\Backup\AdventureWorks2012_full_backup.bak'
WITH COMPRESSION
       ,ENCRYPTION (ALGORITHM = AES_256
,SERVER CERTIFICATE = CertifcateToEncryptBackup)
,STATS = 10
GO

The following is the syntax to encrypt the backup with asymmetric key:

BACKUP DATABASE [AdventureWorks2012]
TO DISK = N'\\ServerLocation\Backup\AdventureWorks2012_full_backup.bak'
WITH COMPRESSION
       ,ENCRYPTION (ALGORITHM = AES_256
,SERVER ASYMMETRIC KEY  = <key_name>)
,STATS = 10
GO

Using SQL Server Management Studio Backup
Database Wizard to Create Encrypted Database Backups

To start the backup you can either:

  • Right-click
    the database and choose Tasks, Back Up.
  • Right-click
    Backup Devices and choose Back Up a Database.

The Backup dialog box has three pages. On
the General page, specify:

  • Database
    to backup
  • Backup
    type
  • Database
    or file/filegroup backup
  • Name and
    description
  • Backup
    expiration date (after which the backup is no longer valid)
  • Permanent
    or temporary backup device destination

On the Media Options page, choose option
“Back up to new media set, and erase all existing sets option”. Next, specify
New
media set name
and New media set description.

On the Backup Options page, check “Encrypt
Backup” option. After checking this option, you must select an encryption
algorithm and a certificate or asymmetric key to encrypt the backup (see
below):

Click OK, to perform the backup as shown
below:

Recommended Practices

It’s important that you immediately back up
the certificate and the database master key (DMK) associated with the
certificate. If the certificate becomes unavailable, or if you want to restore
or attach the database on another server, you must have the backups of both the
certificate and the DMK. Otherwise, you won’t be able to open the database. Run
the following code, to backup database master key (DMK) and server certificate
we created earlier:

        -- Backing up Database Master Key (DMK)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
BACKUP MASTER KEY TO FILE = N'C:\Backup\DMK.key'
ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
GO
-- Backing up Certificate
BACKUP CERTIFICATE CertifcateToEncryptBackup
TO FILE = N'C:\Backup\MyServerTDECert.cer'
WITH PRIVATE KEY (FILE = N'C:\Backup\CertifcateToEncryptBackup.key'
                 ,ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1')
GO

It is also advisable to backup database
master key (DMK) and server certificate to a location other than machine on
which SQL Server instance is installed.

Finally, to increase security, choose
different certificates or asymmetric keys for encrypting the databases
encrypted with TDE.

Restoring an encrypted database backup

The command to restore the encrypted backup
is same as the one you would use when performing the unencrypted database
restore. For more information, see
RESTORE
(Transact-SQL)
on MSDN books online.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |