Transparent Data Encryption (TDE) : Encrypting Data at Rest

Vineet Batta here…..I am senior software development engineer in Information Security Tools team at Microsoft. Today I am going to share my experiences on encrypting data at rest.  Most organizations want to secure their HBI (High Business Impact) data from un-authorized access including backup files (an example of data at rest). Recently we were required to provide a solution to this problem late into development phase and went looking for an option that involved zero code change and little or no impact on performance. Transparent Data Encryption or TDE came to our rescue and here is a overview.

Fact Sheet

What is it?
This is used  for encrypting data at rest. Examples: Database backups and detached database files (data and log files){aka *.mdf/*.ldf files}. This is only available in SQL Server 2008.

Any code change in application?
No

Does it require key and password management in secure way?
Yes

Three files will be generated two for password and one for certificate:
Example:

HBIcertificate.cert
HBICertPassword.key
HBIMasterPassword.key

Can I restore the database without these keys/passwords?
No. If the keys/password are lost all is lost. Ship Sinks.

Performance impact:
Documentation says 5% depending on environment and CPU used.

Does this protect when data in transit?
No. SSL is probably the way to go.

Can I fire my query in SSMS or from any application to view my data?
Yes, just as usual, as long you are Authenticated and Authorized.

Steps to configure this?

Below are steps outlined to encrypt the database (named HBI) here.

  --Step 1
-- Create the Master Key.
USE master;
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'mymasterkeypassword@123';


 --Step 2
-- Back up the Master key on file system and store securely.
GO
BACKUP MASTER KEY TO FILE = 'C:\SaveFiles\HBIMasterKey.key' 
    ENCRYPTION BY PASSWORD = 'mymasterkeypassword@123'
    


 -- Step 3
-- Create Certificate
GO
CREATE CERTIFICATE HBICertificate
WITH SUBJECT = 'Encrypt HBI Database'

 -- Step 4
-- Back up certificate
GO
BACKUP CERTIFICATE HBICertificate TO FILE = 'C:\SaveFiles\HBICertificate.cert'
   WITH PRIVATE KEY 
   (
         FILE = 'C:\SaveFiles\HBICertificatePassword.key',
         ENCRYPTION BY PASSWORD = 'mycertificatepassword@123'
   );

 --Step 5
-- Create ENCRYPTION key
GO
Use HBI;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE HBICertificate



 -- Step 6
-- Turn the encryption on
GO
ALTER DATABASE HBI
SET ENCRYPTION ON


 -- Step 7
-- Verify
GO
SELECT [name], is_encrypted FROM sys.databases
The record for HBI database should be 1 as below

Note: To store these keys securely is responsibility of the organization and should be stored in a different location than the data/backup files itself. Well get to key storage and crypto in some later posts.

If some one gets hold of backup file of HBI database (example: HBI.bak ) and try to restore it on another box with out these keys the user will get the following valid error message and you will still get good nights sleep!

We found it to be very simple and effective and would highly recommend it.

Vineets Tip of the day

To align your code perfectly select the code in Visual Studio (Ctrl A to select the complete file), then press Ctrl K+ Ctrl F. This will work for C++,c#, VB or HTML code in VS 2005 or VS 2008. It will save you lot of time and make your code readable & maintainable. Enjoy.

Stay tuned for more from me in coming weeks….