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:


    Next we need to create a certificate:

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

    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
    TO FILE = 'F:\BackupKeys\SQL2014_SMK.key'

    Backup the Database Master Key:

    -- Backup the Database Master Key
    TO FILE = 'F:\BackupKeys\SQL2014_DMK.key'

    Backup the Certificate:

    BACKUP CERTIFICATE BackupCertificate
    TO FILE = 'F:\BackupKeys\SQL2014_BackupCertificate.cer'
                    FILE = 'F:\BackupKeys\SQL2014_CertificateKey.key'
                    , ENCRYPTION BY PASSWORD = 'CertificateDBEncryption'

    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'
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = BackupCertificate

    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.

    SQL Server Backup Encryption by using ApexSQL Backup

    ApexSQL Backup is a SQL Server tool that enables managing and automating backup and restore jobs and stores details of all backup activities.

    ApexSQL Backup also offers instant backup creation and simplifies cross server backup and restore management and monitoring in comparison to a SQL Server native solution. Backup security is enhanced by automatically performing backup verification.

    ApexSQL Backup completely eliminates the need for writing T-SQL scripts and scheduling SQL Server Agent jobs. It utilizes easy to use wizards that guide the user through the process of creating backup and restore processes. In addition, it also allows creation of backup policies that can be applied to multiple databases and servers at once.

    To perform an encrypted backup in ApexSQL Backup:

    1. From the Home tab select the Backup option:

    2. In the Backup tab of the Backup wizard select a server and a database to back up. Set the backup type (Full database backup will be used as an example). On the same page, specify the destination path for the backup file by clicking on Add destination button.

    3. Besides the backup path, it is possible to set the tags that will be included in the filename of created backups. Add any of the available tags to the filename, or type in a custom string that should be added to the backup filename.

    4. If backup job needs to be run on a regular basis, click on the Schedule radio button, and Schedule wizard will run automatically. Set the frequency for the backup jobs in the wizard and click OK to save the changes for the schedule.

    5. In Advanced tab of the Backup wizard, check the Encrypt backup check box, and select the encryption algorithm. Make sure that encryption algorithm type matches the one that was created earlier (check the chapter “Prerequisites to SQL Server Backup Encryption” on the start of this article). In the same option group, select the Certificate or asymmetric key from the drop menu. Besides encryption, it is possible to set various options regarding verification and compression of the backup file.

    6. In the Notification tab of the wizard, check the conditions that will generate and send the Email notification on the job status. Add one or more Email addresses to send the notiffications to. This step is optional of course, and can be skipped if there is no need for Email notiffications.

    7. To run or schedule the job (depends if the Schedule or Run now option was selected in step 4), click OK button at the bottom of the wizard. For the purpose of the article we’ve executed the command immediately. Click Finish to complete the wizard.

    Restoring a SQL Server encrypted backup in ApexSQL Backup

    ApexSQL Backup offers both instant and automated backup restoration.

    To restore an encrypted backup in ApexSQL Backup:

    1. From the Home tab select the Restore option:

    2. In the main tab of the Restore wizard select a destination server and a database from the drop down menu, as well as the restore type. Since we created a full database backup in previous step, the full restore type will be selected. To specify the backup file to restore from, click on Add backup button.

    3. Paste the full file path of the backup file, or use browse for the backup file by clicking on the folder button.

    4. Same as with Backup wizard, check the Schedule radio button and set the schedule frequency, if restore job needs to be performed periodically

    5. In Advanced tab of the wizard, set the custom location of LDF and MDF file if needed. It is highly recommended to verify database after it is restored. To run the DBCC CECKDB on a database after the restore process, just check the box in front of the respective option. If any additional custom maintenance tasks need to be performed, check the Run a script box, and paste the query in the text box at the bottom.

    6. Optionally, configure Email notifications and recipients in Notification tab of the Restore wizard.

    7. Click OK at the form bottom to execute/schedule the job. As soon as restore operation completes, the info message is displayed

    Useful resources:

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


    April 1, 2015