Configure SQL Server

Configure SQL Server

Set up Microsoft SQL Server to use the DuoKey EKM Provider for TDE

Applies to:

SQL Server 2016+SSMS RequiredSysadmin Privileges

Overview

This guide explains how to configure Microsoft SQL Server to use the DuoKey EKM Provider for Transparent Data Encryption (TDE). Configuration has two parts: a one-time setup of the provider connection file on the host, and the SQL steps that enable EKM, register the provider, create a credential, and open the master key.

Provider Configuration File

Before running any SQL, complete the config.toml you deployed to C:\ProgramData\DKE\EKM\ during installation. It holds the connection settings the provider uses to reach the DuoKey platform.

C:\ProgramData\DKE\EKM\config.tomlTOML

# DuoKey endpoint
server_url = "https://<cockpit-host>"

# Provider identity assigned by DuoKey when the app was created
provider_guid = "<provider-guid>"
provider_name = "<provider-name>"

# Tenant and application identifiers from your SQL EKM App
tenant_id = "<tenant-id>"
app_id    = "<app-id>"

# Provider-level authentication to the platform (single value)
api_key = "<api-key>"

# Optional: mutual TLS
# client_cert_path = "C:\\ProgramData\\DKE\\EKM\\client.pem"
# client_key_path  = "C:\\ProgramData\\DKE\\EKM\\client.key"

What api_key in config.toml is for

The api_key field authenticates the provider's own connection to the platform - it is required (together with, or instead of, mutual TLS) before the provider will start, and it authorizes optional log forwarding to the platform. It does not authorize individual encrypt/decrypt/wrap operations for TDE: every cryptographic call is authorized per SQL Server session using the agent key supplied through CREATE CREDENTIAL ... SECRET (see below). Keep both values secret, but do not assume setting one removes the need for the other.

Keystore persistence

Alongside config.toml, the provider maintains keystore.json in the same folder. It maps the SQL asymmetric key to the DuoKey key so that TDE-protected databases reopen automatically after a SQL Server service restart. Do not delete it while databases are encrypted.

Prerequisites

• SQL EKM App created in DuoKey platform
• EKM Provider library and config.toml deployed on the host
• Agent key from the SQL EKM app
• SQL Server Management Studio (SSMS) installed
• Sysadmin privileges on SQL Server

Configuration Steps

1

Enable EKM Provider Support

First, enable Extensible Key Management in SQL Server. Open SSMS and execute:

Enable EKMSQL

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

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

Tip

This step is only required once per SQL Server instance.

2

Create the Cryptographic Provider

Register the DuoKey EKM Provider library from the folder where you deployed it.

Create ProviderSQL

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

Signature required

The library must be code-signed with a chain trusted on this machine. If the signature is not trusted, this command fails and SQL Server will not load the provider. See the installation guide for verifying the signature.

3

Create the Provider Credential

Create a credential that carries the agent key the provider uses to authenticate with DuoKey.

Create CredentialSQL

CREATE CREDENTIAL DkeEkmCredential
WITH IDENTITY = 'DuoKey EKM',
SECRET = '<agent-key>'
FOR CRYPTOGRAPHIC PROVIDER DkeEkm;
GO

Parameter	Description
IDENTITY	A free-text label for the credential. It is not validated against the platform.
SECRET	The single agent-key value from your SQL EKM App.

Single secret value

The SECRET is one value — the agent key. There is no client ID, client secret, or password to combine, and no delimiter format to follow.

4

Map the Credential to a Login

Map the credential to the login that will manage encrypted databases.

Map CredentialSQL

ALTER LOGIN [sa]
ADD CREDENTIAL DkeEkmCredential;
GO

Tip

Use the login that administers TDE, typically a sysadmin account.

Open the TDE Master Key

The TDE master key lives in DuoKey. You open a reference to it from SQL Server rather than generating a new key locally.

1

Open the Existing Asymmetric Key

Open Master Key from ProviderSQL

USE master;
CREATE ASYMMETRIC KEY TDE_Master
FROM PROVIDER DkeEkm
WITH PROVIDER_KEY_NAME = 'TDE_MASTER',
CREATION_DISPOSITION = OPEN_EXISTING;
GO

Parameter	Description
PROVIDER_KEY_NAME	The name of the key in the DuoKey platform.
CREATION_DISPOSITION	OPEN_EXISTING references the key already provisioned in DuoKey.

Open, do not create

Use CREATION_DISPOSITION = OPEN_EXISTING and do not specify WITH ALGORITHM. The master key is provisioned in DuoKey; SQL Server only opens a handle to it.

2

Create a Login from the Asymmetric Key

Create a login backed by the master key and map the provider credential to it. This is the engine login TDE uses to reach the key.

Create Engine Login and Map CredentialSQL

USE master;
CREATE LOGIN TDE_Master_Login
FROM ASYMMETRIC KEY TDE_Master;
GO

ALTER LOGIN TDE_Master_Login
ADD CREDENTIAL DkeEkmCredential;
GO

Verification

Verify your configuration with these queries.

Verify the Cryptographic ProviderSQL

SELECT
  name,
  guid,
  is_enabled
FROM sys.cryptographic_providers;
GO

Verify the CredentialSQL

SELECT
  name,
  credential_identity
FROM sys.credentials
WHERE name = 'DkeEkmCredential';
GO

Verify the Master Key Is Bound to the ProviderSQL

USE master;
SELECT
  ak.name AS KeyName,
  ak.algorithm_desc,
  cp.name AS ProviderName,
  cp.is_enabled
FROM sys.asymmetric_keys ak
INNER JOIN sys.cryptographic_providers cp
  ON ak.cryptographic_provider_guid = cp.guid
WHERE ak.name = 'TDE_Master';
GO

Tip

If the join returns a row with the provider enabled, SQL Server has successfully opened the master key through DuoKey and you are ready to enable TDE on a database.

Best Practices

Security

• Limit EKM credential access to sysadmins
• Protect config.toml with tight folder ACLs
• Enable SQL Server auditing
• Plan regular agent-key rotation

Performance

• Ensure stable network to DuoKey
• Monitor key operation latency
• Keep the host clock in sync

Maintenance

• Document all configuration
• Test disaster recovery regularly
• Preserve keystore.json while databases are encrypted

Troubleshooting

Next Steps

Encrypt Database

Enable TDE on your databases

Backup & Restore

Set up encrypted database backups

Key Rotation

Learn about key rotation procedures

See Also

• Database Encryption
• Getting Started
• ↗SQL Server EKM (Microsoft)