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: