Getting Started

Getting Started with SQL EKM

Quick start guide to encrypt your first database with DuoKey EKM

Applies to:

SQL Server 2016+Windows Server 2012 R2+Windows onlyEnterprise / Developer

Overview

DuoKey SQL EKM provides external key management for Microsoft SQL Server Transparent Data Encryption (TDE). This guide walks you through the essential steps to encrypt your first database.

Prerequisites

• Microsoft SQL Server 2016 or later (Enterprise or Developer Edition on Windows)
• Windows Server 2012 R2 or later (or Windows 10/11 for development)
• Administrator privileges on the SQL Server machine
• SQL Server Management Studio (SSMS) installed
• Access to DuoKey cloud platform
• Network connectivity (HTTPS port 443) to DuoKey platform
• The EKM provider DLL is Authenticode-signed and its signing chain is trusted on the machine
• The provider config.toml is present at C:\ProgramData\DKE\EKM\config.toml

Quick Start Steps

1

Create SQL EKM App in DuoKey Platform

Create your SQL EKM application in the DuoKey platform:

1. Log in to DuoKey cloud platform
2. Navigate to Apps section
3. Click "Create New APP"
4. Select "SQL EKM" and click "Install Now"
5. Fill in required details and assign roles
6. Select a vault for your keys
7. Download the setup file
8. Note the enrolled agent key provided with the setup file

Tip

Keep the setup file and its enrolled agent key secure - the agent key is the single value SQL Server uses to authenticate to DuoKey.

2

Install EKM Provider on SQL Server

Deploy the DuoKey EKM provider on your SQL Server. The provider is a single Authenticode-signed DLL, DuoKeyCryptoProvider.dll, delivered by the setup file from the DuoKey app.

1. Run the setup file from the DuoKey app to deploy the provider
2. Place DuoKeyCryptoProvider.dll in a folder the SQL Server service can read, for example C:\Program Files\DKE\EKM (not C:\Windows\System32)
3. Confirm the provider config.toml is present at C:\ProgramData\DKE\EKM\config.toml
4. Confirm the DLL is code-signed and its signing chain is trusted on the machine

Important

SQL Server refuses to load an unsigned EKM DLL. The provider must be Authenticode-signed and its signing chain trusted on the SQL Server machine before it can be registered.

Note

The provider is a single DLL. There are no separate helper libraries to install, and no registry changes are required for a standard installation - all connection settings live in config.toml, which the setup file delivers. An optional HKLM\SOFTWARE\DKE\EKM registry key exists for advanced configurations, but it is not needed here.

3

Configure SQL Server

Configure SQL Server to use the DuoKey EKM Provider:

Enable EKMSQL

-- Enable advanced options
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO

-- Enable EKM provider
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
GO

Register Cryptographic ProviderSQL

CREATE CRYPTOGRAPHIC PROVIDER DuoKeyProvider
FROM FILE = 'C:\Program Files\DKE\EKM\DuoKeyCryptoProvider.dll';
GO

Create Provider CredentialSQL

-- Create provider credential (SECRET is the enrolled agent key from the setup file)
CREATE CREDENTIAL DuoKeyCredential
WITH IDENTITY = 'dke-ekm-service',
SECRET = '<agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DuoKeyProvider;
GO

-- Map credential to login
ALTER LOGIN [sa]
ADD CREDENTIAL DuoKeyCredential;
GO

Important

The SECRET is the single enrolled agent key from your setup file - replace <agent-key> with that value. The IDENTITY is just a label; it can be any name you choose.

Open the Vault Master KeySQL

-- Open the pre-provisioned RSA master key that lives in the DuoKey vault
-- (RSA_2048, RSA_3072, or RSA_4096, chosen when the app was created).
-- PROVIDER_KEY_NAME must match the master key name provisioned for your app.
USE master;
CREATE ASYMMETRIC KEY SQL_EKM_Key
FROM PROVIDER DuoKeyProvider
WITH PROVIDER_KEY_NAME = 'TDE_MASTER',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Note

The master key is pre-provisioned in the DuoKey vault and never leaves it, at the RSA key size (RSA_2048, RSA_3072, or RSA_4096) selected when the app was created. Use CREATION_DISPOSITION = OPEN_EXISTING to reference the existing key - do not create a new one.

Create Login and TDE Credential from the KeySQL

-- Create a login mapped to the asymmetric key (used by TDE)
CREATE LOGIN SQL_EKM_Login
FROM ASYMMETRIC KEY SQL_EKM_Key;
GO

-- Create the TDE credential (SECRET is again the enrolled agent key)
CREATE CREDENTIAL SQL_EKM_TdeCred
WITH IDENTITY = 'dke-ekm-tde',
SECRET = '<agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DuoKeyProvider;
GO

-- Map the TDE credential to the key login
ALTER LOGIN SQL_EKM_Login
ADD CREDENTIAL SQL_EKM_TdeCred;
GO

4

Encrypt Your First Database

Create Test DatabaseSQL

-- Create a test database
CREATE DATABASE TestDB;
GO

USE TestDB;
CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  SSN VARCHAR(11)
);
GO

Enable TDE EncryptionSQL

-- Create Database Encryption Key
USE TestDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_Key;
GO

-- Enable encryption
ALTER DATABASE TestDB
SET ENCRYPTION ON;
GO

Verify Encryption StatusSQL

SELECT
  DB_NAME(database_id) AS DatabaseName,
  encryption_state,
  CASE encryption_state
      WHEN 3 THEN 'Encrypted'
      WHEN 2 THEN 'Encryption in progress'
      ELSE 'Other'
  END AS Status,
  percent_complete
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'TestDB';
GO

5

Test and Verify

Test Data OperationsSQL

-- Insert test data
USE TestDB;
INSERT INTO Employees (ID, Name, SSN)
VALUES (1, 'John Doe', '123-45-6789');
GO

-- Query data (works transparently)
SELECT * FROM Employees;
GO

-- Final verification
SELECT
  d.name AS DatabaseName,
  d.is_encrypted,
  dek.encryption_state
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek
  ON d.database_id = dek.database_id
WHERE d.name = 'TestDB';
GO

Tip

When encryption_state = 3, your database is fully encrypted and protected by DuoKey.

Troubleshooting Quick Reference

Common Issues & Solutions

Issue	Quick Fix
EKM not enabled	sp_configure 'EKM provider enabled', 1; RECONFIGURE;
Cannot open key	Verify PROVIDER_KEY_NAME matches the provisioned master and use CREATION_DISPOSITION = OPEN_EXISTING
Provider fails to load	Confirm the DLL is signed and trusted, and the path in FROM FILE is readable by the SQL Server service
DLL not found	Verify DuoKeyCryptoProvider.dll is in the install folder, e.g. C:\Program Files\DKE\EKM
Connection error	Check network connectivity to DuoKey (port 443) and config.toml at C:\ProgramData\DKE\EKM
Credential error	Verify SECRET is the enrolled agent key from the setup file

What's Next?

Backup & Restore

Backup encrypted databases and restore to other servers

Key Rotation

Rotate encryption keys for enhanced security

Uninstallation

Properly remove the EKM provider

Summary

What You've Accomplished

Created SQL EKM AppIn DuoKey platform

Installed EKM ProviderOn SQL Server

Configured SQL ServerFor external key management

Encrypted DatabaseUsing TDE with DuoKey

See Also

• SQL EKM Overview
• Database Encryption
• ↗SQL Server EKM (Microsoft)