1. Fortanix
  2. Fortanix Data Security Manager (DSM)
  3. Integration / Use-case
  4. Transparent Data Encryption

Using Fortanix Data Security Manager with Microsoft SQL Server TDE

Last Updated: 

1.0  Overview

Transparent Data Encryption (TDE) in Microsoft SQL Server Databases allows you to protect sensitive data in tables by encrypting them when they are stored on media. The data is transparently decrypted for authorized users or applications when they access the data. See Transparent Data Encryption for more information.

This article describes how to integrate Fortanix Data Security Manager (DSM) to be used with SQL Server TDE. The steps below have been tested for MSSQL version: 2014.f

Before starting, download the Fortanix DSM client installer for Windows 64-bit from Resources.

2.0  Encryption Hierarchy

SQL Server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. Asymmetric keys and symmetric keys can be stored outside of SQL Server in an Extensible Key Management (EKM) module.

The following illustration shows that each layer of the encryption hierarchy encrypts the layer beneath it and displays the most common encryption configurations. The access to the start of the hierarchy is usually protected by a password.

Hierarchy.png
Figure 1: Encryption Hierarchy

Keep in mind the following concepts:

  • For best performance, encrypt data using symmetric keys.
  • Database master keys are protected by the Service Master Key. The Service Master Key is created by the SQL Server setup and is encrypted with the Windows Data Protection API (DPAPI).
  • Other encryption hierarchies stacking additional layers are possible.
  • An Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server.
  • Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by API Key protected by the database master key of the master database, or by an asymmetric key stored in an EKM.
  • The Service Master Key and all Database Master Keys are symmetric keys.

The following illustration shows the same information in an alternative manner.

Hierarchy1.png
Figure 2: Encryption Hierarchy

3.0  Database TDE Key Management

3.1  Contents

  • What is DEK?
  • What is Master Key?
  • How is it securely stored?
  • How is it managed inside a database?

Before enabling TDE, a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or 3 Key Triple DES. Once TDE is enabled on a database, the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.

TDE also requires creating an EKM master key which is created on Fortanix DSM using the EKM module. The recommended algorithm for the master key is RSA 2048 or higher. This EKM Key is used to encrypt the service master key and other database keys. See the diagram below for the key hierarchy.

keymanagement.png
Figure 3: Key Management

3.2  DEK Storage

The database encryption key is stored inside the database boot page; the contents of this boot page are not encrypted so the DEK has to be encrypted by another key; we call it the DEK's encryptor. Currently, SQL Server allows encrypting a DEK by Fortanix DSM Asymmetric key. Besides the DEK, the boot page also contains other information necessary to identify and open an encrypted database.

3.3  DEK's Encryptor

In the case of an EKM key, the Asymmetric key resides on the Fortanix DSM end which makes management a little easier. In either case, it is important to hold on to this encryptor as long as the database or the log is dependent on it.

When you restore or attach a TDE database on another server make sure that the encryptor is present on this server as well. In case of an EKM key, the provider and the key should be available on this server as well.

4.0  Before you Begin

4.1  Limitations and Restrictions

  • You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. That user must be able to be authenticated by the EKM module.
  • Upon startup, the Database Engine must open the database. To do this, you should create a credential that will be authenticated by the EKM and add it to a login that is based on an asymmetric key. Users cannot sign in using that login, but the Database Engine will be able to authenticate itself with the EKM device.
  • If the asymmetric key stored by EKM Provider (Fortanix DSM) is lost, the database will not be able to be opened by SQL Server. Hence, it is recommended to never delete/edit SQL Server-managed keys from Fortanix DSM manually. Even after key rotation, it is recommended to keep the old keys, so that older backups can be used in contingency scenarios.
  • Access to install the Fortanix KMS Server file to configure it on the machine and user.

5.0  Security

5.1  Permissions

  • This article uses the following permissions:
  • To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
  • Requires ALTER ANY CREDENTIAL permission.
  • Requires ALTER ANY LOGIN permission.
  • Requires CREATE ASYMMETRIC KEY permission.
  • Requires CONTROL permission on the database to encrypt the database.

6.0  Installation

FortanixKmsClient.msi installs the Fortanix DSM CNG Provider, as well as the EKM provider and the PKCS#11 library.

6.1  Installation Prerequisites

If Extensible Key Management is not supported or enabled in the SQL server edition, then run the following commands:

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


SQL-Error1.png
Figure 4: Enable EKM

6.2  Windows Server Client Configuration

The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or current user with C:\Program Files\Fortanix\KmsClient\FortanixKmsClientConfig.exe.

The machine key store uses the local machine configuration, and the user key store uses the current user configuration.

For example, to configure the Fortanix KMS Server URL for the local machine, run:

FortanixKmsClientConfig.exe machine --api-endpoint https://sdkms.fortanix.com

To configure the Fortanix KMS Server URL for the current user, run:

FortanixKmsClientConfig.exe user --api-endpoint https://sdkms.fortanix.com

To configure proxy information, add --proxy http://proxy.com or --proxy none to unconfigure proxy.

6.3  SQL Server Configuration

To Configure SQL Server to use Fortanix EKM Provider, run the commands in Section 6.3.1 in SQL Server Studio.

6.3.1 Create Cryptographic Provider

  1. Use the correct location of the EKM DLL. 
    CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
    FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO

    Add_Cryptographic_Provider1.png
    Figure 5: Create cryptographic provider 

6.3.2 Create Credential

  1. Get the API Key:
    1. Go to https://sdkms.fortanix.com/#/ 
    2. In the UI left panel, click the Apps mceclip1.png tab: https://sdkms.fortanix.com/#/apps
    3. Click COPY API KEY  to copy the API key of your application, and then paste the key in the SECRET field of the command above. COPY_APIKEY1.png
      Figure 6: Copy API key</strong
  2. Create a credential using the copied API key in your SQL Studio Server 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

    6.png
    Figure 7: Create Credentials
  3. Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login]. 
    ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
    In case there is no domain and the machine is part of a workgroup/standalone, please run the commands below: 
    ALTER LOGIN LOCALHOST\Administrator
    ADD CREDENTIAL "sa_ekm_tde_cred";
GO
    Command_for_no_domain1.png
    Figure 8: Command for no domain If you are not an administrator and hence you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below. Map_Credential11.png
    Figure 9: Map credentials
  4. Create an asymmetric key stored inside the EKM provider. 
    USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO
    Create_asymmetric_key1.png
    Figure 10: Create Asymmetric key
  5. 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 ;
    Create_credential_for_DB_engine1.png
    Figure 11: Create credential for database engine
  6. 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
    Add_new_cred1.png
    Figure 12: Add the new credential to the login
  7. Create Database employee. 
    CREATE DATABASE employee
  8. Create table employee. 
    USE employee
    CREATE TABLE employee (first_name VARCHAR(128),last_name VARCHAR(128),empID DECIMAL,salary DECIMAL(6));
GO
    Create_table11.png
    Figure 13: Create Table
  9. Create the database encryption key that will be used for TDE. 
    USE employee
    CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO

    Create_DB_encry1.png
    Figure 14: Create Database encryption key
  10. Alter the database to enable transparent data encryption. 
    ALTER DATABASE employee
SET ENCRYPTION ON ;
GO

    Enable_TDE1.png
    Figure 15: Enable transparent data encryption
  11. Monitor TDE Progress: 

    SQL Server keeps track of the encryption progress and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns that are required to understand the progress of TDE. ‘Encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us the percent completed of the DB encryption state change.

    Encryption_state(int) Description
    0 No database encryption key present, no encryption
    1 Unencrypted
    2 Encryption in progress
    3 Encrypted
    4 Key change in progress
    5 Decryption in progress
    6 Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)
    The following T-SQL statement can be used to monitor TDE progress/status. 
    SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
    encryption_state_desc =
    CASE encryption_state
       WHEN '0' THEN 'No database encryption key present, no encryption'
       WHEN '1' THEN 'Unencrypted'
       WHEN '2' THEN 'Encryption in progress'
       WHEN '3' THEN 'Encrypted'
       WHEN '4' THEN 'Key change in progress'
       WHEN '5' THEN 'Decryption in progress'
       WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
       ELSE 'No Status'
       END,
    percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
    The output of the above query comes really handy to manage TDE, Now let’s move on to managing TDE.

7.0  Rotating Keys for Transparent Data Encryption (TDE) in Fortanix

Fortanix recommends updating your TDE security keys regularly by rotating the available symmetric and asymmetric encryption keys. Execute the following from the SQL query window.

7.1  Rotate keys for TDE

To rotate the keys for TDE, you must be logged in as a service account or system admin user to create the master key.

  1. Create the credentials. To know the procedure, follow the steps 1 to 3 mentioned in section 6.3.2 Create Credentials.
  2. Create a CREDENTIAL for EKM Provider created by Fortanix. 
    USE master ;
    GO
    CREATE ASYMMETRIC KEY ekm_login_key_v2
    FROM PROVIDER EKM_Prov
    WITH ALGORITHM = RSA_2048,
    PROVIDER_KEY_NAME = 'SQL_Server_Key_v2' ;
    GO

    Where SQL_EKM_KEY_v7 is the new rotation key.

    Rotate_Keys_in_Fortanix_DSM.PNG

    Figure 16: Rotate Keys in Fortanix DSM

    Rotate_Keys_in_the_Database.PNG

    Figure 17: Rotate Keys in the Database

  3. Create a CREDENTIAL for EKM Provider created by Fortanix. 
    USE master ;
    GO
    CREATE CREDENTIAL db_ekm_tde_cred_v2
    WITH IDENTITY = 'Identity_v2',
    SECRET = '{key}'
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov

    CREATE_CREDENTIAL_FOR_EKM_PROVIDER.PNG

    Figure 18: Create Credential for EKM Provider

    Where, Credential and Identity can be as per your internal organization policy.

  4. Create a login based on the recently created asymmetric key. 
    CREATE LOGIN EKM_Login_v2
    FROM ASYMMETRIC KEY ekm_login_key_v2 ;
    GO

    CREATE_LOGIN_FOR_ASYMMETRIC_KEY.PNG
    Figure 19: Create Login for Asymmetric Key

  5. Map the credential to the recently created login. 
    ALTER LOGIN <Name of Login> 
    ADD CREDENTIAL <Name of credential>;
    Map_Credentials.PNG
    Figure 20: Map Credentials
  6. Rotate the master key for Transparent Database Encryption Key. 
    USE employee;
    ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key_v2;
    Where employee is the Database name.
    Enable_TDE_Key_Rotation.PNG
    Figure 21: Enable TDE Key Rotation
    Old_Key.PNG
    Figure 22: Old Key
    New_Key.PNG
    Figure 23: New Key

8.0  Using Extensible Key Management on a SQL Server Failover Cluster

This section focuses on the preparation of the environment for 2-node SQL Server Cluster in Windows Server.

  1. Refer to SQL Server documentation to install a failover cluster.
    Setting up a Shared Storage
    To set up a shared storage disk for SQL Server Cluster, refer to the configuration procedures that apply for your shared storage solution. Plan the size of the shared storage depending on the number of certificates that you are enrolling.
  2. Once the cluster is up and running, install the Fortanix client on both nodes.
  3. Configure and set up the appliance on both nodes.
  4. Install Fortanix EKM client on both nodes.
  5. Configure the Fortanix EKM provider on both nodes.
  6. Open the SQL Server Management Studio to register the Fortanix EKM provider on the first node.
  7. Setup the credentials on the first node.
  8. Now create some keys using the Fortanix EKM provider on the first node.
  9. Create a table and encrypt some columns with the Fortanix EKM key with the first node.
  10. Shut down the first node.
  11. Now log in to the second node and decrypt the data encrypted on the first node.
  12. Data is decrypted successfully.

Extensible Key Management using Fortanix EKM is now working fine on a SQL Server cluster.

9.0  Using TDE Within an Always On Availability Group

These procedures have been tested for an availability group that used two servers. Server 1 held a (nominal) primary replica, Server 2 held a (nominal) secondary replica. Primary and secondary replicas were read/write. The configuration used Fortanix HSMs, and no shared disk. Each server could be logged into directly, or through a cluster availability group (virtual) address. The configuration also required a third server to act as Remote File System (RFS).

9.1  Setting up and Switching on TDE

NOTE
The MSSQL Server Studio Add Database Wizard (versions to SQL Server 2014) will not support the addition of a database that is already encrypted, or that includes a database encryption key even if encryption is switched off. However, you may set up TDE encryption for an existing non-encrypted database that is already within an availability group using T-SQL, as described in the steps below.
  • SQL Server (versions to SQL Server 2014) may not support a readable secondary using a clustered column store index within the context of availability group failover.

The following steps should be performed for each database, the primary, and each secondary, that is part of the availability group, and for which you wish to switch on TDE encryption. Before starting, it is assumed that the database you wish to encrypt:

  • Already exists
  • Is already part of an availability group within a cluster
  • Is NOT currently encrypted, and includes no database encryption key (TDEDEK)
  • Has never been encrypted before. If it has, you may see errors and a request for a log backup
NOTE
In the examples shown here, the database to be encrypted is called SourceDatabase, and the database wrapping key is called SQL_Server_Key2 in the SQLEKM provider, and ekm_login_key1 in the master database. Change names or other parameters to your own requirements. Also, these steps assume that a wrapping key of the same name does not already exist in either the SQLEKM provider or the master database.

Before proceeding with the following steps:

  • Make sure your database is recently backed up.
  • Make sure that primary and secondary replicas are synchronized within the availability group, and that failover can occur without any data loss.
  • If you prefer a particular server for the primary role, then you are failed over to that server.
  • You should also remember the roles (primary/secondary) that each server node starts with.

Perform the following steps in the order shown. The following description is written as if the server nodes retain the initial primary or secondary roles they begin with. You can use the availability group cluster virtual address, and manually failover between the nodes in order to access them but bear in mind this description refers to the initial (starting) role of each node, even if its actual role changes later.

Before you proceed, make sure that the Primary and Secondary database is in a synchronized state as shown below.

Mgmt_studio1.png
Figure 24: SQL Server Management Studio

Now install the Fortanix EKM provider in the Windows machine and set up the endpoints. The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or current user with C:\Program Files\Fortanix\KmsClient\FortanixKmsClientConfig.exe. The machine key store uses the local machine configuration, and the user key store uses the current user configuration. For example, to configure the Fortanix KMS Server URL for the local machine, run:
For Machine:

FortanixKmsClientConfig.exe machine --api-endpoint https://sdkms.fortanix.com

For User:

FortanixKmsClientConfig.exe user --api-endpoint https://sdkms.fortanix.com

Configuration for SQL Server

To Configure SQL Server to use Fortanix EKM Provider, run the following commands in SQL Server Studio: Use the correct location of the EKM DLL.

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

SQL_Server_Config1.png
Figure 25: Configure SQL Server

In case of an error such as "Extensible key management is not supported or enabled in this edition of SQL server":

Error_Ext1.png
Figure 26: SQL Server Error

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

ConfigSQL1.png
Figure 27: Reconfigure SQL Server 

Re-run the following command to add a cryptographic provider.

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

Add_Cryptographic_Provider2.png
Figure 28: Add Cryptographic Provider

Create a credential that will be used by the system administrators. See the Fortanix DSM Getting Started guide on how to generate an API Key.

CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO

 For getting the API Key:

Run the command again to create a credential using the copied API key in your SQL Studio Server.

CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM API KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO

Create_Cred_using_API1.png
Figure 30: Create Credential using API

Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login].

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

In case there is no domain, and the machine is part of a workgroup/standalone, please run the commands below:

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

NoDomain1.png
Figure 31: No Domain scenario

In case you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.

Map_Credential12.png
Figure 32: Map Credentials

  1. On Primary: Set up the database wrapping key, TDE credential and then log in:
      1. Make sure you are running this on the PRIMARY.
      2. This script sets up a TDE wrapping key, login, and credential on the primary.
      3. Create a wrapping key. 
        USE master
        CREATE ASYMMETRIC KEY SQL_Server_Key2 FROM PROVIDER EKM_Prov
        WITH PROVIDER_KEY_NAME= 'ekm_login_key1',
        CREATION_DISPOSITION = CREATE_NEW, ALGORITHM = RSA_2048;
        GO
      4. Create the wrapping key credential. 
        Use master
        CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY SQL_Server_Key2;
        CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'Identity1', SECRET = '<DSM API Key>'
        FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;
        ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
    2.  On (each) Secondary: Restart the SQL Server instance. Set up the database wrapping key, TDE credential, and then log in.
      1. Make sure you are running this on the SECONDARY.
        NOTE
        The wrapping key must already exist, as created by the primary.
      2. This script opens a wrapping key, TDE login, and credentials on the secondary.
      3. The credential must match the primary.
      4. Create the wrapping key.  
        USE master
        CREATE ASYMMETRIC KEY SQL_Server_Key2 FROM PROVIDER EKM_Prov
        WITH PROVIDER_KEY_NAME='ekm_login_key1',
        CREATION_DISPOSITION = OPEN_EXISTING; --Wrapping key should already have been created on the primary.
        GO
      5. Now sync the wrapping key credential on the secondary as created in the Primary. 
        USE master
        CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY SQL_Server_Key2;
        CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'Identity1', SECRET = '<DSM API Key>'
        FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;
        ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
    3. On both primary and secondary, check that the database remains synchronized. To do this, on the SQL Server Management Studio, look at the [Server name] → [Name of your database]. If after the previous steps you find that the database is now ‘Not Synchronized’, re-synchronize by running the following query:
      1. Run on the primary/secondary that appears to be unsynchronized with the availability group. 
        USE master;
        GO
        ALTER DATABASE [SourceDatabase] SET HADR RESUME
        If the database remains unsynchronized after performing this step, then you may have configuration problems. Try to correct this before proceeding.
    4. On Primary: Create the database encryption key and switch on the TDE encryption.
      1. Make sure you are running this on PRIMARY.
      2. Create the actual database encryption key (TDEDEK). 
        USE SourceDatabase;
        CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
        ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_Server_Key2;
        GO
      3. A short delay may be required here before switching on encryption. 
        WAITFOR DELAY '00:00:05';
        Set a delay period as required. One second = '00:00:01'
      4. Break any connection with the SourceDatabase so that encryption can commence. 
        USE [master];
        GO
      5. Enable TDE (switch on encryption) on the SourceDatabase: 
        ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
        GO

9.2  Key Rotation on SQL Always On Availability Group

On Primary

      1. On Primary, run the command below. 
        USE master;
        CREATE ASYMMETRIC KEY SQL_EKM_KEY_v1
        FROM Provider EKM_Prov
        WITH ALGORITHM = RSA_2048,
        PROVIDER_KEY_NAME = 'SQL_EKM_KEY_v1',
        CREATION_DISPOSITION=CREATE_NEW
        SQL-EKM-Key1.png
        Figure 33: Create New SQL EKM Key Where “SQL_EKM_KEY_v1” is the new key for rotation.
      2. Create a CREDENTIAL for EKM Provider created by Fortanix. 
        CREATE CREDENTIAL <Name of credential>
        WITH IDENTITY='<Name of EKM User>', SECRET='<DSM API KEY>'
        FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
        Create_CredentialEKM1.png
        Figure 34: Create Credential for EKM Provider Where the New Credential is “New Rotation” and tagged with Identity as “New Rotation”.
      3. Create a login based on the recently created asymmetric key. 
        CREATE LOGIN <Name of login>
        FROM ASYMMETRIC KEY SQL_EKM_KEY_v1;
        Create_Login_for_Asymmetric_key2.png
        Figure 35: Create Login for the Asymmetric Key
      4. Map the credential to the recently created login. 
        ALTER LOGIN <Name of Login>
        ADD CREDENTIAL <Name of credential>;
      5. On Primary, go to Object Explorer, Click Always On Availability group, select Availability Database and then select the Database, and suspend the Data movement as shown below. Suspend_the_data_movement1.png
        Figure 36: Suspend Data Movement

        Configure Access on an Availability Replica

        • In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
        • Expand the Always On High Availability node and the Availability Groups node.
        • Click the availability group whose replica you want to change.
        • Right-click the availability replica, and then click Properties.
        • In the Availability Replica Properties dialog box, you can change the connection access for the primary role and for the secondary role, as shown in Figure 35.
        • For the secondary role, select a new value from the Readable secondary drop list, as follows:
          • Yes
            All connections are allowed secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access. Please follow the screenshot below: Secondary_Database1.png
            Figure 37: Secondary Database
      6.  Enable Transparent Database Encryption Key Rotation. 
        USE AutoHa-sample; 
        ALTER DATABASE ENCRYPTION KEY
        REGENERATE
        WITH ALGORITHM = AES_128
        ALTER DATABASE ENCRYPTION KEY
        ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_KEY_v1;
        GO
        SELECT * FROM sys.dm_database_encryption_keys
        GO
        7. The Server Asymmetric Key will be rotated.
      7. Run the below command to check the status of the key as per the screenshot below. 
        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
           WHEN '0'  THEN  'No database encryption key present, no encryption'
           WHEN '1'  THEN  'Unencrypted'
           WHEN '2'  THEN  'Encryption in progress'
           WHEN '3'  THEN  'Encrypted'
           WHEN '4'  THEN  'Key change in progress'
           WHEN '5'  THEN  'Decryption in progress'
           WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
           ELSE 'No Status'
           END,
        percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys
        Status_Key1.png
        Figure 38: Check the Status of the Key

On Secondary

      1. Reopen the new key created on Primary. 
        USE master
        CREATE ASYMMETRIC KEY SQL_EKM_KEY_v1 FROM PROVIDER EKM_Prov
        WITH PROVIDER_KEY_NAME='SQL_EKM_KEY_v1',
        CREATION_DISPOSITION = OPEN_EXISTING;
        GO
        Reopen_the_new_Key1.png
        Figure 39: Reopen the new Key
      2. To create the same credential as per Primary. 
        CREATE CREDENTIAL <Name of credential>
        WITH IDENTITY='<Name of EKM User>', SECRET='<DSM API KEY>'
        FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
        Create_same_cred_as_prim1.png
        Figure 40: Create Same Credential as Primary
      3. Create a new login on Secondary. 
        CREATE LOGIN <Name of login>
        FROM ASYMMETRIC KEY SQL_EKM_KEY_v1;
        New_Login_on_Secondary1.png
        Figure 41: Create New Login on Secondary
      4. Once the Encryption key is regenerated on Primary, run the below query on secondary to check the status if the key has been rotated/altered. 
        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
           WHEN '0'  THEN  'No database encryption key present, no encryption'
           WHEN '1'  THEN  'Unencrypted'
           WHEN '2'  THEN  'Encryption in progress'
           WHEN '3'  THEN  'Encrypted'
           WHEN '4'  THEN  'Key change in progress'
           WHEN '5'  THEN  'Decryption in progress'
           WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
           ELSE 'No Status'
           END,
        percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys
        Check_Key_Status_on_sec1.png
        Figure 42: Check the Status of the key on Secondary

9.3  Troubleshooting

      • If your database has previously been encrypted, you may see errors at this point. If you are asked to take a pending log backup, then take backup using the below methods:
        • Using command:  
          BACKUP LOG employee TO DISK = 'C:\employee.TRN'
GO
          This will create a transaction log backup of the employee database and write the backup contents to file "C:\employee.TRN". The .TRN extension is commonly used for identifying that the backup is a transaction log backup.
        • Using SQL Server Management Studio:
            • Right click the database name.
            • Select Tasks > Backup.
            • Select Transaction Log as the backup type.
            • Select Disk as the destination.
            • Click Add... to add a backup file and type "C:\company.TRN" and click OK.
            • Click OK again to create the backup.
          Management_Studio1.png
          Figure 43: SQL Server Management Studio
      • If you get an error requesting that you take a log backup, try adapting the following code to your own requirements, and then run it. 
        USE master;
        GO
        ALTER DATABASE <Name-of-your-database>
        SET RECOVERY FULL;
        GO
        USE master;
        GO
        NOTE
        You should have provided a path to your backups when setting up your availability group.
        EXEC sp_addumpdevice 'disk', '<Name-of-your-device>',
        ‘<Path-to-your-backups>\<Name-of-your-log-backup-file>';
        GO
        Back up the log 
        BACKUP LOG <Name-of-your-database> TO <Name-of-your-device>;
        GO
        Drop backup device 
        EXEC sp_dropdevice '<Name-of-your-device>';

        Example:

        USE master;
        GO
        ALTER DATABASE SourceDatabase
        SET RECOVERY FULL;
        GO
        USE master;
        GO
        EXEC sp_addumpdevice 'disk', 'EncryptedSourceDatabaseBackupLog',
        '\\Server-2\NetWorkShareFolder\SourceDatabase_20160210122459';
        GO
        Back up the log 
        BACKUP LOG SourceDatabase TO EncryptedSourceDatabaseBackupLog;
        GO
        Drop backup device 
        EXEC sp_dropdevice 'EncryptedSourceDatabaseBackupLog';
      • Break any connection with the SourceDatabase so that encryption can commence. 
        USE [master];
        GO
        Enable TDE (switch on encryption) on the SourceDatabase. 
        ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
        GO

10.0  Migrating the TDE Enabled Databases to Another/ New Server

Considering that you have an SQL Server TDE already enabled on one of the Servers that is, SQL1, do the following steps to Migrate the TDE enabled database. Refer to Section 8 for configuring SQL server SQL1.

NOTE
Before you migrate the TDE Enabled Database Turn the Encryption off.
ALTER DATABASE Company
SET ENCRYPTION OFF ;
GO

SQL_Encryption_Off.png
Figure 44: Turn encryption off

10.1  Backup SQL1 Server Database

Now take the backup of your database from the SQL1 server.

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

      • Object Explorer:
        1. Right-click on the desired database (Company ).
        2. Select Tasks and then click Back Up. SQL_backup.png
          Figure 45: Take backup of SQL1
        3. Select the backup path. SQL_backuppath.png
          Figure 46: Select the backup path
        4. Backup completed successfully. SQL_backupSuccess.png
          Figure 47: Backup completed
        5. Move the backup database to the secondary server.
        6. Log in to the secondary server – SQL2.
        7. Perform the necessary configuration on Windows client for the server SQL2.

10.2  Configuration for Windows Client

Refer to Section 6.2 for steps to configure the windows client.

10.3  Configuration for SQL Server - SQL2

To Configure SQL Server to use Fortanix EKM Provider, run the following commands in SQL Server Studio:

      1. Use the correct location of the EKM DLL. 
        CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
        FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ; 
        GO
        Add_Cryptographic_Provider1.png
        Figure 48: Create cryptographic provider
        In case of an error such as "Extensible key management is not supported or enabled in this edition of SQL server": Error1.png
        Figure 49: Error Scenario Run the following commands: 
        sp_configure 'show advanced', 1
        GO
        RECONFIGURE
        GO
        sp_configure 'EKM provider enabled', 1
        GO
        RECONFIGURE
        GO
        SQL-Error1.png
        Figure 50: Run commands for error scenario

        Now, re-run the following command:

        CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
        FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
      2. Create a credential that will be used by the system administrators. See the Getting Started guide on how to generate an API Key.
        CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
      3. For getting the API key, go to https://sdkms.fortanix.com/#/
      4. In the UI left panel, click the Applications
      5. Click COPY API KEY to copy the API key of your application, and then paste the key in the SECRET field of the command above. SQL_CopyAPIkey.png
        Figure 51: Copy API Key
      6. Run the command again to create a credential using the copied API key in your SQL Studio Server.
        CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
        SQL_credential.png
        Figure 52: Create credential
      7. Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login].
        ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
        ADD CREDENTIAL sa_ekm_tde_cred ;
        GO
      8. In case there is no domain, and the machine is part of a workgroup/standalone, run the commands below:
        ALTER LOGIN MSSQL2017
        ADD CREDENTIAL "sa_ekm_tde_cred";
        GO

        SQL_NoDomain.png
        Figure 53: No domain scenario
      9. In case you are unable to alter the login, open the Object Explorer and map the Credentials as shown below.

        SQL_MapCredentials.png
        Figure 54: Map credentials
      10. Reopen the Asymmetric key which is saved in the EKM server. 
        CREATE ASYMMETRIC KEY ekm_login_key
        FROM PROVIDER [EKM_Prov]
        WITH PROVIDER_KEY_NAME = 'SQL_Key',
        CREATION_DISPOSITION = OPEN_EXISTING;
      11. 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 ;

        SQL_createCredential.png
        Figure 55: Create a Credential
      12. Add a login used by the 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

        SQL_AddCredential2Login.png
        Figure 56: Add New Credential to Login
      13. Restore the database in SQL Server – SQL2. 
      14. Once restored create the following Database Encryption Key that will be used for Encryption.
        USE Company
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_256
        ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
        GO

        SQL_CreateDEK.png
        Figure 57: Create DEK
      15. Alter the database to enable transparent data encryption.
        ALTER DATABASE employee
        SET ENCRYPTION ON ;
        GO

        SQL_EnableTDE.png
        Figure 58: Enable TDE
      16. Monitor TDE Progress: 

        SQL Server keeps track of the encryption progress and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns which are required to understand the progress of TDE. ‘Encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us the percent completed of the DB encryption state change.

        Encryption_state(int) Description
        0 No database encryption key present, no encryption
        1 Unencrypted
        2 Encryption in progress
        3 Encrypted
        4 Key change in progress
        5 Decryption in progress
        6 Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)
        The following T-SQL statement can be used to monitor TDE progress/status. 
        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
           WHEN '0' THEN 'No database encryption key present, no encryption'
           WHEN '1' THEN 'Unencrypted'
           WHEN '2' THEN 'Encryption in progress'
           WHEN '3' THEN 'Encrypted'
           WHEN '4' THEN 'Key change in progress'
           WHEN '5' THEN 'Decryption in progress'
           WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
           ELSE 'No Status'
           END,
        percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
        The output of the above query comes really handy to manage TDE.

11.0  Cell/Column Level Encryption (CLE)

In CLE separate data fields in the same table can be encrypted under different encryption keys. These keys can be protected by different credentials. Unlike TDE protection, the user will need to obtain keys from the SQL Extensible Key Management (EKM) provider and must have the correct credentials to authorize and load the encryption key(s) for the specific encrypted data they wish to access. Non-encrypted data is not affected by this and is visible to any authorized user.

Cell-level encryption will only work on data stored in the database as VARBINARY type. You must provide any necessary type conversions so that data is in VARBINARY form before encryption is performed. Decryption will return the data to its original VARBINARY structure. It may then be necessary to reconvert to its original type for viewing in human-readable form.

11.1  Creating a Symmetric Key from SQL Server

Use the following command to create a symmetric key from the SQL server:

USE Company
CREATE SYMMETRIC KEY dbAES256Key
FROM PROVIDER [EKM_Prov]
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Identity5',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;
GO

Once the symmetric key is created, you will be able to see the same key in Fortanix DSM.

11.2  Symmetric Key Creation in Fortanix Data Security Manager

Create a symmetric key in Fortanix DSM.

CreateKey_MSSQL.png
Figure 59: Symmetric key

After the key is created you can run the following command to encrypt the column from the database. Select the sample database as shown in the below command where we would encrypt the column salary.

NOTE
A Salary column should be added for having the VARBINARY structure.

  EncryptColumn_MSSQL.png
Figure 60: Encrypt column

To encrypt the salary column, run the following command to encrypt with the Symmetric key created earlier.

USE Company
UPDATE [dbo].[employee_test]
SET salary = EncryptByKey(Key_GUID('dbAES256Key'),
salary)

For Decryption of the salary column run the following command.

USE Company
UPDATE [dbo].[employee_test]
SET salary = DecryptByKey(salary);

11.3  Creating an Asymmetric Key From SQL Server

To create an asymmetric key, use the following command:

USE column_encryption_test
CREATE ASYMMETRIC KEY dbRSA2048 FROM PROVIDER [EKM_Prov]
WITH PROVIDER_KEY_NAME='ekmRSA2048',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048;
GO

CreateAsymmetricKey_MSSQL.png
Figure 61: Create Asymmetric Key

11.4  Asymmetric Key Creation in Fortanix Data Security Manager

Create an asymmetric key in Fortanix DSM.

CreateKeyAsym_MSSQL.png
Figure 62: Create Asymmetric Key

Once the asymmetric key is created, encrypt the employee id (empID) column. Run the following command to encrypt with the Asymmetric key created earlier.

USE Company
UPDATE [dbo].[employee]
SET empID = EncryptByKey(Key_GUID('dbRSA2048'),
empID)

For Decryption of the employee id (empID) column run the following command:

USE Company
UPDATE [dbo].[employee]
SET empID = DecryptByKey(empID);

11.5  View the Keys Present in the SQL Server

      • To view the symmetric keys in a database: 
        USE Company
        SELECT * FROM sys.symmetric_keys
        ViewSymmetricKeys_MSSQL.png
        Figure 63: View symmetric key
      • To view the asymmetric keys in a database: 
        USE Company
        SELECT * FROM sys.asymmetric_keys
        ViewAsymmetricKeys_MSSQL.png
        Figure 64: View Asymmetric Key

To check all the  asymmetric keys between the database and the cryptographic provider run the following commands:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '[EKM_Prov]');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
FULL OUTER JOIN sys.asymmetric_keys
ON sys.asymmetric_keys.thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
GO

To check all the  symmetric keys between the database and the cryptographic provider run the following commands:

DECLARE @ProviderId int;
SET @ProviderId = (SELECT TOP(1) provider_id FROM sys.dm_cryptographic_provider_properties
WHERE friendly_name LIKE '[EKM_Prov]');
SELECT * FROM sys.dm_cryptographic_provider_keys(@ProviderId)
FULL OUTER JOIN sys.symmetric_keys
ON sys.symmetric_keys.key_thumbprint = sys.dm_cryptographic_provider_keys.key_thumbprint
WHERE sys.dm_cryptographic_provider_keys.key_type = 'SYMMETRIC KEY'
GO

12.0  Logging

To find the external key manager (Ekm) logs, run the following command on the admin command prompt.

cd c:\
dir /s/a "EkmLog.txt"

The Ekm log file is always located in the following path: C:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Roaming\Fortanix\KmsClient

Where, MSSQLSERVER will change according to your local setup.

Comments

  • Avatar
    Yuxiang Cao

    For EKM's log, except searching, it usually located at

    C:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Roaming\Fortanix\KmsClient

    The "MSSQLSERVER" might change according to your local setup.

    0

Please sign in to leave a comment.

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