Fortanix Data Security Manager with Microsoft SQL Server - Always Encrypted

1.0 Introduction

This article describes the steps to integrate the Fortanix-Data-Security-Manager (DSM) with Microsoft SQL Always Encrypted Server. For more information, refer to the Microsoft Always Encrypted.

2.0 Prerequisites

Ensure the following:

• The Fortanix CNG 32-bit client must be installed and configured.

• Port 443 must be accessible from the SQL target machine to Fortanix DSM.

  Protocol	Inbound/ Outbound	Port Number	Load balancer (Yes/No)	Purpose
  TCP	Outbound	443	No	HTTPS – Used for calling the REST API. MS-SQL server will access the cluster/SaaS URL on this port. Each individual node will also need this port open.

• The SQL Server must be installed and configured on the target machine.

• Administrators must have the necessary privileges to access SQL Server Management Studio from the target machine.

3.0 SQL Server Always On Setup

This integration uses the following setup to demonstrate the Always Encrypted with Fortanix DSM:

• A Windows Server machine as a Domain Controller.

• A Windows Server machine with SQL Server and Fortanix CNG 32-bit client installed.

• A Windows 10 Professional machine with Fortanix CNG 64-bit client installed for the test column decryption.

4.0 Supported Versions

This SQL Always Encrypted integration is tested on the following versions:

• Microsoft SQL Server 2019

• Microsoft Server Management Studio 19 (v19.1)

• Fortanix DSM 4.19

• Fortanix CNG Client 4.19 (32-bit)

5.0 Fortanix CNG Client

The Fortanix CNG Provider must be installed on every target machine. Refer to the Fortanix CNG/EKM (32-bit) to download the CNG Provider.

FortanixKmsClient.msi installs the Fortanix CNG Provider, along with an EKM provider and the PKCS#11 library. Once installed, the Fortanix CNG Provider is configured to communicate with Fortanix DSM to perform cryptographic operations.

5.1 Installation

Perform the following steps to complete the installation on your machine:

1. On the Fortanix KMS Client Setup dialog box, click Next.

2. Select the checkbox for I accept the terms in the License Agreement and click Next.

3. Enter the location for installing the Fortanix KMS Client as C:\Program Files\Fortanix\KMS Client\.

4. Click Install to install the Fortanix KMS client.

5. After the installation is done, click Finish.

5.2 Configuring CNG Client

The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or the current user. 

1. Run the following command to navigate to FortanixKmsClientConfig.exe file.

   cd C:\Program Files (x86)\Fortanix\KmsClient\

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

   For example:

   Run the following command to configure the Fortanix KMS Server URL for the current user:

   FortanixKmsClientConfig.exe user --api-endpoint {KMS_URL}

   Run the following command to configure the Fortanix KMS Server URL for the local machine:

   FortanixKmsClientConfig.exe machine --api-endpoint {KMS_URL}

   Where,

   • KMS_URL refers to the Fortanix DSM URL. On-premises customers use the KMS URL and SaaS. The customers can use the following URLs based on the region.

     • Europe: https://eu.smartkey.io/

     • APAC: https://apac.smartkey.io/

     • United States of America: https://amer.smartkey.io/

       For example:

       FortanixKmsClientConfig.exe user --api-endpoint https://<fortanix_dsm_url>

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

4. Run the following command to configure the user keystore:

   FortanixKmsClientConfig.exe user --api-key <api_key>

   Where,

   • <DSM_API_KEY> refers to the Fortanix DSM API key as copied in Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin.

6.0 SQL Always Encrypted

6.1 Create Sample Database

For testing this integration, a sample database is created. However, you can use the existing database table to encrypt the required column.

1. Open the SQL Server Management Studio and connect to the database.

2. Run the following commands to create the database employee:

   CREATE DATABASE employee

3. Run the following commands to create the table employee:

   USE employee
   CREATE TABLE employee (first_name VARCHAR(128),last_name VARCHAR(128),empID DECIMAL,salary DECIMAL(6));
   GO

4. Run the following commands to insert data into the table:

   insert into employee values ('Adam','Parker','1','5000')
   insert into employee values ('John','Doe','2','4500')
   insert into employee values ('Peter','Williams','2','4500')
   GO

6.2 Creating a Security Object

Perform the following steps to create an RSA key in the Fortanix DSM:

1. In the DSM left navigation panel, click the Security Objects menu item, and then click the + button to create a new security object.

   

2. On the Add new Security Object page, do the following:

   1. Security Object name: Enter the name for your security object.

   2. Group: Select the group as created in the Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin.

   3. Select the GENERATE radio button.

   4. In the Choose a type section, select the RSA key type.

   5. In the Key Size section, select the size of the key in bits.

   6. In the Key operations permitted section, select Encrypt, Decrypt, Sign, and Verify operations to define the actions that can be performed with the cryptographic keys.

3. Click GENERATE to create the new security object.

The security object is added to the Fortanix DSM successfully.

6.3 Create Column Master Key

The column master keys are key-protecting keys that are used to encrypt the column encryption keys. The column master keys will be stored on the Fortanix DSM. The database only contains metadata about the column master keys, such as the type of key store and location. The column master key metadata is stored in the sys.column_master_keys (Transact-SQL) catalog view.

1. After the key is created in Section 6.2: Creating a Security Object, log in to the SQL Server Management Studio.

2. Navigate to the Databases → employee → Security → Always Encrypted Keys → Column Master Keys. Right-click the folder Column Master Keys and select the New Column Encryption Key option to create the Column Master Keys.

3. Enter the following details:

   • Name: Enter the required name of the key.

   • Key Store: Select the Key Storage Provider (CNG) option from the drop down menu.

   • Select a provider: Select the Fortanix KMS CNG Provider option from the drop down menu.

     

4. Click OK.

6.4 Create Column Encryption Key

The column encryption keys are content-encryption keys used to encrypt the data in the database columns. You can encrypt one or more columns with the same column encryption key or use multiple column encryption keys, depending on your application requirements. The column encryption keys are themselves encrypted, and only the encrypted values of the column encryption keys are stored in the database (as part of the column encryption key metadata). The column encryption key metadata is stored in the sys.column_encryption_keys (Transact-SQL) and sys.column_encryption_key_values (Transact-SQL) catalog views. The column encryption keys used with the AES-256 algorithm are 256 bits long.

1. Navigate to the Databases → employee → Security → Always Encrypted Keys → Column Encryption Keys to create the Column Encryption Keys.

2. Enter the following details:

   • Name: Enter the name of the column encryption key.

   • Column master key: Select the same column master key as created in Section 6.3 – Create Column Master Key. For example, select Fortanix_CMK key from the drop down menu.

     

3. Click OK.

6.5 Encrypt Columns Using Always Encrypted Key

Perform the following steps:

1. Navigate to the Databases → employee → Tables to encrypt the columns. Right-click the required table and select the Encrypt Columns option to encrypt the columns.

   

2. On the Introduction screen, click Next.

   

3. On the Column Selection screen, select the following:

   1. Encryption Type: Choose the required option from the drop down menu:

      • Deterministic encryption always generates the same encrypted value for a given plaintext value.

      • Randomized encryption uses a method that encrypts data less predictably.

   2. Encryption Key: Choose the same key name as created in Section 6.3: Create Column Encryption Key.

      

4. Click Next.

5. On the Run Settings screen, select the Proceed to finish now radio button and click Next.

   

6. On the Summary screen, wait until the results are processed.

   

7. Click Finish to view the results.

   

6.6 Verify Always Encrypted Columns

Perform the following steps on the testing server or the application server to view encrypted columns in plain text format:

NOTE

Ensure that the Fortanix CNG 64-bit client is installed and configured with the Fortanix endpoint and API key.

1. Log in to the Windows 10 Professional machine.

2. Install Fortanix Client 64-bit CNG Client. For more information, refer to Section 5.0: Fortanix CNG Client.

3. Run the following command to install the SQL Server PowerShell module:

   install-Module -Name SqlServer -AllowClobber

4. Use the following sample script to decrypt the data:

   # # Import the SqlServer module.
   Import-Module "SqlServer"
   # Connect to your database.
   $serverName = "<server_name>"
   $databaseName = "<database_name>"
   
   # Change the authentication method in the connection string, if needed.
   $connStr = "Data Source=$serverName;Initial Catalog=employee;Integrated Security=True;Column Encryption Setting = Enabled"
   
   # Testing using SQL login
   #$pwd = read-host -AsSecureString -Prompt "Password"
   #$connStr = "Data Source=$serverName; User Id=applogin;Initial Catalog=$databaseName; Password =Fortanix123!;TrustServerCertificate=true;Column Encryption Setting = Enabled"
   
   # Invoke the query to view the encrypted data
   Invoke-Sqlcmd -ConnectionString $connStr -Query "SELECT * FROM dbo.employee" | Format-Table -AutoSize

   

6.7 Rotate Always Encrypted Key

Rotating the Always Encrypted Keys is the process of replacing an existing key with a new one. You may need to rotate a key if it has been compromised, or to comply with your organization's policies or compliance regulations that mandate that the cryptographic keys must be rotated regularly.

• Rotate column encryption key: This involves decrypting the existing data with the current key and re-encrypting it using the new column encryption key.

• Rotate column master key: This involves decryption the column encryption key and protecting it with a new column master key. For more information, refer to the Rotate Always Encrypted keys using SQL Server Management Studio.
  Perform the following steps:

  1. Creating New Column Master Key:
     Create new security object in Fortanix DSM as described in Section 6.3: Create Column Master Key.

     

     

  2. Rotating the Key:
     After the key is rotated, the affected column encryption key will have two encrypted values: one value encrypted with the existing column master key and a new value encrypted with the new column master key.

     1. Navigate to the Security → Always Encrypted Keys → Column Master Keys folder and locate the column master key that you want to rotate.

     2. Right-click on the column master key and select the Rotate option.

     3. In the Column Master Key Rotation dialog box, select the name of your new column master key that you created in Step 1: Creating New Column Master Key in the Target field.

     4. Review the list of column encryption keys protected by the existing column master keys. These keys will be affected by the rotation.

     5. Click OK.

        

  3. Configure Application with New Column Master Key:

     Ensure that all your client applications query database columns that are protected with the rotated Fortanix column master key can access the new column master key.

     The column master key is stored in Fortanix DSM. The application must be implemented so that it can authenticate to Fortanix DSM and has permission to access the new column master key.

  4. Cleaning Up:

     After you have configured all your applications to use the new column master key, remove the values of column encryption keys that are encrypted with the old column master key from the database. Removing old values will ensure that you are ready for the next rotation.

     NOTE

     Each column encryption key is protected with a column master key to be rotated, must have exactly one encrypted value.

     WARNING

     If you remove the value of a column encryption key before its corresponding column master key has been made available to an application, the application will no longer be able to decrypt the database column.

     1. Navigate to the Security → Always Encrypted Keys folder and locate the existing column master key that you want to replace.

     2. Right-click on your existing column master key and select the Cleanup option.

     3. Review the list of column encryption key values to be removed.

     4. Click OK.

        

  5. After Rotation:

     To verify if that rotation is done successfully, refer to the following figures:

     1. Column Master Key:

        

     2. Column Encryption Key: