Enable Transparent Data encryption on the Microsoft SQL Server

1min
All of the following commands need to be run inside a Query window in SQL Server Management Studio.
1
Create a credential for use by system administrators.
Text
CREATE CREDENTIAL EkmCredential
    WITH
 	   IDENTITY = 'crypto1',
 	   SECRET = 'safest'
    FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
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  that is specified in the FXCL EKM configuration file (i.e., config.json).
2
Add the credential to a highly privileged user, such as your own domain login.
Text
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
    ADD CREDENTIAL EkmCredential;
GO
ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
    ADD CREDENTIAL EkmCredential;
GO
﻿
3
Create an asymmetric key stored in  through the  EKM provider.
Text
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
Create a credential for the Database Engine to use.
Text
CREATE CREDENTIAL EkmEngineCredential
    WITH
	    IDENTITY = 'crypto1',
	    SECRET = 'safest'
    FOR CRYPTOGRAPHIC PROVIDER FxclEkmProvider;
GO
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 that is 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
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
ALTER LOGIN EkmLogin
   ADD CREDENTIAL EkmEngineCredential;
GO
﻿
7
Create a new example database, add a table to it, then insert information into the table.
Database encryption operations cannot be executed 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
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
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
ALTER DATABASE exampleDB
    SET ENCRYPTION ON;
GO
﻿
10
Check if 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;
USE exampleDB;
SELECT * FROM users;
﻿
﻿
Updated 09 Dec 2024
Did this page help you?
Configuring EKM on the Microsoft SQL Server
Appendix A: Back up and restore Microsoft SQL Server databases