Fotanix Data Security Manager with Microsoft SQL Server - Always Encrypted

Last Updated: July 26, 2023 17:11

1.0 Introduction

This document describes the steps to integrate the Fortanix Data Security Manager (DSM) with Microsoft SQL Always Encrypted Server.

For more information, refer to the https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

1.1 Prerequisites

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

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

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.

2.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 install to the test column decryption.

2.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)

3.0 Integration Steps

3.1 Fortanix CNG Client

The Fortanix CNG Provider must be installed on every target machine. Refer to https://support.fortanix.com/hc/en-us/articles/360018084132-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.

3.1.1 Installation

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

On the Fortanix KMS Client Setup dialog box, click the Next button.
Select the checkbox for I accept the terms in the License Agreement and click the Next Button.
Enter the location for installing the Fortanix KMS Client as C:\Program Files\Fortanix\KMS Client\.
Click the Install button to install the Fortanix KMS client.
After the installation is done, click the Finish button.

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

Run the following command to navigate to FortanixKmsClientConfig.exe file.
```
cd C:\Program Files (x86)\Fortanix\KmsClient\
```
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 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 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 machine --api-endpoint https://<fortanix_dsm_url>
```
Run the following command to configure the Fortanix KMS Server URL for the current user:
```
FortanixKmsClientConfig.exe user --api-endpoint {KMS_URL} 
```
To configure proxy information, add --proxy http://proxy.com or --proxy none to unconfigure proxy.
Run the following command to configure the API key for the machine keystore:
```
FortanixKmsClientConfig.exe machine --api-key {KMS_URL} 
```
OR
Run the following command to configure the API key for the user keystore:
```
FortanixKmsClientConfig.exe user --api-key {KMS_URL} 
```

3.1.3 Creating Groups

A Fortanix DSM group is a collection of security objects created by and accessible by users and applications that belong to the group. The user who creates a group automatically gets assigned the role of group administrator. You can add more users to the group in the role of administrators or auditors. You can also add applications to the group to enable the applications to create and use security objects in that group.

To add a group, specify the following:

The title of the group (required).
A short description for the group (not mandatory).
Users in your account as members.
Applications in your account to add to the group so that they can use the security objects in the group. Refer to “Section 3.1.4- Creating Apps” to know the steps for creating the app.
Add a quorum approval policy (optional). A group administrator may enable a quorum approval policy for a group, which mandates that all security-sensitive operations in that group would require a quorum approval.

Figure 1: Adding New Group

3.1.4 Creating Apps

An application can use Fortanix DSM to generate, store, and use security objects, such as cryptographic keys, certificates, or an arbitrary secret. Examples of applications include web servers, PKI servers, key vaults, and so on. An application can interact with Fortanix DSM using the REST APIs or the PKCS#11, JCE, or CNG providers.

To add an application, specify the following:

Name of the application (required).
Type of the application. Select the value as interface.
A short description of the application.
Select the authentication method as API key.
Assign the app to the MSSQL group as created in the “Section 3.1.3- Creating Group”.

After the application has been added, you can use the API key to authenticate the CNG client to Fortanix DSM and start making calls to do cryptographic operations.

Figure 2: Adding New App

3.2 SQL Always Encrypted

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

Open the SQL Server Management Studio and connect to the database.
Run the following commands to create database employee:
```
CREATE DATABASE employee
```

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

Run the following commands to insert data into the table:

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

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

Log in to the Fortanix DSM user interface and create an RSA key such as Always_Encrypted_Key_Database_Name.

Figure 3: Create Key
After the key is created, log in to the SQL Server Management Studio.
Navigate to the Databases > employee > Security > Always Encrypted Keys > Column Master Keys to create the Column Master Keys.
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.
  
  Figure 4: Column Master Key
Click the OK button.

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

Navigate to the Databases > employee > Security > Always Encrypted Keys > Column Encryption Keys to create the Column Encryption Keys.
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 3.2.2 – Create Column Master Key. For example, select Fortanix_CMK key from the drop down menu.
  
  Figure 5: Column Master Encryption Key
Click the OK button.

3.3.4 Encrypt Columns Using Always Encrypted Key

Perform the following steps:

Navigate to the Databases > employee > Encrypt Columns to encrypt the columns.

Figure 6: Encrypt Column
On the Introduction screen, click the Next button.

Figure 7: Introduction
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 3.2.3: Create Column Encryption Key.
  
  Figure 8: Column Selection
Click the Next button.
On the Run Settings screen, select the Proceed to finish now radio button and click the Next button.

Figure 9: Run Settings
On the Summary screen, wait until the results are processing.

Figure 10: Summary
Click the Finish Button to view the results.

Figure 11: Results

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

Run the following command to install the SQL Server PowerShell module:
```
install-Module -Name SqlServer -AllowClobber
```

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"

#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

3.4.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 3.2.2: Create Column Master Key.
  
  Figure 12: Key Created on Fortanix DSM
  
  Figure 13: Key Created on SQL Server
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.
    
    Figure 14: Rotate the Key
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.
    
    Figure 15: Clean Up
5. After Rotation:
  To verify if that rotation is done successfully, refer to the following figures:
  1. Column Master Key:
    
    Figure 16: Column Master Key
  2. Column Encryption Key:
    
    Figure 17: Column Encryption Key