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 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:
I remember when all we could say for SQL Server 2000 was “go purchase encryption software”. Now, at least, we have a few choices.
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.