Enabling TDE on SQL Server by using EKM

1min
This section shows how to enable transparent data encryption (TDE) in SQL Server to protect a database encryption key by using an asymmetric key stored on the  EKM/HSM module.
You must run all the following commands inside a Query window in SQL Server Management Studio.
1
To create a credential for system administrators to use, run the following command:
The values set in the IDENTITY and SECRET fields should be the name and password of the identity created on the  specified in the FXEKM configuration file (fxekm.cfg).
SQL
CREATE CREDENTIAL EkmCredential
	WITH
		IDENTITY = 'crypto1',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FuturexEkmProvider;
GO
CREATE CREDENTIAL EkmCredential
	WITH
		IDENTITY = 'crypto1',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FuturexEkmProvider;
GO
﻿
2
Add the credential to a high-privileged user, such as your domain login, in the format [DOMAIN\login].
SQL
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
	ADD CREDENTIAL EkmCredential;
GO
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
	ADD CREDENTIAL EkmCredential;
GO
﻿
3
Create an asymmetric key stored inside the  EKM provider.
SQL
USE master;  
GO
CREATE ASYMMETRIC KEY EkmAsym
	FROM PROVIDER FuturexEkmProvider
	WITH
		ALGORITHM = RSA_2048,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO
USE master;  
GO
CREATE ASYMMETRIC KEY EkmAsym
	FROM PROVIDER FuturexEkmProvider
	WITH
		ALGORITHM = RSA_2048,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO
﻿
4
Create a credential for the database engine to use.
The values set in the IDENTITY and SECRET fields should be the name and password of the identity created on the  specified in the FXEKM configuration file (fxekm.cfg).
SQL
CREATE CREDENTIAL EkmEngineCredential
	WITH
		IDENTITY = 'crypto1',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FuturexEkmProvider;
GO
CREATE CREDENTIAL EkmEngineCredential
	WITH
		IDENTITY = 'crypto1',
		SECRET = 'safest'
	FOR CRYPTOGRAPHIC PROVIDER FuturexEkmProvider;
GO
﻿
5
Create a login to use the asymmetric key stored inside the EKM provider.
SQL
CREATE LOGIN EkmLogin
    FROM ASYMMETRIC KEY EkmAsym;
GO
CREATE LOGIN EkmLogin
    FROM ASYMMETRIC KEY EkmAsym;
GO
﻿
6
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
Create a new example database, add a new table, 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
Create a database encryption key for the exampleDB database.
SQL
USE exampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
	WITH
		ALGORITHM = AES_256
	ENCRYPTION BY SERVER ASYMMETRIC KEY EkmAsym;
GO
USE exampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
	WITH
		ALGORITHM = AES_256
	ENCRYPTION BY SERVER ASYMMETRIC KEY EkmAsym;
GO
﻿
9
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 verify that you can decrypt data, restart SQL Server service with the HSM offline and run the following command:
SQL
USE exampleDB;
SELECT * FROM users;
USE exampleDB;
SELECT * FROM users;
﻿
If the following command fails, then TDE is set up correctly. 
If the HSM is online, the command should succeed.
﻿
﻿
Updated 26 Aug 2024
Did this page help you?
Configuring EKM on the SQL Server
Oracle Database TDE (12c)