Using CAKM for Microsoft SQL Server EKM Provider with Database Backup Encryption

This section provides information on encryption options for Microsoft SQL Server database backups to manage the security of data which has been backed up to the file system. This data is backed up in the form of database backup files by using SQL Server backup encryption feature introduced from Microsoft SQL Server 2014.

Microsoft SQL Server Database Backup Encryption

With the growing need to keep data secure, ensure to secure your database backup files, especially those on a server file system. With native Microsoft SQL Server backups, the data in the backup file is stored in plain text on the file system. It is easily read using a text editor. Depending on the data types used in your tables, some data is much easier to view than other data. In the following images, the backup files are opened in Notepad. You can see which database backup file is encrypted:

Microsoft SQL Server native backup without encryption:

Microsoft SQL Server native backup with backup encryption:

From Microsoft SQL Server 2014 onward, Microsoft SQL Server can encrypt the data while creating a backup. By specifying the encryption algorithm and an encryptor (Asymmetric Key) when creating a backup, you can create an encrypted backup file.

Prerequisites

To encrypt the database backup during backup operation, specify an encryption algorithm and an encryptor to secure the encryption key. Supported encryption options include:

• Encryption algorithm:

  • AES_128

  • AES_192

  • AES_256

  • Triple_DES_3Key

• Encryptor: Asymmetric key

Changes in System Tables

There are changes to system tables that provide information about the key algorithm, encryption type, and encryption thumbprint used while the database backup is encrypted.

msdb.dbo.backupset

This table in msdb system database contains a row for each backup set. A backup set contains the backup for a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device(s). key_algorithm, encryptor_thumprint, encryptor_type columns of this DMV provide information on whether the backup is encrypted, what encryptor type is used, and the encryptor thumbprint.

SELECT TOP 5
name,
key_algorithm,
encryptor_thumbprint,
encryptor_type,
is_password_protected
FROM msdb.dbo.backupset AS backupset with (NOLOCK)
WHERE type IN ('D', 'I') AND database_name = 'MyTestDb'
ORDER BY backupset.backup_start_date DESC
GO

msdb.dbo.backupmediaset

This table in msdb system database contains one row for each backup media set. The is_encrypted column indicates whether the backup is encrypted or not. 0 indicates not-encrypted and 1 means encrypted. Initially, this value is set to NULL which indicates non-encrypted backupmediaset.

SELECT TOP 2
name,
media_set_id,
is_encrypted,
is_password_protected,
is_compressed, *
FROM msdb.dbo.backupmediaset AS mediaset with (NOLOCK)
ORDER BY mediaset.media_set_id DESC
GO

Considerations

The following restrictions apply to the encryption options:

• If you are using the asymmetric key to encrypt the backup data, only asymmetric keys residing in the CAKM for Microsoft SQL Server EKM provider are supported.

• Stronger encryption algorithm (depending on the encryption algorithm chosen) consumes more CPU resources than a weaker encryption algorithm.

• Microsoft SQL Server Express Edition and Microsoft SQL Server Web Edition do not support encryption during backup. However, restoring from an encrypted backup to an instance of Microsoft SQL Server Express Edition or Microsoft SQL Server Web Edition is supported.

• Previous versions of Microsoft SQL Server cannot read encrypted backups.

• Appending to an existing backup set option is not supported for encrypted backups. If an encrypted backup file using WITH INIT has been generated, it is not possible to append another encrypted backup to the same file. Doing so returns the following error:

  Error: Msg 3095, Level 16, State 1, Line 11
  The backup cannot be performed because 'ENCRYPTION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'ENCRYPTION' or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
  Msg 3013, Level 16, State 1, Line 11
  BACKUP DATABASE is terminating abnormally.
  

Permissions

• To encrypt a database backup during backup or to restore from an encrypted backup, VIEW DEFINITON permission is required on the asymmetric key that is used to encrypt the database backup.

• BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

• The account performing the restore should have VIEW DEFINITION permissions on the asymmetric key used to encrypt during backup.

Database Backup Encryption Methods

The following sections list the detailed steps to encrypt the database backup during backup.

Microsoft SQL Server Management Studio

In Microsoft SQL Server Management Studio, on the Backup Options page or Backup Database wizard, you can select encryption, and specify the encryption algorithm and the asymmetric key to use for the encryption. Steps:

1. Create an asymmetric key in master database as below:

   CREATE ASYMMETRIC KEY <key_name_in_sql_server>
   FROM PROVIDER <provider_name>
   WITH ALGORITHM = <algorithm_name>,
   PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
   CREATION_DISPOSITION=CREATE_NEW
   

2. Create a credential with the same Identity that was used by login_name.

3. Create a login and map it to the asymmetric key & credential created in step 1 & 2 above.

4. Connect to the appropriate instance of Microsoft SQL Server Database Engine. In Object Explorer, expand server name, and then the Databases.

5. Right click on the database, point to Tasks, and then click Back Up.

   

6. The Back Up Database dialog box appears:

   

7. On the General and Media Options pages, enter the database backup options as you would do in a normal scenario while taking a database backup using Backup Database dialog box.

8. For database backup encryption, the options are listed in Backup Options page of Backup Database dialog box:

   

9. To create an encrypted backup, check Encrypt backup.

10. Select an encryption Algorithm from the drop down list.

11. Select the Asymmetric key (created in step 1) from the list of existing certificates or asymmetric keys.

    Note

    • The encryption option is disabled if you select to append an existing backup set option on Media Options page of Backup Database wizard. Choose backup to a new media set, and erase all existing backup sets.

    • Create the key before starting the process. Asymmetric key created before initiating the Backup Database wizard will be listed in the drop-down.

12. Click OK to begin the encryption process.

13. On successful completion, you should be able to see the following message “The backup of database database_name completed successfully.”

Restoring Database Backup Files

Following encrypted databases can be restored on a server:

• TDE enabled backup

• Database backup encrypted by Asymmetric key

Prerequisites

The EKM client must be installed on the target Microsoft SQL hosted server. For more information on installing EKM client, refer to Installing, Uninstalling, and Upgrading CAKM for Microsoft SQL Server EKM Provider.

Configuring EKM on New Microsoft SQL Server Instance

To configure EKM on the new Microsoft SQL server instance:

1. Login to the Instance.

2. Validate the Microsoft SQL Server version on the target instance. It should be same as the instance from which the backup is taken.

   Select @@version
   

3. Enable the EKM on the Microsoft SQL Server instance.

   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   sp_configure 'EKM provider enabled', 1;
   RECONFIGURE;
   

4. Load the CAKM for Microsoft SQL Server EKM Provider DLL.

   CREATE CRYPTOGRAPHIC PROVIDER <provider_name>
   FROM FILE = '<installation_folder>'
   

   For example:

   CREATE CRYPTOGRAPHIC PROVIDER <provider_name>
   FROM FILE ='C:\Program Files\CipherTrust\CAKM For SQLServerEKM\cakm_mssql_ekm.dll'
   

5. Create the Microsoft SQL Server credential.

   CREATE CREDENTIAL <credential_name> WITH IDENTITY='<cipherTrust_manager_user>',
   SECRET='<cipherTrust_manager_user_password>'
   FOR CRYPTOGRAPHIC PROVIDER <provider_name>
   

6. Map the credential to Microsoft SQL Server credential.

   ALTER LOGIN [<domain-Name\<login_name>] ADD CREDENTIAL <credential_name>
   

7. Create an asymmetric key with same name which was used to enable the TDE or backup the database on the host machine.

   CREATE ASYMMETRIC KEY <key_name_in_sql_server>
   FROM PROVIDER <provider_name>
   WITH PROVIDER_KEY_NAME = '<key_name_in_cipherTrust_manager>',
   CREATION_DISPOSITION=OPEN_EXISTING;
   

   Note

   Steps 8 and 9 (creation of new credential and login) are only required when TDE enabled database is restored on the target server.

8. Create a credential for the Database Engine.

   CREATE CREDENTIAL <credential_name>
   WITH IDENTITY = '<cipherTrust_manager_user>',
   SECRET = '<cipherTrust_manager_user_password>'
   FOR CRYPTOGRAPHIC PROVIDER <provider_name>
   

9. Create a login for TDE and map it to credential created in the previous step.

   CREATE LOGIN <login_name>
   FROM ASYMMETRIC KEY <asymmetric_key_name>;
   ALTER LOGIN <login_name>
   ADD CREDENTIAL <credential_name>
   

Restoring the Database File

The following section lists the detailed steps to restore the encrypted database backup:

Microsoft SQL Server Management Studio

Copy the backup file from the source server to the destination server.

1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand the Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.

3. Right-click the database, select Tasks > Restore, and then click Files and Filegroups, which opens the Restore Files and Filegroups dialog box.

4. On the General page, enter the name of new or existing database in To database for restore operation.

5. On the General > Source section, specify the source and location of the backup sets to restore. Select the following option:

   Database

   Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history.

   Note

   If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.