Enable TDE in Microsoft SQL Server by using EKM

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.

Run the following commands to create a credential for system administrators to use:

Sql

CREATE CREDENTIAL EkmCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = '<kmes_identity_password>'
	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.

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

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_2048,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO

ppRun the following commands to create a credential for the database engine to use:

Sql

CREATE CREDENTIAL EkmEngineCredential
	WITH
		IDENTITY = 'SqlServer',
		SECRET = '<kmes_identity_password>'
	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.

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

Run the following commands to enable the login to use the database engine credential:

Sql

ALTER LOGIN EkmLogin
    ADD CREDENTIAL EkmEngineCredential;
GO

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

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

Run the following commands to enable transparent data encryption on the exampledb database:

Sql

ALTER DATABASE exampleDB
	SET ENCRYPTION ON;
GO

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;

You can view the asymmetric key that is created on the KMES Series 3 and used for encrypting the Database Encryption Key (DEK) on the Key Management > Keys menu in the KMES application interface.