Skip to main content
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 = '<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.
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
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_2048,
		PROVIDER_KEY_NAME = 'EkmAsym';
GO
4
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.
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
6
Run the following commands to enable the login to use the database engine credential:
Sql
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
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
9
Run the following commands to enable transparent data encryption on the exampledb database:
Sql
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;
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.