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 Microsoft Always Encrypted.

2.0 Prerequisites

• The 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 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 are privileged 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:

1. A Windows Server machine, as a Domain Controller.

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

3. A Windows 10 Professional machine with Fortanix CNG 64-bit client install to the test column decryption.

3.1 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)

4.0 Fortanix CNG Client

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

FortanixKmsClient.msi installs the Fortanix CNG Provider, as well as an EKM provider and the PKCS#11 library. Next, to configure the CNG client Fortanix CNG Provider communicates with Fortanix DSM for crypto operations.

4.1 Installation

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

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

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

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

4. Click the Install button to install the Fortanix KMS client.

5. After the installation is done, click the Finish button.

4.2 Configuring CNG Client

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

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

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

2. 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 KMS URL and SaaS. The custul data-block-id="61dd6e97-d23d-43c7-910a-545c6c9f1751" type="disc">

   • KMS_URL refers to the Fortanix DSM URL. On-premises customers use 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. 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 then paste the DSM API key as the value for the <api_key> parameter in the next command.

      

5. Run the following command to configure the API key as copied in the previous step for the user keystore:

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

5.0 SQL Always Encrypted

5.1 Create Sample Database

For testing the 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 database  employee:

   CREATE DATABASE employee

3. Run the following commands to create 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

5.2 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 type of key store and location. The column master key metadata is stored in the sys.column_master_keys (Transact-SQL) catalog view.

1. Log in to the Fortanix DSM user interface and create an RSA key with the following permissions, such as Always_Encrypted_Key_Database_Name.

   • Encrypt

   • Decrypt

   • Sign

   • Verify

   

2. After the key is created, log in to the SQL Server Management Studio.

3. 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.

4. 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.

     

5. Click the OK button.

5.3 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-bit 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 5.2 – Create Column Master Key. For example, select Fortanix_CMK key from the drop down menu.

     

3. Click the OK button.

5.4 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 the Next button.

   

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

   1. Encryption Type: Chose 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 in a less predictable manner.

   2. Encryption Key: Chose the same key name as created in Section 5.3: Create Column Encryption Key.

      

4. Click the Next button.

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

   

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

   

7. Click the Finish Button to view the results.

   

5.5 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 must be installed and configured with Fortanix endpoint and API key.

1. Log into the Windows 10 Professional machine.

2. Install Fortanix Client 64-bit CNG Client. For more information, refer to Section 4.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 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"
   
   #Tesing 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

   

5.6 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 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 new column master key. For more information, refer to https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/rotate-always-encrypted-keys-using-ssms?view=sql-server-ver16. Perform the following steps:

  1. Creating New Column Master Key:
     Create new security object in Fortanix DSM as described in Section 5.2: 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 the column encryption keys, protected by the existing column master keys. These keys will be affected by the rotation.

     5. Click the OK button.

        

  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 the OK button.

        

  5. After Rotation:

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

     1. Column Master Key:

        

     2. Column Encryption Key: