Microsoft SQL Server External (EKM)

Eviden KMS integrates with Microsoft SQL Server External key management using a Windows DLL implementing the SQL Server Extensible Key Management (EKM).

The Windows DLL is a SQL Server EKM provider that forwards key operations to the Eviden KMS over mutual TLS. This allows SQL Server features like column-level encryption and Transparent Data Encryption (TDE) to use keys managed by the Eviden KMS, without exposing key material to the SQL Server host.

The Windows DLL is available in a separate project on GitHub; pre-built signed DLLs are available for download.

Architecture & Workflow

The sequence diagram below shows how SQL Server interacts with the Cosmian EKM Provider DLL (cosmian_ekm_sql_server.dll), which forwards KMIP/REST operations over TLS to the Eviden KMS, persisting key state in the configured KMS database backend:

sequenceDiagram
    autonumber
    participant MSSQL as Microsoft SQL Server
    participant EKM as EKM Provider DLL (cosmian_ekm_sql_server.dll)
    participant KMS as Eviden KMS Server
    participant DB as KMS Database Backend (PostgreSQL / MySQL / SQLite / Redis)

    Note over MSSQL,EKM: Initialization & Authentication
    MSSQL->>EKM: Open provider & authenticate credential (mapped KMS username)
    EKM->>EKM: Load config.toml & client certificate
    EKM-->>MSSQL: Provider ready

    Note over MSSQL,DB: Asymmetric / Symmetric Key Provisioning
    MSSQL->>EKM: CREATE ASYMMETRIC KEY ... FROM PROVIDER [CosmianEKM]
    EKM->>KMS: POST /kmip (KMIP Create KeyPair / Key)
    KMS->>DB: Store key pair & attributes in DB
    DB-->>KMS: Key UID persisted
    KMS-->>EKM: KMIP Response with key UID
    EKM-->>MSSQL: Key handle returned to SQL Server

    Note over MSSQL,DB: TDE / Column Encryption Key Operations
    MSSQL->>EKM: Encrypt / Decrypt DEK using EKM Key
    EKM->>KMS: POST /kmip (KMIP Encrypt / Decrypt Request)
    KMS->>DB: Retrieve wrapping key via ObjectCache / DB
    DB-->>KMS: Key material retrieved
    KMS->>KMS: Perform crypto operation
    KMS-->>EKM: KMIP Encrypt/Decrypt Response (ciphertext / unwrapped DEK)
    EKM-->>MSSQL: Result returned to SQL Server Engine

Deployment Guide

This document explains how to install and configure the Cosmian EKM SQL Server provider, starting from a signed DLL. Follow the steps in order.

Prerequisites

1. Install SQL Server

Install SQL Server with default settings. The instance name used throughout this guide is the default instance (MSSQLSERVER). For a named instance, substitute MSSQL$<InstanceName> wherever a service name is needed.

Ensure the SQL Server Database Engine service account is noted (default: NT SERVICE\MSSQLSERVER).

2. Start the Eviden KMS

The KMS must be running and accessible before SQL Server loads the EKM provider.

# Example — adjust paths and config file as needed
cosmian_kms_server.exe --config kms_server.toml

The KMS must be configured with:

• TLS certificate trusted by this machine (or accept_invalid_certs = true in config.toml during development)
• At least one client certificate entry matching the SQL Server credential identity

Verify the KMS is reachable:

Test-NetConnection -ComputerName localhost -Port 9998

3. Prepare the machine (one-time)

# Creates directories and grants ACLs to the SQL Server service account
scripts\Initialize-EkmEnvironment.ps1

This is a one-time operation per machine.

4. Install the provider configuration

Create C:\ProgramData\Cosmian\EKM\config.toml (see config.toml.example for the full schema):

max_age_seconds = 1800
stale_collector_period_seconds = 120

[kms]
server_url = "https://localhost:9998"
accept_invalid_certs = false          # set true only for local dev

[[kms.certificates]]
username = "admin"
client_cert = 'C:\ProgramData\Cosmian\EKM\admin.cert.pem'
client_key  = 'C:\ProgramData\Cosmian\EKM\admin.key.pem'

Copy the mTLS client certificate and key files to the paths referenced above, and copy the KMS CA certificate so TLS verification succeeds.

5. Enable EKM in SQL Server

Connect to SQL Server (e.g. via SSMS or sqlcmd) and run:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'EKM provider enabled', 1;
RECONFIGURE;

6. Deploy the DLL

# Must be run as Administrator
scripts\Deploy-EkmDll.ps1

This script:

1. Verifies the DLL's Authenticode signature.
2. Stops the MSSQLSERVER service.
3. Copies the DLL to C:\Program Files\Cosmian\EKM\cosmian_ekm_sql_server.dll.
4. Starts the MSSQLSERVER service.
5. Registers (or re-registers) CosmianEKM as a cryptographic provider.

To deploy to a named instance or skip the service restart:

scripts\Deploy-EkmDll.ps1 -SqlServiceName "MSSQL`$MyInstance"
scripts\Deploy-EkmDll.ps1 -SkipServiceRestart   # register only

7. Register the provider

If Deploy-EkmDll.ps1 was run with -SkipRegistration, register manually:

CREATE CRYPTOGRAPHIC PROVIDER CosmianEKM
FROM FILE = 'C:\Program Files\Cosmian\EKM\cosmian_ekm_sql_server.dll';

Verify:

SELECT name, is_enabled FROM sys.cryptographic_providers WHERE name = 'CosmianEKM';

8. Create a credential and map it to a login

Each SQL Server login that uses EKM keys must have a credential that maps to a KMS identity (the username in config.toml).

-- Create the credential
CREATE CREDENTIAL Cosmian_EKM_Cred
WITH IDENTITY = 'admin',      -- must match [[kms.certificates]] username
     SECRET   = 'unused'
FOR CRYPTOGRAPHIC PROVIDER CosmianEKM;

-- Map to a Windows login (adjust the login name)
ALTER LOGIN [HOSTNAME\YourLogin] ADD CREDENTIAL Cosmian_EKM_Cred;

9. Create keys

USE master;

-- Asymmetric key (RSA 2048)
CREATE ASYMMETRIC KEY MyRsaKey
FROM PROVIDER CosmianEKM
WITH ALGORITHM = RSA_2048,
     PROVIDER_KEY_NAME = 'my-rsa-key',
     CREATION_DISPOSITION = CREATE_NEW;

-- Symmetric key (AES 256)
CREATE SYMMETRIC KEY MyAesKey
FROM PROVIDER CosmianEKM
WITH ALGORITHM = AES_256,
     PROVIDER_KEY_NAME = 'my-aes-key',
     CREATION_DISPOSITION = CREATE_NEW;

10. Column-level encryption (quick check)

USE master;

-- Symmetric key protected by the EKM asymmetric key
CREATE SYMMETRIC KEY ColKey
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY MyRsaKey;

-- Encrypt
OPEN SYMMETRIC KEY ColKey DECRYPTION BY ASYMMETRIC KEY MyRsaKey;
SELECT ENCRYPTBYKEY(KEY_GUID('ColKey'), N'Hello EKM!');
CLOSE SYMMETRIC KEY ColKey;

11. Transparent Data Encryption (TDE)

-- Create a login backed by the EKM asymmetric key
CREATE LOGIN Cosmian_TDE_Login FROM ASYMMETRIC KEY MyRsaKey;

-- Credential for unattended access
CREATE CREDENTIAL Cosmian_TDE_Cred
WITH IDENTITY = 'admin', SECRET = 'unused'
FOR CRYPTOGRAPHIC PROVIDER CosmianEKM;
ALTER LOGIN Cosmian_TDE_Login ADD CREDENTIAL Cosmian_TDE_Cred;

-- Enable TDE on a database
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY MyRsaKey;

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Monitor encryption progress:

SELECT db.name, dek.encryption_state, dek.percent_complete
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id;

State 3 means fully encrypted.

12. Run integration tests

# All-in-one: build + sign + deploy + copy test config
scripts\Prepare-Integration-Tests.ps1

# Then run the tests
cargo test --test integration -- --test-threads=1

Troubleshooting

Provider not loading

Check the EKM log file:

C:\ProgramData\Cosmian\EKM\logs\cosmian_ekm.log.<date>

"Cannot open database / invalid object name"

The SQL Server service account needs Modify rights on C:\ProgramData\Cosmian\EKM\. Re-run Initialize-EkmEnvironment.ps1.

KMS connection errors

• Verify config.toml has the correct server_url.
• Confirm the client certificate CN or SAN matches the SQL credential identity.
• Check the KMS server is running: Test-NetConnection localhost -Port 9998.

Key operations fail after redeployment

If the DLL is replaced, SQL Server must be restarted to reload it. Run Deploy-EkmDll.ps1 (which handles the restart automatically).