Friday, January 13, 2017

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

SafeNet Authentication Service video demos

Below are a couple of video demos which I made for my work as Sales Engineer at Gemalto.


  • Demonstration of the SAS integration with the Microsoft Remote Desktop Gateway and Remote Desktop WebAccess. The most interesting part is when we show how the 2nd factor authentication can be bypassed by clicking directly on a cached RDP file.


  • Demonstration of the SAS integration with Netscaler using SAML, where SAS is the IdP and Netscaler is the SP.


  • Demonstration of the integration of SAS with Salesforce.com. The first video shows the configuration, the 2nd video shows the authentication user experience.




  • Demonstration of the integration of SAS with Twillio Programmable Voice APIs. The authenticating user receives a call and Twillio plays the 6 digit code the user need to authenticate.


  • Demonstration of SAS integration with Linux PAM.




Thursday, July 10, 2014

How to SSH with your Smart Card

If you have Linux servers, you must be familiar with SSH - Secure SHell. It is common for administrators to use strong authentication because they have the control of the entire company network and resources.

You can configure tools such as PuttySC, PuttyCAC or SecureCRT to use smart card credentials to SSH to your servers.

I made this video to show how it works, enjoy!


To configure your server for smart card authentication:
1. Extract the public key out of the certificate in the card, I run the following command:
pprint.exe -l "c:\Program Files (x86)\Gemalto\DotNet PKCS11\gtop11dotnet.dll"
2. Add the public key to ~/.sshd/authorized_keys on the server, it looks like this:
ssh-rsa AAAAB3NzaC1yc2EAAAAFAAABAAEAAACBANnQe0X1Rl6QezigIXlfe4uzBtKkI083/oL3fl3vfQKdpdwwlwit3ODAOh2qpfs97r+OYUQPY66knNCW/u6hX2hiQk5DXeMR1HuZXQRxGKBxJZAftRXO3pD6b3pfH7djnfudGpg8UMHUBoWDUJ1UMh60K/+0QUqAyKT42vexh1Kj token-key


Saturday, June 28, 2014

Install and Configure Citrix XenApp 6.5 in 15 minutes

I recently had to build a Citrix XenApp lab and I decided to make this video to help others that are just getting started with this software.

The video shows how to install and configure step by step:
- Citrix License Server
- Citrix XenApp 6.5
- Citrix Web Interface

Finally we connect to the Citrix environment to access a virtualized application.


Sunday, October 16, 2011

Neutral or stability shoes? Hack into your running!


Two month ago, I sprained my ankle. Now that my ankle recovered, I feel comfortable running again. My shoes had about 200 miles on them already so I decided to buy a new pair. I went to Texas Running Company and I discovered a very geeky equipment there, and I could not resist to share it on this blog.

I am not an expert in running shoes business but from what I understand there are two types: neutral or stability. Depending on how you run, you will need one or the other. But how do you know which one is good for you? You could ask somebody to run behind you and look, or you can go the techie way. And of course, this is the way I like the best. :)

In the store, they had a camera positioned right behind a treadmill. We recorded 2 short runs (about 30 seconds for each run) and analyzed them. I was honestly very surprised by the results.

The two pictures below shows the two shoe types. On the left, I was trying neutral shoes; on the right it is stability shoes.


As you can see, my right ankle bends inward when my feet is landing with the neutral shoes. The technical term for it is actually overpronation.

Here is the video of the 2 runs.

Since this is a tech blog, I would like to share how I merge the 2 videos I got from the store. I used Avisynth and VirtualDub with the following script.


clip1=AVISource("run1.avi").AssumeFPS(5, false)
clip2=AVISource("run2.avi").AssumeFPS(5, false)
StackHorizontal(clip1,clip2)