Data Security Manager with Microsoft SQL Server TDE Integration - Backup & Restore

1.0 Overview

This article describes the step-by-step procedure to backup and restore the Microsoft SQL server Transparent Data Encryption (TDE) enabled database, which is protected by Fortanix Data Security Manager (DSM).

To perform the restoration, the target database MSSQL server must point to the same asymmetric key, which was previously created on the source database MSSQL server.

When the transparent data encryption is enabled in the database, the database backup files are encrypted as well. The following error appears on the screen when the user tries to restore a TDE enabled database backup to a different server:

ERROR_DIALOG_BOX.png

Figure 1: Error dialog Box

2.0 Backing Up the Data from Source

This section lists the steps for taking a backup of your database from the source server. This backup contains the data in encrypted format, which also contains the Data Encryption Key (DEK) protected by the Fortanix master key.

In the given example, we will use the database name as employee and we are backing it up from the Object Explorer or T-SQL command.

  1. Right-click the desired database (Company).
  2. Select Tasks and click the Back Up option from the context menu.
    TAKE_BACKUP_OF_SOURCE_SERVER.png
    Figure 2: Take Backup of Source Server
    1. Select the backup path.
      SELECT_THE_BACKUP_PATH.png
      Figure 3: Select the Backup Path
    2. Backup completed successfully.
      BACKUP_COMPLETED.png
      Figure 4: Backup Completed
  3. Move the backup database to the target server.
  4. Log in to the secondary target server.

3.0 Configuring TDE on Target Server

3.1 Enabling SQL Features

Run the following commands if Extensible Key Management (EKM) is not supported or enabled in the SQL server edition:

sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

Where,

  • EKM_Prov refers to the name of the provider defined by the user.

RUN_COMMANDS_FOR_ERROR_SCENARIO.png

Figure 5: Run Commands for Error Scenario

3.2 Creating Cryptographic Provider

Run the following commands to use the correct location of the EKM DLL:

CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO

Where,

  • EKM_Prov refers to the name of the provider defined by the user.

CREATE_CRYPTOGRAPHIC_PROVIDER.png

Figure 6: Create Cryptographic Provider

3.3 Creating Credentials

This section describes the steps to create the credentials to generate the master key on the Fortanix DSM using the SQL admin.

The SQL admin requires permission to connect to Fortanix DSM to generate the key.

  1. Perform the following steps to get the API key:
    1. Log in to the Fortanix DSM.

    2. From the UI left panel, click the Apps tab.

    3. Click COPY API KEY to copy the API key of your application and paste the DSM API key as the value for the SECRET parameter in the next command.

      COPY_API_KEY.png
      Figure 7: Copy API Key
  2. Run the following commands to create a credential using the copied API key in your SQL Server Studio that will be used by the system administrators:

    CREATE CREDENTIAL sa_ekm_tde_cred
    WITH IDENTITY = 'Identity1',
    SECRET = '<DSM API KEY>'
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
    GO
    Figure 8: Create Credential
  3. Add the credential to a high privileged user such as your own domain login in the format

    ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
    ADD CREDENTIAL "sa_ekm_tde_cred";
    GO

    Run the following commands in case there is no domain, and the machine is part of a workgroup or standalone:

    ALTER LOGIN LOCALHOST\Administrator
    ADD CREDENTIAL "sa_ekm_tde_cred";
    GO

    COMMAND_FOR_NO_DOMAIN.png
    Figure 9: Command for No Domain

    If you are not an administrator and hence unable to alter the login, open the Object Explorer and map the credentials as shown in the following image:

    MAP_CREDENTIALS.png
    Figure 10: Map Credentials

3.4 Creating Asymmetric Keys

The MSSQL admin has the credentials associated with creating the Master Encryption Key (MEK) on the Fortanix DSM. This section describes the steps to create the asymmetric key from the existing key in the Fortanix DSM.

Run the following commands to create an asymmetric key stored inside the EKM provider:

USE master
CREATE ASYMMETRIC KEY ekm_login_key FROM PROVIDER EKM_Prov
WITH PROVIDER_KEY_NAME='SQL_Server_Key',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Where,

  • ekm_login_key is the key name on SQL server created on the source server.
  • SQL_server_key is the key name on Fortanix DSM created on the source server.

CREATING_ASYMMETRIC_KEYS.png

Figure 11: Creating Asymmetric Keys

3.5 Creating Credentials (DB Engine)

Run the following commands to create a credential that will be used by the database engine:

USE master;
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2',
SECRET = '<DSM API KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;

Where,

  • ekm_tde_cred refers to the name of the credentials.
  • Identity2refers to the identity name. The value can be any name.
  • EKM_Prov refers to the Fortanix EKM Provider.
  • SECRET refers to the Fortanix DSM API Key.
CREATE_CREDENTIAL_FOR_DATABASE_ENGINE.png
Figure 12: Create Credential for Database Engine

3.6 Creating Login (DB Engine)

Run the following commands to add a login used by TDE and add the new credential to the login:

CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO
ALTER LOGIN EKM_Login
ADD CREDENTIAL ekm_tde_cred ;
GO

Where,

ADD_NEW_CREDENTIAL_TO_LOGIN.png

Figure 13: Add New Credential to Login

4.0 Restoring the Encrypted Database

This section describes the steps for restoring the encrypted backup on the target server. When the backup is encrypted with TDE at the time of restoration, the database tries to unlock the DEK using MEK. The SQL server starts the restoration process only if the respective master key is available on the database.

RESTORING_DATABASE.png

Figure 14: Restoring Database

 

Comments

Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful