Encrypt your MSSQL database with TDE and SafeNet KeySecure, and why!

One of the easiest way to encrypt a MSSQL (or Oracle) database is to use TDE - Transparent Data Encryption. TDE requires the higher end enterprise MSSQL license and requires a DBA to execute SQL commands.

Why should you encrypt your database? If a hacker gets into your network, he may be able to steal a copy of the database or parts of the database. If the data is confidential or under HIPAA, SOX, CJIS or one of the many regulations out there, it can become quite a headache.

One of the benefit of TDE is that the application querying the database does not need to be aware the database encryption: it is transparent to the application. If you have an existing application and database, you can enable TDE on the database without downtime and without changing the code of the application.

But there is catch! By default, MSSQL (or Oracle) stores the encryption key in software on the same machine, so it is not protected and not physically separated from the data. Again, if a hacker has access to you network and access to the data, he will have access to the key next to it so he can just decrypt the data. It is basically like leaving the key on the door of your car. Might as well not lock it!

SafeNet KeySecure solves this issue by keeping a Key Encryption Key outside of the database. In the video below, I walk you through the step of encrypting a SQL database with KeySecure. We look at the MDF and backup files in a text editor before and after encryption to prove the data is being encrypted. We also look at how the access to key in KeySecure is being logged.

/****************************************************/
/* Dummy database */
/****************************************************/
USE master;

CREATE DATABASE SampleDBwithPII;
GO

USE SampleDBwithPII;

Create table Customers (Id int not null, Name varchar(max) not null, Address varchar(max) not null, SSN varchar(max) not null);
GO

INSERT INTO Customers values (2, 'Matt Buchner', 'Arboretum Plaza II, 9442 Capitol of Texas Hwy, 78759 Austin TX', '111-222-3333');



/****************************************************/
/* PREP FOR TDE */
/****************************************************/


/*  enable EKM - Extensible Key Management
 you must be a sysadmin
*/

USE master;

GO 

sp_configure 'show advanced options', 1;
RECONFIGURE;

GO 

sp_configure 'EKM provider enabled', 1;
RECONFIGURE;

GO 


/* Load KS EKM - must be a sysadmin */
/* After running this command, check Security\Cryptographic Providers */
CREATE CRYPTOGRAPHIC PROVIDER safenetSQLEKM
FROM FILE = 'C:\Program Files\Safenet\SQLEKM\safenetsqlekm.dll'

GO 

/* The credentials below should match the credential in KS */
/* After running this command, check Security\Credentials */
CREATE CREDENTIAL EKMCred WITH IDENTITY='tdeuser', SECRET='P@ssw0rd'
FOR CRYPTOGRAPHIC PROVIDER safenetSQLEKM

GO 

ALTER LOGIN sa ADD CREDENTIAL EKMCred
/* example with Windows credentials
ALTER LOGIN [GTOLAB\mbuchner] ADD CREDENTIAL EKMCred;
*/

GO 

/* create a key in KS and create a reference to it in MSSQL */
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider safenetSQLEKM
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'MSSQL_TDE_EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW

GO 

/* reuse the existing key in other cluster nodes 
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider safenetSQLEKM
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'MSSQL_TDE_EKM_RSA_2048_Key',
CREATION_DISPOSITION=OPEN_EXISTING
*/


/* check the keys have been created */
Select * from [master].[sys].[asymmetric_keys]



/****************************************************/
/* HOW TO CONFIGURE TDE */
/****************************************************/
USE master;

GO 

CREATE CREDENTIAL EKMCredTDE
WITH IDENTITY = 'tdeuser',
SECRET = 'P@ssw0rd'
FOR CRYPTOGRAPHIC PROVIDER safenetSQLEKM ;

CREATE LOGIN tde_login
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key ;
GO

ALTER LOGIN tde_login
ADD CREDENTIAL EKMCredTDE;
GO


/* connect to our database */
USE SampleDBwithPII ;
GO

/* create symmetric encryption key */
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key ;
GO

/* enable encryption */
ALTER DATABASE SampleDBwithPII
SET ENCRYPTION ON ;
GO

/* query encryption state */
SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state,
CASE e.encryption_state
 WHEN 0 THEN 'No database encryption key present, no encryption'
 WHEN 1 THEN 'Unencrypted'
 WHEN 2 THEN 'Encryption in progress'
 WHEN 3 THEN 'Encrypted'
 WHEN 4 THEN 'Key change in progress'
 WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc, c.name, e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint