SQL Server 2014 Backup Encryption

  • Article

Editor’s note: The following post was written by SQL Server MVP Nicolas Souquet

Introduction

With the verly large amounts of data companies store today, databases are very likely to contain critical and confidential data, making database backups encryption an essential database engine feature. It is easily foreseeable that such feature will soon be required to pass accreditation audits.

Raising the security bar through releases, SQL Server 2014 now enables to encrypt database backups natively. It has been a long awaited feature, as the options to cover this need were rather cumbersome :

-          Transparent Data Encryption, released with SQL Server 2008, but it implies some CPU overhead, and the backup file compression ratio is then very low;

-          Disk encryption, such as BitLocker, but ciphering large disk volumes is a long operation;

-          Third-party software.

In this paper, we will expose how backup encryption works, before walking through the steps needed to encrypt databases backups with T-SQL and with Maintenance Plans. We will finally detail the actions required to restore a ciphered database backup. Backing up to a file and to Azure will be addressed.

How does backup encryption work ?

There is no encryption without keys, and backup encryption is no exception : we first need to create a Database Master Key (DMK) of the master system database, and a certificate. The DMK is a symmetric key, and is unique to each database in each SQL Server instance : we cannot restore an encrypted backup file on a distinct or re-installed SQL Server instance without the master system database DMK (or an encrypted database without its DMK).

The Database Master Key is encrypted by the AES 256 algorithm, using the Service Master Key, which is also a symmetric key. It is encrypted based on the SQL Server service account credentials and the Windows Data Protection API machine key. The Service Master Key is unique per SQL Server instance, and created during SQL Server installation. It is stored in the master system database and in the user database, so as to enable its automatic decryption (cf. sys.symmetric_keys system view).

Finally, a Certificate contains a public key that is digitally signed, and may contain a private key. This private key is protected by the DMK. While SQL Server can generate IETF X.509v3-compliant certificates, it also allows to use certificates generated by third-parties (cf. sys.certificates system view).

We can summarize the encryption hierarchy levels with the following diagram :

The backup process works on a data page basis : it copies data pages from the data files into the backup file. Since SQL Server 2008, we can compress database backups. This feature is supported by an algorithm similar to the ones behind file compression software : it factorizes patterns of data found in the data pages. Whether the backup data pages are compressed or not, SQL Server 2014 is able to encrypt these pages with the AES 128, AES 192, AES 256 or Triple DES algorithms.

Encrypting a database backup in T-SQL

In this example, we will use the Contoso demo database. The code needed to take an encrypted database backup is following the steps described in the previous section. First of all, we must backup the service master key :

USE master

GO

 

-- Saving the service master key

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP SERVICE MASTER KEY

TO FILE = 'E:\SQLServerBackup\SQL2014_service_master_key.key'

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

 

We then have to create the master key, which again is straightforward. The password used to encrypt the master key can be different than the one used to encrypt the service master key backup file.

-- Creating a new database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

 

-- Saving the database master key

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP MASTER KEY

TO FILE = 'E:\SQLServerBackup\SQL2014_Contoso_master_key.key'

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

 

We can confirm the creation of the master key by examining the sys.symmetric_keys system view : it now shows one, named ##MS_DatabaseMasterKey##. Next, we need to create the certificate, which is achieved with a single instruction :

 

USE master

GO

 

-- Creating the certificate

CREATE CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

WITH SUBJECT = 'SQL Server 2014 Backup Encryption demo with Contoso';

GO

 

Similarly, interrogating the sys.certificates system view in the master database context will now return a supplementary row, and the column pvt_key_encryption_type_desc indicates ENCRYPTED_BY_MASTER_KEY.

 

As for every key, we want to save the certificate : this requires to specify a private key file. It clearly implies that one does not work without the other. Here again, a password is required to cipher the certificate file, and optionally a different one can be defined for the decryption. This password is the public key of the certificate.

 

-- Saving the certificate

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

TO FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate.cer'

WITH PRIVATE KEY

        (

                FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate_private_key.key'

                , ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

        );

GO

 

At this point, the groundwork is laid for taking encrypted backups. We have 4 files that we need to keep in a secure and distinct storage area :

 

 

Backing up a database and ciphering the resulting file only requires us to choose an encryption algorithm and to specify the certificate we want to use :

-- Backing up the ContosoRetailDW demo database, with encryption

BACKUP DATABASE ContosoRetailDW

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH INIT, CHECKSUM, COMPRESSION, STATS = 1

        , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

Restoring a database from an encrypted backup file

 

Before we walk thorugh the restoration process, we must keep in mind that SQL Server 2014 introduces native backup encryption. Thus, restoring a database from a natively encrypted backup file on a version of SQL Server that is anterior to SQL Server 2014 is not supported.

Restoring the database on the same SQL Server 2014 instance

Restoring a database from an encrypted backup file on the same SQL Server 2014 instance as the one on which its backup has been taken is operated as usual : all the keys and the certificate are already registered in the master database. Consequently, they are opened automatically when needed for decryption.

RESTORE DATABASE ContosoRetailDW_RestoredFromEncryptedBackupFile

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH MOVE 'ContosoRetailDW2.0' TO 'E:\Contoso\ContosoRetailDW_EncrypteBackup_data.mdf'

, MOVE 'ContosoRetailDW2.0_log' TO 'E:\Contoso\ContosoRetailDW_EncrypteBackup_log.ldf'

, STATS = 1

 

Restoring a database from an encrypted backup file on another SQL Server 2014 instance

This operation requires to :

  • Restore the Database Master Key from its backup file
  • Create the Certificate from its backup file, which involves the private key file.

The restoration process is as follows :

USE master

GO

 

-- Restoring the master key on the target SQL Server instance

-- from its backup file

RESTORE MASTER KEY

FROM FILE = 'E:\SQLServerBackup\SQL2014_Contoso_master_key.key'

-- the password that was used to encrypt the master key in the source SQL Server instance

DECRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

-- the password with which we want to encrpyt it on the target SQL Server instance

-- the service key is different on the source and the target SQL Server instances

ENCRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!';

 

-- Since the master key is not registered in the master database

-- We need to open it in order to decrypt it

-- It stays opened for the session duration

OPEN MASTER KEY

DECRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!'

 

-- Restoring the certificate by the private key

-- the password is the one we used to encrypt it on the source SQL Server instance

CREATE CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

FROM FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate.cer'

WITH PRIVATE KEY

        (

                FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate_private_key.key'

                , DECRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

        )

 

-- Getting the list of files in the backup file

-- The instruction is identical for a non-encrypted database

RESTORE FILELISTONLY

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

 

-- Finally restoring the database

-- The instruction is identical for a non-encrypted database

RESTORE DATABASE ContosoRetailDW

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH MOVE 'ContosoRetailDW2.0' TO 'D:\SQL Server\\ContosoRetailDW_EncryptBackup_data.mdf'

, MOVE 'ContosoRetailDW2.0_log' TO 'D:\SQL Server\ContosoRetailDW_EncryptBackup_log.ldf'

, STATS = 1

 

-- Closing the master key

CLOSE MASTER KEY

 

In the case of a restoration automation, we may not want to restore and open the database master key each time.

This is achievable by running the following command, after having restored and opened the master key in the same session :

 

ALTER MASTER KEY REGENERATE

WITH ENCRYPTION BY PASSWORD = 'aN0th€r$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

Taking encrypted backups with Maintenance Plans

The dialog window to customize maintenance plan backup tasks had been revamped in SQL Server 2014, and reveals the option to take encrypted backups. It lets you pick the encryption algorithm among the four available.

 

However, there is not a way yet to manually create a certificate or an asymmetric key from this interface, or from SQL Server Management Studio Object Explorer : it has to be created following the steps described earlier.

Backing up and restoring in Azure

SQL Server 2014 enables to backup databases to Microsoft’s cloud platform : Azure (More exactly since SQL Server 2012 SP1 CU4). This nice feature is now enabling companies :

-          to store their backups outside their data center, covering the case in which it would face a complete outage;

-          to save their data in a geographically remote location at a low cost compared to investing in a secondary data center.

Prerequisites

In order to backup a database in Azure, we need to have :

-          An Azure subscription

-          Access to Azure by the Azure Portal (or via PowerShell)

-          An Azure Storage Account created

-          A storage container created. In this example, it is named “backup”.

Instructions to save a database to Azure

As the documentation reveals, instead of specifying the TAPE or DISK clause of the BACKUP instruction, we use URL, which makes the implementation of this feature remarkably simple.

Prior to this, we need to create a Credential : it is a security element that contains a user name and password for a user to access resources external to SQL Server. In our case, the Credential will contain the name of the storage account, and the primary key to this storage account.

In the print-screen below, we find the name of the storage account under the NAME column :

 

Clicking the MANAGE ACCESS KEYS button brings a popup window, in which we find the storage primary access key. So to create the credential with which we will gain access to Azure storage, we write :

USE master;

 

CREATE CREDENTIAL SQLServer2014EncryptedBackupInTheCloud

WITH IDENTITY = 'account_name'

        , SECRET = 'AzureStoragePrimaryAccessKey_ItIsNormallyALongString'

Before issuing the BACKUP instruction, we need to get the URL that we will specify. We can get it by successively clicking on the storage account name, and on the CONTAINERS tab :

 

This is the occasion to confirm the name of the storage container : “backup”, which is also visible in the URL.

Finally, the instruction is :

BACKUP DATABASE ContosoRetailDW

TO URL = 'https://account_name.blob.core.windows.net/backup/ContosoRetailDW.bak'

WITH INIT, COMPRESSION, CHECKSUM, STATS = 1

        , CREDENTIAL = 'SQLServer2014EncryptedBackupInTheCloud'

        , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

Once the backup is complete, we can see the file in the container :

 

 

Restoring a database from Azure

Similar to the restoration instruction sequence we have seen for file backups, it is required to have :

-          the database master key and the certificate restored;

-          the credential existing; if necessary, we can recreate it based on the data gathered from the Azure portal website.

The command batch is then :

OPEN MASTER KEY

DECRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!'

 

RESTORE DATABASE ContosoRetailDW

FROM URL = 'https://account_name.blob.core.windows.net/backup/ContosoRetailDW.bak'

WITH CREDENTIAL = 'SQLServer2014EncryptedBackupInTheCloud'

Performance Considerations

SQL Server 2014 offers four algorithms to encrypt backup files, and ciphering is an intensive CPU activity. It is also possible that the various algorithms have a different impact on the size of the resulting backup files.

To measure the impact of the various algorithm on CPU usage, we have taken a Performance Monitor trace with the Processor / % Processor Time counter only, and taken a backup of the same database with each of the four algorithms, plus a non-encrypted backup. All backups were compressed. The batch of commands is :

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_NO_ENCRYPTION.bak'

WITH INIT, COMPRESSION, CHECKSUM

 

WAITFOR DELAY '00:00:10'

 

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_128.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

 

WAITFOR DELAY '00:00:10'

 

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_192.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

WAITFOR DELAY '00:00:10'

 

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_256.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

WAITFOR DELAY '00:00:10'

 

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_TRIPLE_DES_3KEY.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

 

We wait 10 seconds between each backup, so as for the CPU usage to show bumps on the graph between each backup execution. Below is the commented Perfmon trace graph. It shows that while the AES algorithms have not much impact on the CPU usage compared to each other and to a backup taken with no encryption, the TRIPLE DES algorithm uses 25-30% more CPU than the AES ones.

 

 

After having integrated the Perfmon trace file into a table, and associated the CPU usage to the encryption algorithm used based on the time, we computed the median of the CPU usage, which produces the result below :

 

 

 

 

 

Finally, we can look at the backup duration and the backup file size. A few columns have been added to the system tables in the msdb system database, to expose backup encryption :

-          dbo.backupset

  • key_algorithm
  • encryptor_thumbprint
  • encryptor_type

-          dbo.backupmediaset

  • is_encrypted

Below is a query that retrieves the backup history, specifying the columns from the dbo.backupset table listed above :

;WITH

        CTE AS

        (

                SELECT  database_name

                        , last_backup_start_date_time

                        , physical_device_name

                        , backup_size / (1024 * 1024) AS backup_size_MB

                        , compressed_backup_size / (1024 * 1024)  AS compressed_backup_size_MB

                        , CAST(backup_size / compressed_backup_size AS decimal(5,2)) AS compression_ratio

                        , key_algorithm

                        , encryptor_thumbprint

                        , encryptor_type

                FROM    (

                                SELECT          S.database_name

                                                , MAX(S.backup_start_date) AS last_backup_start_date_time

                                                , MF.physical_device_name

                                                , S.backup_size

                                                , S.compressed_backup_size

                                                , S.key_algorithm

                                                , S.encryptor_thumbprint

                                                , S.encryptor_type

                                FROM            msdb.dbo.backupset AS S

                                INNER JOIN      msdb.dbo.backupmediafamily AS MF

                                                        ON S.media_set_id = MF.media_set_id

                                GROUP BY                S.database_name

                                                , S.type

                                                , MF.physical_device_name

                                                , S.backup_size

                                                , S.compressed_backup_size

                                                , S.key_algorithm

                                                , S.encryptor_thumbprint

                                                , S.encryptor_type

                        ) AS BH

        )

SELECT          D.name AS database_name

                , C.last_backup_start_date_time

                , C.physical_device_name

                , CAST(C.backup_size_MB AS decimal(15,2)) AS backup_size_MB

                , CAST(C.compressed_backup_size_MB AS decimal(15,2)) AS compressed_backup_size_MB

                , compression_ratio

                , C.key_algorithm

                , C.encryptor_thumbprint

                , C.encryptor_type

FROM            sys.databases AS D

INNER JOIN      CTE AS C

                        ON D.name = C.database_name

ORDER BY                C.last_backup_start_date_time

 

 

We obtain the following result, which clearly demonstrates that the encryption has no impact on the compression of the backup file size :

 

 

About the author

Nicolas Souquet is a Bangkok-based SQL Server database architect, and also a writer and speaker. He has centered his career on SQL Server databases modeling and performance tuning. With 10 years of industry experience, he has been working on all versions of SQL Server, ranging from 2000 to 2014, and has been continuously involved in several OLTP data-intensive environments. He has been rewarded SQL Server MVP in 2009, 2012 and 2013 for publishing articles on his blog ( https://blog.developpez.com/elsuket/ ) and animating SQL Server conferences in Bangkok. He is now gaining interest in Business Intelligence implementations with SQL Server. Finally, he has participated in writing a book on SQL Server 2014 in French with two other SQL Server MVPs, Frédéric Brouard and David Barbarin, which will be released in July 2014.

 About MVP Monday

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.