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.

    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 Database page of the Backup wizard select a server and a database to backup:

    3. In the Type an output page set the destination and customize a name of a backup:

    4. In the Options page select the Encrypt backup option and set the encryption algorithm, Encryptor type (certificate or an asymmetric key), and the Encryptor name (name of the Certificate previously created):

    5. In the Schedule page ApexSQL Backup offers an option to create a backup schedule or to execute the command immediately, as well as the option to send an email notification on either failure or success:

    6. For the purpose of the article we’ve executed the command immediately to measure the elapsed time:

    7. 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 Destination page of the Restore wizard select a destination server and a database:

      3. In the Source page ApexSQL Backup offers the Specific backup file option using the user friendly file wizard in addition to the standard Folder scan option:

      4. In the Options page select the data folders for data and log files and choose to perform after-restore verification if required:

      5. The Schedule page offers an option to create a restore schedule or to execute the command immediately, as well as the option to send an email notification on either failure or success, same as the Backup wizard.

      Useful resources:

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

      April 1, 2015
  • Hiram Fleitas León

    This doesn’t show how to restore that encrypted backup onto another svr and what commands you need to run in between on the other svr, which is a common scenario if the main svr is unavailable or the backups need to be restored somewhere else for any reason.