Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

SQL Server 2014 Backup Encryption


By:   |   Read Comments (6)   |   Related Tips: More > Backup

Problem

Information security is one of the top priorities of a DBA and that includes backup security. In previous versions of SQL Server in order to encrypt a backup you needed third party tools. Many of these tools offered some interesting features. In this tip I will show you how to issue encrypted backups, a new feature in SQL Server 2014.

Solution

SQL Server 2014 has many new and exciting features and amongst them is the capability to perform encryption of data at backup time. In other words, when data goes outside the scope of the Database Engine it is encrypted.

SQL Server 2014 Backup Encryption Considerations

This feature is supported on the Enterprise, Business Intelligence and Standard versions of SQL Server 2014, but an encrypted backup can be restored on the Web and Express editions of SQL Server.

To encrypt a backup we need to configure an encryption algorithm (supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES) and an encryptor (a certificate or asymmetric key). So if you don't have a Database Master Key defined you need to create one.

Asymmetric keys used to encrypt backups must be from a cryptographic provider in Extensible Key Management.

Something to keep in mind is that encrypted backups cannot be appended to an existing backup set.

An encrypted backup can be restored on another server as long as the server has the appropriate certificate.

You should perform a backup of the encryption certificate and keys then keep them safe because an encrypted backup cannot be restored without the certificate used for encryption.

In the next section of this tip, I will show you how to create an encrypted backup with the SQL Server Management Studio Backup Database Wizard and Transact-SQL code.

Setting up a sample SQL Server database and encryption configurations

First we need to create a new database to perform a backup of it. You can omit this step if you have a database you want to use.

CREATE DATABASE SampleDB
ON PRIMARY
  (NAME = SampleDB_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.mdf',
          SIZE = 100MB,          
          FILEGROWTH = 10%),

FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
  ( NAME = SampleDB_MemoryOptimized,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_MemoryOptimized')

LOG ON
  ( NAME = SampleDB_log_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SampleDB_1.ldf',
          SIZE = 100MB,          
          FILEGROWTH = 10%)
GO

As I previously mentioned, in order to create an encrypted backup we must create a Database Master Key.

USE SampleDB
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MSSQLTips.com';

GO

We must create a certificate to act as an encryptor.

Use SampleDB
GO

CREATE CERTIFICATE SampleDB_Backup_Certificate
   WITH SUBJECT = 'SQL Server 2014';
GO

Now we are ready to create encrypted backups.

Creation of Encrypted Backup with the SSMS Back Up Database Wizard

To access the Back Up Database Wizard in SSMS, navigate to SQL Server Management Studio | expand the root node | expand the "Databases" folder | right click on the database you want to backup | select the "Tasks" option and select the "Back Up..." option.  On the first page of the wizard we choose the backup type and destination as shown below.

Step 1 - Backup General Page

Since encrypted backups cannot be appended to an existing backup set on the "Media Options" page of the wizard we must choose "Back up to a new media set, and erase all existing backup sets". New media set name and description are optional.

Step 2 - Backup Media Options

The last step of the wizard enables us to encrypt the backups by checking the "Encrypt Backup" option. After we check this option we must select an encryption algorithm and a certificate or asymmetric key as shown below.

Step 3 - Backup Options

Creation of Encrypted SQL Server Backups with Transact-SQL

To create an encrypted backup you must add the ENCRYPTION option to the BACKUP statement.

Here is the statement to backup the SampleDB database with encryption using a certificate.

USE master
GO

BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH FORMAT,
 INIT,
 SKIP,
 ENCRYPTION (
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
  SERVER CERTIFICATE = [SampleDB_Backup_Certificate]
  )
GO

To use an asymmetric key instead of a certificate the statement is as follows.

USE master
GO

BACKUP DATABASE [SampleDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH FORMAT,
 INIT,
 SKIP,
 ENCRYPTION (
  ALGORITHM = AES_256, --  { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
  SERVER ASYMMETRIC KEY = [Key_Name]
  )
GO

Restoring an Encrypted SQL Server Backup

The restore process is as usual. You don't have to specify any extra options, but the certificate or key used to encrypt the backup must be available on the instance.

USE master

RESTORE DATABASE [SampleDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SampleDB.bak'
WITH REPLACE

GO
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 11, 2014 - 7:26:24 AM - Perry Whittle Back To Top

Since the cert will be stored in the master database you also create the database master key in the master database not the user database. The DMK is used to protect the private key for the cert you will use to encrypt your database backups. See more at my article here on SSC

http://www.sqlservercentral.com/articles/Encryption/109028/

Regards Perry


Friday, September 19, 2014 - 6:16:19 AM - Georg Back To Top

Katerina is right, the certificate must be created in master database.

Otherweise the backup command can not be find the certificate...

USE master

GO

BACKUP DATABASE...

Best regards,

Georg Pongracz


Sunday, April 20, 2014 - 11:00:45 PM - Daniel Farina Back To Top

Hi Katerina,

You need to create a database master key on the database you want to encrypt its backups. Are you performing your backup to a new media set?

Best Regards

 


Friday, April 18, 2014 - 5:53:48 AM - katerina Back To Top

Thanks for useful information but it didn't work..  I can't see a certificate in backup page. I think master key should be created only master database... 


Thursday, February 06, 2014 - 5:57:00 PM - Daniel Farina Back To Top

Hi,

You can create a maintenance plan with a backup task and in Options tab check "Backup Encryption"

 

Thank you for reading!


Monday, February 03, 2014 - 12:38:13 PM - MRSKINNMAN Back To Top

Will these features be available in maintenance plans or as in previous versions will I be creating T-SQL to get the feature i.e. password protect backups?


Learn more about SQL Server tools