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.
In the following section, I will describe
the basic steps to create an encrypted backup both via TSQL and SQL Server
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
SELECT * FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##'; SELECT * FROM sys.certificates WHERE [name] = 'CertifcateToEncryptBackup'
Using Transact-SQL to Create Encrypted
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:
the database and choose Tasks, Back Up.
Backup Devices and choose Back Up a Database.
The Backup dialog box has three pages. On
the General page, specify:
or file/filegroup backup
- Name and
expiration date (after which the backup is no longer valid)
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
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
Click OK, to perform the backup as shown
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
on MSDN books online.