BANGALORE, INDIA: Securing a database is always a top priority for a database professional, whatever capacity he might be working in, a database developer, administrator, designer or architect. Each new version coming from all database vendors adds more security to a product, and Microsoft SQL Server is no different.

MS SQL Server 2005 provided you with out-of-the-box features to encrypt data using certificates, symmetric key encryption and asymmetric key encryption. But that was at the cell level, which means, you could encrypt one column in a row before you store it in the database, and decrypt the information before you use it.

SQL Server 2008 introduces a new feature, Transparent Data Encryption (TDE), which lets you encrypt the complete database. Securing a database is a vast and complex topic, so let me restrict this discussion to showing how to use TDE in databases.

USE master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyStrongPassword';
GO

Then you create a certificate that is protected by the master key.

CREATE CERTIFICATE payrollCert
WITH SUBJECT='TDE for payroll';
GO

Next you create the DEK and secure it using the certificate.

USE payroll
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE payrollCert;
GO

Finally you enable the encryption.

ALTER DATABASE payroll
SET ENCRYPTION ON;
GO

You can still access the data as before, and TDE is transparent to the client.