Transparent Data Encryption in SQL Server 2008

Hello Folks 

After availability of SQL Server 2008, if we talk about the security/encryption of our data , than we are very much available with the following options : 

(1) Native Data Encryption (Available in SQL Server 2005 and 2008)  –> This is column level encryption. 

(2) Transparent Data Encryption (TDE ) (Available in SQL Server 2008 ) — >  This is database file level encryption. 

(3) File Level encryption (Provided by Windows ) 

TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and BitLocker™ Drive Encryption. 

I will not be going in more details here in this post. At the end of this post you can find the reference links for further details and explanations . 

To implement the TDE  follow the below mentioned steps : 

To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database. 

  

 

 

 To enable TDE 

Perform the following steps in the master database: 

1. If it does not already exist, create a database master-key (DMK) for the master database. Ensure that the database master-key is encrypted by the service master-key (SMK). 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’; 

 2. Either create or designate an existing certificate for use as the database encryption key (DEK) protector. For the best security, it is recommended that you create a new certificate whose only function is to protect the DEK. Ensure that this certificate is protected by the DMK. 

CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’; 

3. Create a backup of the certificate with the private key and store it in a secure location. (Note that the private key is stored in a separate file—be sure to keep both files). Be sure to maintain backups of the certificate as data loss may occur otherwise. 

BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’ 

WITH PRIVATE KEY (FILE = ‘path_to_private_key_file’,ENCRYPTION BY PASSWORD = ‘cert password’); 

4. Optionally, enable SSL on the server to protect data in transit. 

Perform the following steps in the user database. These require CONTROL permissions on the database. 

5. Create the database encryption key (DEK) encrypted with the certificate designated from step 2 above. This certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database. 

CREATE DATABASE ENCRYPTION KEY   WITH ALGORITHM = AES_256   ENCRYPTION BY SERVER CERTIFICATE tdeCert 

6. Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously. 

ALTER DATABASE myDatabase SET ENCRYPTION ON 

To monitor progress, query the sys.dm_database_encryption_keys view (the VIEW SERVER STATE permission is required) as in the following example: 

SELECT db_name(database_id), encryption_state   FROM sys.dm_database_encryption_keys 

   

For more information: 

http://technet.microsoft.com/en-us/sqlserver/default.aspx 

http://msdn2.microsoft.com/en-us/sqlserver/default.aspx 

   

 

Advertisements

One thought on “Transparent Data Encryption in SQL Server 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s