Geeks With Blogs
SharePoint & SQL Thoughts

Some literature first

If you are not, you should start considering database encryption for your organization as data is the only reason you are in business. If and when it becomes compromised bad things are bound to happen.

When TDE is enabled or disabled for that matter a background process is initiated by the server that encrypts all the database files.

This is an Enterprise or Developer version(s) only feature.

How it works

DDL is initiated (SELECT something FROM SOMEWHERE WHERE otherthing = 'athing') and update lock is taken on the database causes an encryption scan, by the way this is asynchronous to DDL any operation that do not conflict with the update and shared lock proceeds without interruption excluding the database detach and file structure modification.

After the scan is complete DEK is set to encrypted and all the files on disk, the database and the log files writes to the disk will be encrypted.

What's encrypted?

Files on disk will be encrypted the *.mdf, *.ndf, snapshots, backups, "*.ldf” and TempDB is by default encrypted when encryption is enabled on user databases.

Transaction logs by design are write-once fail safe hence a challenge for TDE thus not everything is encrypted on them.

What's not encrypted?

Data that is in memory (data in use) is not encrypted TDE does not offer in memory encryption (Consider Cell-Level encryption if this is a requirement).

Data in transit is not encrypted either (consider using SSL if this is a requirement)

Database page files (written to disk), the headers are also not encrypted because they may be used for reload.

System databases are currently not encrypted by TDE.

Impact on databases

TDE as the name suggests tries to make the encryption as transparent as possible, no application changes and user experience is meant to remain the same as non-encrypted database.

Being a database level process is what makes it possible to perform as a non-encrypted database because the database can still leverage the usual database optimizations.

Encryption is CPU intensive hence generating high I/O. Consider your I/O impact prior to enabling encryption. It’s usually estimated to be about 30% increase in CPU usage.

During the initial encryption scan process, performance is sharply impacted thus consider enabling the process during light usage period in your environment.

There is no padding on database files on disk though transaction logs are padded.

User permissions are not impacted.

Since it’s an Enterprise/Developer versions only feature, encrypted databases cannot be used in other versions.

Impact on Backups

Don’t loose the key, I repeat don’t loose the key else you are doomed... enuf sed.

See Create a backup of the DEK on making it work

Making it work

--- Check if encryption exists

SELECT name, is_encrypted FROM SYS.DATABASES

--- Check if certificate exists

SELECT name, is_encrypted FROM MASTER.SYS.CERTIFICATES WHERE pvt_key_encryption_type = 'MK' AND NAME LIKE '%TDE'

--- Create an encryption key DMK database master key

--- Ensure that the DMK is encrypted by SMK

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'

--- Create a certificate to be used by DEK database encryption key 

CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate';

--- Create a backup of the DEK this is the one that goes under the bed you lose it you lose your data

--- I've always preferred UNC path's ensure that you have appropriate permissions defined.

BACKUP CERTIFICATE tdeCert TO FILE = '\\myuncpath\filename.certbak'

   WITH PRIVATE KEY (

         FILE = '\\myuncpath\filename.pkbak',

         ENCRYPTION BY PASSWORD = 'strong cert password');

--- If data is transmitted beyond your server, consider SSL'ing your box.

--- Create your DEK, ensure that you pick the appropriate algorithm at the time of publishing this article AES-256 (3 Key Triple DES) was the highest encryption per NIST and NSA.

--- This step here is necessary to set the USE database else you will get the error "Cannot change database encryption state because no database encryption key is set."

USE <MyDatabase>

GO

CREATE DATABASE ENCRYPTION KEY

   WITH ALGORITHM = AES_256

   ENCRYPTION BY SERVER CERTIFICATE tdeCert

--- Enable your TDE on your database

ALTER DATABASE myDatabase SET ENCRYPTION ON

--- Monitor progress 

SELECT db_name(database_id), encryption_state

FROM sys.dm_database_encryption_keys

/******** if you already have an existing key that needs to be transferred to a different server ******************/

-- This is necessary if you will be restoring your database in a different SQL Server.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'

--- Create a certificate generated from the path where you stored the backup certificate

CREATE CERTIFICATE tdeCert FROM FILE = 'path_to_file'     

WITH PRIVATE KEY (

               FILE = 'path_to_private_key_file',

         DECRYPTION BY PASSWORD = 'strong cert password')

Posted on Tuesday, April 16, 2013 11:05 AM | Back to top


Comments on this post: Database Encryption - Transparent Data Encryption (TDE) - how to

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net