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 

   

 

Column level encryption in SQL Server 2005

A. Simple symmetric encryption

USE AdventureWorks;
GO

–If there is no master key, create one now.
IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = ‘23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj’
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = ‘Employee Social Security Numbers’;
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

— Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
GO

— Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

— Encrypt the value in column NationalIDNumber with symmetric
— key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID(‘SSN_Key_01’), NationalIDNumber);
GO

— Verify the encryption.
— First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

— Now list the original ID, the encrypted ID, and the
— decrypted ciphertext. If the decryption worked, the original
— and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS ‘Encrypted ID Number’,
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS ‘Decrypted ID Number’
    FROM HumanResources.Employee;
GO

B. Symmetric encryption that includes an authenticator

USE AdventureWorks;
–If there is no master key, create one now.
IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = ‘23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj’
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = ‘Customer Credit Card Numbers’;
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

— Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
    ADD CardNumber_Encrypted varbinary(128);
GO

— Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

— Encrypt the value in column CardNumber using the
— symmetric key CreditCards_Key11.
— Save the result in column CardNumber_Encrypted. 
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID(‘CreditCards_Key11’)
    , CardNumber, 1, HashBytes(‘SHA1’, CONVERT( varbinary
    , CreditCardID)));
GO

— Verify the encryption.
— First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

— Now list the original card number, the encrypted card number,
— and the decrypted ciphertext. If the decryption worked,
— the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted
    AS ‘Encrypted card number’, CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 ,
    HashBytes(‘SHA1’, CONVERT(varbinary, CreditCardID))))
    AS ‘Decrypted card number’ FROM Sales.CreditCard;
GO

 

Source : http://msdn.microsoft.com/en-us/library/ms179331.aspx

 

 

 

Encrypt SQL script on Production Server — Protecting Database

Note : Please save your original script before encrypting it .

CREATE  

 

PROCEDURE  encryptTest WITH ENCRYPTION AS

BEGIN  

 

 

SELECT   ‘Test’— comment: sp_password END

exec

 

encryptTest

— result is : foo

exec 

 

sp_helptext ‘encryptTest’— result is :The text for object ‘encryptTest’ is encrypted.

— Points to be noted before encrypting ant script

— (1) make sure you keep the logic of the stored procedure in a safe place,

— since you won’t have easy access to the procedure’s code once you’ve saved it

— (2) there are at least two ways to defeat this mechanism. One is to run SQL Profiler while executing

— the stored procedure; this often can reveal the text of the procedure itself, depending on what the

— stored procedure does (e.g. if it has GO batches, dynamic SQL etc). If they miss the initial install,

— the user can delete the stored procedures or drop the database, start a Profiler trace, and ask you to

— re-create them (in which case they will capture the CREATE PROCEDURE statements). You can prevent Profiler

— from revealing the text to snoopers by embedding sp_password in the code, as a comment:

— (3) These encrypted scripts will not be in updated in the process of replication