Enable Transparent Data encryption on the Microsoft SQL Server

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:

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).

Add the credential to a highly privileged user, such as your domain login.

Text

ALTER LOGIN [WIN-Q4E6RG9BSOL\Administrator]
    ADD CREDENTIAL EkmCredential;
GO

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

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).

Create a login that uses the asymmetric key stored inside the FXCL EKM provider.

Text

CREATE LOGIN EkmLogin
   FROM ASYMMETRIC KEY EkmAsym;
GO

Enable the login to use the database engine credentials.

Text

ALTER LOGIN EkmLogin
   ADD CREDENTIAL EkmEngineCredential;
GO

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

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

Enable transparent data encryption on the exampleDB database.

Text

ALTER DATABASE exampleDB
    SET ENCRYPTION ON;
GO

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;