Practical Steps of Configuring Transparent Data Encryption
(SQL Server 2008)
By : Kasim Wirama, MCITP, MCDBA
Transparent Data Encryption (TDE) is new feature of SQL Server 2008. I can say it is security feature. The feature will encrypt database files into encrypted format in SQL Server 2008 Enterprise or Developer edition. Books Online describes more detail about TDE. What I would focus on this posting is how to configure TDE.
The case is that you will encrypt AdventureWorks2008 OLTP database. So here is following steps to configure :
1. Open SQL Server Management Studio and open new query window.
2. Switch to master database context.
3. Create master key encrypted by password here :
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123’;
4. Create certificate. The certificate will be encrypted by master key.
CREATE CERTIFICATE mycert WITH SUBJECT = ‘Tde certificate’;
5. Backup certificate. The certificate is required for restoring database backup into other SQL Server instance.
BACKUP CERTIFICATE mycert TO FILE =’C:\mycert_bkup.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\mycert_pvt.cer’,
ENCRYPTION BY PASSWORD = ‘456’
);
6. Switch to AdventureWorks2008 database context
7. Create database encryption key.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE mycert;
8. Turn on encryption option on AdventureWorks2008
ALTER DATABASE AdventureWorks2008
SET ENCRYPTION ON;
To monitor progress of encryption process, you could query into new dynamic management view here:
SELECT DB_NAME(DATABASE_ID), ENCRYPTION_STATE FROM SYS.DM_DATABASE_ENCRYPTION_KEYS