---
title: "Fortanix DSM with Microsoft SQL Server - Always Encrypted"
slug: "fortanix-dsm-with-microsoft-sql-server-always-encrypted"
updated: 2026-04-01T08:17:14Z
published: 2026-03-21T17:25:58Z
canonical: "support.fortanix.com/fortanix-dsm-with-microsoft-sql-server-always-encrypted"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.fortanix.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Fortanix DSM 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*](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15)*.*

> [!NOTE]
> NOTE
> 
> Ensure that you have performed the steps from [*Data Security Manager with Microsoft SQL TDE Integration – Before You Begin*](/v1/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin)*.*

## 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* [*Fortanix CNG/EKM*](https://fortanix.zendesk.com/hc/en-us/sections/27488968118932-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 **I accept the terms in the License Agreement** check box 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:

```bash
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:

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

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

```bash
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/](https://eu.smartkey.io/)
  - APAC: [https://apac.smartkey.io/](https://apac.smartkey.io/)
  - United States of America: [https://amer.smartkey.io/](https://amer.smartkey.io/)

For example,

```bash
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:

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

Where, `&lt;DSM_API_KEY&gt;` refers to the Fortanix DSM API key as copied in [*Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin*](https://support.fortanix.com/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin#35-copying-the-api-key).

## 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`:

```bash
CREATE DATABASE employee
```
3. Run the following commands to create the table `employee`:

```bash
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:

```bash
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 **ADD SECURITY OBJECT** to create a new security object.

![](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/Add-SO(21).png)

**Figure 1: Add security object**
2. On the **Add new Security Object** page:
  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*](https://support.fortanix.com/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin#43-creating-a-group).
  3. Select **GENERATE**.
  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*](/v1/docs/fortanix-data-security-manager-with-microsoft-sql-server-always-encrypted#62-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 **New Column Encryption Key** to create the Column Master Keys.
3. Enter the following details:
  - **Name:** Enter the required name of the key.
  - **Key Store:** Select **Key Storage Provider (CNG)** from the drop down menu.
  - **Select a provider:** Select **Fortanix KMS CNG Provider** from the drop down menu.

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16920869760276.png)

**Figure 2: Column master key**
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*](/v1/docs/fortanix-data-security-manager-with-microsoft-sql-server-always-encrypted#52-create-column-master-key). For example, select **Fortanix_CMK** key from the drop down menu.

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16920924635540.png)

**Figure 3: Column master encryption key**
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 **Encrypt Columns** to encrypt the columns.

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16920941255316.png)

**Figure 4: Encrypt column**
2. On the **Introduction** screen, click **Next**.

![Picture2.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921056903316.png)

**Figure 5: Introduction**
3. On the **Column Selection** screen, select the following:
  1. **Encryption Type:** Select 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.4: Create Column Encryption Key*](/v1/docs/fortanix-data-security-manager-with-microsoft-sql-server-always-encrypted#64-create-column-encryption-key)*.*

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921092803348.png)

**Figure 6: Column selection**
4. Click **Next**.
5. On the **Run Settings** screen, select **Proceed to finish now** and click **Next**.

![Screenshot 2023-11-21 225602.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/21146010358676.png)

**Figure 7: Run settings**
6. On the **Summary** screen, wait until the results are processed.

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921092810900.png)

**Figure 8: Summary**
7. Click **Finish** to view the results.

![image](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921071877268.png)

**Figure 9: 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]
> 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*](/v1/docs/fortanix-data-security-manager-with-microsoft-sql-server-always-encrypted#50-fortanix-cng-client)*.*
3. Run the following command to install the SQL Server PowerShell module:

```bash
install-Module -Name SqlServer -AllowClobber
```
4. Use the following sample script to decrypt the data:

```bash
# # 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
```

![Picture3.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921159684116.png)

### 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* [*Rotate Always Encrypted keys using SQL Server Management Studio*](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 6.3: Create Column Master Key*](/v1/docs/fortanix-data-security-manager-with-microsoft-sql-server-always-encrypted#63-create-column-master-key).

![](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/Always_Encrypted_App_Created.png)

**Figure 10: Key created on Fortanix DSM**

![Picture5.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921233303956.png)

**Figure 11: 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 **Rotate**.
    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**.

![Picture6.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921271644308.png)

**Figure 12: 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]
> NOTE
> 
> Each column encryption key is protected with a column master key to be rotated, must have exactly one encrypted value.

> [!WARNING]
> 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 **Cleanup**.
    3. Review the list of column encryption key values to be removed.
    4. Click **OK**.

![Picture7.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921334726036.png)

**Figure 13: Clean up**
  5. **After Rotation:**

To verify if that rotation is done successfully, refer to the following figures:
    1. Column Master Key:

![Picture8.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921330920980.png)

**Figure 14: Column master key**
    2. Column Encryption Key:

![Picture9.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/16921334764180.png)

**Figure 15: Column encryption key**

Fortanix Data Security Manager (DSM) is the world’s first cloud service secured with Intel® SGX. With Fortanix DSM, you can securely generate, store, and use cryptographic keys and certificates, as well as other secrets such as passwords, API keys, tokens, or any blob of data. Your business-critical applications and containers can integrate with Fortanix DSM using legacy cryptographic interfaces (PKCS#11, CNG, and JCE) or using the native Fortanix DSM RESTful interface.

## Related

- [Fortanix DSM with Microsoft SQL Server TDE - Advanced](/fortanix-dsm-with-microsoft-sql-server-tde-integration-advanced.md)
- [CMK Rotation - Fortanix DSM Version Below 5.6](/cmk-rotation-fortanix-dsm-version-below-5-6.md)
- [Fortanix DSM with Oracle TDE - Advanced](/using-fortanix-dsm-with-oracle-tde-advanced-integration.md)
- [FX2200 Hardware Guide](/fortanix-data-security-manager-fx2200-hardware-guide.md)
- [What languages and interfaces are supported by Fortanix Data Security Manager?](/what-languages-and-interfaces-are-supported-by-fortanix-data-security-manager.md)