>SQL 2008 Transparent Data Encryption (TDE)

6/25/2010

>The need to protect data from unauthorized viewing and use has been an issue for decades. We protect data in transit using IPSEC. We have the ability to protect a directory and associated files through the use of the Encrypting File System (EFS). The release of Windows Vista and Windows 7 allows for the use of BitLocker Drive Encryption. SQL 2005 introduced the capability to encrypt individual columns of data; the arrival of SQL 2008 Enterprise Edition however; now allows for the encryption of an entire database within the scope of a SQL instance.

Various regulatory standards bodies require differing types of data to be handled securely. Examples of these bodies include Sarbanes-Oxley, Health Insurance Portability and Accountability Act, (HIPAA), Payment Card Industry Security Standards Council, (PCI DSS) among others. As mentioned earlier, SQL 2005 allows for encrypting columns of data, but this requires a change to the underlying schema of the database, a change to the applications that access the data and the loss of the capability to sort or query the data while it is encrypted. This is where SQL 2008 TDE shines.

TDE allows for the encryption of data at rest. This means that the database is always encrypted and this capability does not require changes to applications nor do you lose the ability to query or sort the data. It is invoked on a database by database level and is totally transparent to the end user. When SQL Server is installed a Service Master Key is created for that instance of SQL. This is a symmetric key that can be seen by starting:

SQL Server Management Studio, (SSMS) >> opening Object Explorer, drill into the instance of SQL Server >> Databases >> System Databases >> Master >> Security >> Symmetric Keys.

You will see an object labeled ##MS_ServiceMasterKey##, this is the symmetric key created automatically when SQL Server was installed. This is the key which is used to create the database master key used by TDE for the encryption of the database. You can also view this key by connection to the master database and querying a system catalog view:

SELECT * FROM sys.symmetric_keys

You will then create the database master key and the associated certificate. The database master key does not create an object in Object Explorer.

Examples of the syntax to create the key and certificate are:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Password’
GO
CREATE CERTIFICATE GetDataCert
AUTHORIZATION Somebody
WITH SUBJECT ‘Data Certificate’
GO
CREATE SYMMETRIC KEY GetDataKey
AUTHORIZATION Somebody
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE GetDataCert
GO

After creation, the presence of this key can be viewed in Object Explorer

SQL Server Management Studio, (SSMS) >> opening Object Explorer, drill into the instance of SQL Server >> Databases >> System Databases >> Name of Database >> Security >> Symmetric Keys.

Or by connecting to the user database and running this query:

SELECT * FROM sys.symmetric_keys

You can determine if a database is protected with TDE through the use of the following query:

SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

When accessing data which is typically done through a stored procedure, you would start it in the following way:

EXECUTE AS USER = ‘Somebody’
OPEN SYMMETRIC KEY GetDataKey DECRYPTION BY CERTIFICATE GetDataCert

REVERT

The wonderful thing about TDE is that the data is always encrypted while at rest, this includes any backups that are made of the database. Other than invoking TDE, there are no required changes to existing applications, stored procedures, views, user defined functions, etc. Many in IT expect that encryption of data at rest will become a regulatory requirement in the days ahead. SQL Server 2008 Enterprise Edition Transparent Data Encryption positions you squarely to meet these upcoming requirements. LRS offers courses, MS6158 and MS6231 which provide in-depth coverage of this topic.