SQL Server database backup encryption

A database is one of the most important parts of every information system and therefore is an often target of hackers. Encryption is the process of obfuscating data with the use of a key and/or password making the data unintelligible to anyone without a corresponding decryption key or a password.

SQL Server offers two ways of encrypting data:

  1. Transparent Data Encryption (TDE)

    SQL Server 2008 introduced Transparent Data Encryption (TDE) that enables encrypting of an entire database. TDE protects the database against unauthorized access to the hard disks or backups on which a database is stored. As Transparent Data Encryption encrypts data “on the fly” it showed performance issues resulting in significantly increased CPU usage during the e.g. backup operations.

  2. Backup Encryption

    SQL Server backup encryption is introduced in SQL Server 2014 and it supports encrypting database backups directly from the database engine. As in case of the Backup Encryption feature encryption/decryption is performed only when backing up and restoring a database therefore there are no performance issues.

    3. How does SQL Server backup encryption work?

    SQL Server has a hierarchical encryption infrastructure where each layer in the hierarchy encrypts the layer below.

    The first hierarchy layer is the Service Master Key (SMK). Service Master Key is generated automatically during the SQL Server installation and stored in the system master database. SMK is unique for every SQL Server instance. Service Master Key is encrypted based on the credentials for the SQL Server service account and the Windows Data Protection API (DPAPI) key.

    The next layer is a Database Master Key (DMK) of the master database. Database Master Key is unique to each system master database for each SQL Server instance. Database Master Key is encrypted using the Service Master Key.

    The next level in the hierarchy is a certificate that can contain a private key that is protected by the Database Master Key, or an asymmetric key (note that if using an asymmetric key for encrypting the backup data only asymmetric keys that reside in the Extensible Key Management (EKM) provider are supported).

    Choosing a SQL Server backup algorithm

    SQL Server backup encryption feature provides data encryption with the AES 128, AES 192, AES 256, and Triple DES (3DES) algorithms.

    Data Encryption Standard (DES)

    Data Encryption Standard, also known as the Data Encryption Algorithm (DEA), is developed in the early seventies and published in 1977. Data Encryption Standard is a block cipher that encrypts data in 64-bit blocks. DES is a symmetric algorithm meaning that the same algorithm and a key are used for both encryption and decryption. A key length of 56-bits. A brute force DES-cracking machine can find a key in approximately 3.5 hours. And with time DES will only become less secure.

    In order to improve security, there are DES variants such as Triple DES (3DES). Triple DES is a way of using Data Encryption Standard encryption three times and has a key length of 168 bits, but it’s also has been proven to be ineffective against brute force attacks.

    Advanced Encryption Standard (AES)

    Advanced Encryption Standard (AES), also known as Rijndael which is its original name, is a specification established in 2001 for the encryption of electronic data.

    Advanced Encryption Standard consists of three block ciphers, AES 128, AES 192 and AES 256 and each block cipher encrypts data in 128 bits blocks. A key length is 128, 192, and 256 bits, respectively. AES is, like DES, also a symmetric algorithm.

    Advanced Encryption Standard is used worldwide and adopted by the U.S. government and it displaces the Data Encryption Standard (DES) in terms of security.

    The best option is to use AES 256 encryption as using the stronger encryption requires more CPU power to encrypt the data and also more CPU power to decrypt it in case of an attempt to break the encryption.

    Prerequisites to SQL Server Backup Encryption

    Before performing a backup encryption check if there is a Service Master Key and a Database Master Key in the master database. As a Service Master Key is generated automatically during the SQL Server installation it should already be contained in the master database. The presence of SMK and DMK is checked by querying the master.sys.symmetric_keys catalog view and search for the ##MS_DatabaseMasterKey## row in the results: 

    SELECT * FROM master.sys.symmetric_keys

    If the ##MS_DatabaseMasterKey## row does not exist, use the following query to create it: 

    CREATE MASTER KEY ENCRYPTION BY PASSWORD='TestPass'

    Next we need to create a certificate: 

    USE master
GO
CREATE CERTIFICATE BackupCertificate
WITH SUBJECT = 'SQL Server 2014 Backup Encryption test';
GO

    If a certificate is not backed up prior to backing up a database in T-SQL the following warning will be displayed in the results:

    Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

    To back up a certificate and master keys use the following queries:

    Backup the Service Master Key: 

    -- Backup the Service Master Key
USE master
GO
BACKUP SERVICE MASTER KEY

TO FILE = 'F:\BackupKeys\SQL2014_SMK.key'

ENCRYPTION BY PASSWORD = 'SMKDBEncryption';

GO

    Backup the Database Master Key: 

    -- Backup the Database Master Key

BACKUP MASTER KEY

TO FILE = 'F:\BackupKeys\SQL2014_DMK.key'

ENCRYPTION BY PASSWORD = 'DMKDBEncryption';

GO

    Backup the Certificate: 

    BACKUP CERTIFICATE BackupCertificate

TO FILE = 'F:\BackupKeys\SQL2014_BackupCertificate.cer'

WITH PRIVATE KEY

        (

                FILE = 'F:\BackupKeys\SQL2014_CertificateKey.key'

                , ENCRYPTION BY PASSWORD = 'CertificateDBEncryption'

        );

GO

    Now we finished preparations for creating an encrypted backup and we have all four files backed up:

    SQL Server Backup Encryption by using T-SQL

    To backup a database with encryption by using T-SQL the encryption algorithm and certificate need to be specified: 

    BACKUP DATABASE [AdventureWorksDW2014]
TO DISK = N'F:\EncryptedBackups\TestTSQLEncryptedBackup.bak'
WITH
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = BackupCertificate
   )
 GO

    Scheduling an encrypted backup requires a SQL Server Agent job to be made.

    Restoring a SQL Server encrypted backup in T-SQL

    If restoring a database from an encrypted backup file is performed on the same SQL Server instance the restore operation is performed as usual as the keys and the certificate are already contained in the master database and opened automatically in the process of decryption: 

    RESTORE DATABASE [AdventureWorksDW2014]

FROM DISK = 'F:\EncryptedBackups\TestTSQLEncryptedBackup.bak'

    SQL Server Backup Encryption in SQL Server Management Studio

    To perform an encrypted backup in SQL Server Management Studio:

    1. Right click on a database and select the Tasks ➜ Back Up option:

    2. In the Back Up Database wizard under the General tab select a database and set the destination:

    3. One of the restrictions of encrypted backups is that they cannot be appended to an existing backup set, therefore SQL Server Management Studio requires setting a database to backup to a new media set. Under the Media Options tab of the Backup Up Database wizard select the Back up to a new media set, and erase all existing backup sets option and enter a media set name and description:

    4. Under the Backup Options tab select the Encrypt backup option and set the encryption algorithm and the certificate:

    Restoring a SQL Server encrypted backup in SQL Server Management Studio

    To restore an encrypted backup in SQL Server Management Studio:

    1. Right click on the Databases node in the Object Explorer pane and select the Restore Database option:

    2. In the Restore Database wizard browse to a folder where the backup is stored and click OK:

    Downsides of native backup and restore tasks in SQL Server Management Studio, beside the inability for automation, are that SQL Server Management Studio doesn’t offer an option for a report about the backup and restore processes, and also a cross-server restore would require a large amount of manual work.

    SQL Server Backup Encryption by using Maintenance Plans

    The Back-Up Database task in Maintenance Plans in SQL Server 2014 also has the option to take an encrypted backup.

    To perform an encrypted backup by using Maintenance Plans:

    1. From the Maintenance Plans option under the Management node in the Object Explorer pane select the Maintenance Plan Wizard.

    2. In the Select Maintenance Task page of the wizard select the Back Up Database task and click Next:

    3. In the Define Back Up Database Task page select a database to backup under the General tab.

    4. In the same page under the Options tab select the Backup encryption option and set the algorithm and a certificate or an asymmetric key:

    To execute the maintenance plan right click the plan under Maintenance Plans option and select execute:

    Although the backup process can be scheduled as a SQL Server Agent job from the Maintenance Plan Wizard the disadvantage of Maintenance Plans is that there is no ‘Restore Database’ task. For the purpose of restoring an encrypted (or any) backup, the Execute T-SQL Statement Task has to be used by adding a restore T-SQL script in the task.

    Useful resources:

    Encryption Hierarchy
    Backup Encryption
    SQL Server Confidential – Part II – SQL Server Cryptographic Features

     

    Related posts:

    1. How to schedule a database backup restore in SQL Server
    2. Manage multiple database backups across different SQL Server instances
    3. How to perform a SQL backup and restore history cleanup
    4. SQL Server database shrink – How and when to schedule and perform shrinking of database files
    5. How to create and manage SQL backup policies
    April 1, 2015