Skip to main content
All of the following commands need to be run inside a Query window in SQL Server Management Studio.
Perform the following steps to enable Transparent Data encryption on the Microsoft SQL Server:
1
Create a credential for use by system administrators.
Text
CREATE CREDENTIAL EkmCredential
    WITH
 	   IDENTITY = 'crypto1',
 	   SECRET = 'safest'
    FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
The values set in the IDENTITY and SECRET fields should be the name and password of the identity created on the CryptoHub that is specified in the FXCL EKM configuration file (such as config.json).
2
Add the credential to a highly privileged user, such as your domain login.
Text
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
    ADD CREDENTIAL EkmCredential;
GO
3
Create an asymmetric key stored in CryptoHub through the Futurex EKM provider.
Text
USE master;  
GO
CREATE ASYMMETRIC KEY EkmAsym
    FROM PROVIDER FxclEkmProvider
    WITH
	    ALGORITHM = RSA_1024,
	    PROVIDER_KEY_NAME = 'EkmAsym';
GO
4
Create a credential for the Database Engine to use.
Text
CREATE CREDENTIAL EkmEngineCredential
    WITH
	    IDENTITY = 'crypto1',
	    SECRET = 'safest'
    FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
The values set in the IDENTITY and SECRET fields should be the name and password of the identity created on the CryptoHub specified in the FXCL EKM configuration file (such as config.json).
5
Create a login that uses the asymmetric key stored inside the FXCL EKM provider.
Text
CREATE LOGIN EkmLogin
   FROM ASYMMETRIC KEY EkmAsym;
GO
6
Enable the login to use the database engine credentials.
Text
ALTER LOGIN EkmLogin
   ADD CREDENTIAL EkmEngineCredential;
GO
7
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.
Text
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.
Text
USE exampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
    WITH
	    ALGORITHM = AES_128
    ENCRYPTION BY SERVER ASYMMETRIC KEY EkmAsym;
GO
9
Enable transparent data encryption on the exampleDB database.
Text
ALTER DATABASE exampleDB
    SET ENCRYPTION ON;
GO
10
Check whether the data can be decrypted.
If possible, restart SQL Server service with the CryptoHub offline, then check if the following command fails. If it does, then TDE is set up correctly. If the CryptoHub is online, the command should succeed.
Text
USE exampleDB;
SELECT * FROM users;