Worried about Data at Rest? Try TDE…

SQL Server 2008 introduced a great new feature called TDE, Transparent Data Encryption. It allowed a database to be completely encrypted without having to change the applications that access it. It is referred to as encryption for “data at rest”. But what about data in motion? That’s where column-level encryption pays off to provide end-to-end encryption.

With TDE, each data page is encrypted when is written to disk and is decrypted when read from disk. This provides extra physical level security so that if a disk drive falls into the wrong hands the data is protected by strong encryption. This also applies to backup tapes since technically the backup is a copy of the encrypted data pages from disk. The master key and associated certificate are backed up separately providing an extra level of security. A major trade-off is that the TempDB is also encrypted even if only one database has TDE switched on. This has performance implications. Because the application does not have to change, this is ideal for package databases provided by third-parties.

Column-level (or “cell-level”) encryption was introduced with SQL Server 2005. We can encrypt individual columns that are sensitive in nature. The trade-off here is that we need to change our database design as the data type we use needs to be varbinary. Also the application has to be changed to use a symmetric key, a certificate and the new functions EncryptByKey and DecryptByKey. There’s also extra administration to allow security for the symmetric keys/certificates and performance again will take a hit. However, when encrypted the columns are encrypted on disk, in memory, across the network, everywhere, until we choose to decrypt the data in the application.

Database Encryption in SQL Server 2008:

http://msdn.microsoft.com/en-us/library/cc278098(SQL.100).aspx

I remember when all we could say for SQL Server 2000 was “go purchase encryption software”. Now, at least, we have a few choices.

Author: Brian Egler

Related Courses

Developing and Implementing a SQL Server 2008 Database (M6232)

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services (M6236)

SQL Server 2005 Administration (M2780)

SQL Server 2008 for Administration (M6231, M6232)

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)

In this article

Join the Conversation