Enable TDE in Microsoft SQL Server by using EKM

2min
This section shows how to use an asymmetric key stored on the KMES Series 3 to enable transparent data encryption (TDE) in Microsoft SQL Server for database protection.
You must run all of the following commands in a Query window in SQL Server Management Studio.
1
Run the following commands to create a credential for system administrators to use:
SQL
CREATE CREDENTIAL EkmCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
CREATE CREDENTIAL EkmCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
﻿
Change the values set in the IDENTITY and SECRET fields to match the name and password of the KMES user you specified in the FXCL EKM configuration file.
2
Run the following commands to add the credential to a high-privileged user, such as your own domain login in the [DOMAIN\login] format:
SQL
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
	ADD CREDENTIAL EkmCredential;
GO
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
	ADD CREDENTIAL EkmCredential;
GO
﻿
3
Run the following commands to create an asymmetric key stored inside the FXCL EKM provider:
SQL
USE master;  
GO
CREATE ASYMMETRIC KEY EkmAsym
	FROM PROVIDER FxclEkmProvider
	WITH
		ALGORITHM = RSA_1024,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO
USE master;  
GO
CREATE ASYMMETRIC KEY EkmAsym
	FROM PROVIDER FxclEkmProvider
	WITH
		ALGORITHM = RSA_1024,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO
﻿
4
Run the following commands to create a credential for the database engine to use:
SQL
CREATE CREDENTIAL EkmEngineCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
CREATE CREDENTIAL EkmEngineCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
﻿
Change the values set in the IDENTITY and SECRET fields to match the name and password of the KMES user you specified in the FXCL EKM configuration file.
5
Run the following commands to create a login that uses the asymmetric key stored inside the FXCL EKM provider:
SQL
CREATE LOGIN EkmLogin
    FROM ASYMMETRIC KEY EkmAsym;
GO
CREATE LOGIN EkmLogin
    FROM ASYMMETRIC KEY EkmAsym;
GO
﻿
6
Run the following commands to enable the login to use the database engine credential:
SQL
ALTER LOGIN EkmLogin
    ADD CREDENTIAL EkmEngineCredential;
GO
ALTER LOGIN EkmLogin
    ADD CREDENTIAL EkmEngineCredential;
GO
﻿
7
Run the following commands to create a new example database, add a table to it, and insert information into the table:
You cannot execute database encryption operations on master, model, tempdb, msdb, or resource databases.
SQL
CREATE DATABASE exampleDB;
GO
USE exampleDB;
GO
CREATE TABLE users (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(64),
	password VARCHAR(128),
	ssn VARCHAR(32)
);
GO
INSERT INTO users (id, name, password, ssn) VALUES (1, 'SomeGuy', 'blah', '000-00-0000'), (2, 'SomeGal', 'password', '000-00-0000'), (3, 'TestUser', 'test123', '000-00-0000');
GO
CREATE DATABASE exampleDB;
GO
USE exampleDB;
GO
CREATE TABLE users (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(64),
	password VARCHAR(128),
	ssn VARCHAR(32)
);
GO
INSERT INTO users (id, name, password, ssn) VALUES (1, 'SomeGuy', 'blah', '000-00-0000'), (2, 'SomeGal', 'password', '000-00-0000'), (3, 'TestUser', 'test123', '000-00-0000');
GO
﻿
8
Run the following commands to create a database encryption key for the exampleDB database:
SQL
USE exampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
	WITH
		ALGORITHM = AES_128
	ENCRYPTION BY SERVER ASYMMETRIC KEY EkmAsym;
GO
USE exampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
	WITH
		ALGORITHM = AES_128
	ENCRYPTION BY SERVER ASYMMETRIC KEY EkmAsym;
GO
﻿
9
Run the following commands to enable transparent data encryption on the exampledb database:
SQL
ALTER DATABASE exampleDB
	SET ENCRYPTION ON;
GO
ALTER DATABASE exampleDB
	SET ENCRYPTION ON;
GO
﻿
10
To check if data can be decrypted, restart SQL Server service with the KMES Series 3 offline, then run the following command. If it fails, TDE is configured correctly. If the KMES is online, the command should succeed.
SQL
USE exampleDB;
SELECT * FROM users;
USE exampleDB;
SELECT * FROM users;
﻿
You can view the asymmetric key that is created on the KMES and used for encrypting the Database Encryption Key (DEK) on the Key Management > Keys menu in the KMES application interface.
﻿
Updated 20 Feb 2024
Did this page help you?
Configure EKM in Microsoft SQL Server
MongoDB