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.
]]>