How encryption felt in SQL 2005
Hierarchies. Asymmetric keys. Symmetric keys. Certificates. Algorithms. Authenticators. Encrypting your data can seem like a very daunting and confusing process. On the one hand, you want your data to be secure. But on the other hand, you still want good performance. And, no pressure, but fail to plan properly and your data becomes completely unreadable. Makes you want to jump right in, doesn’t it? Well take a breath. Relax. It’s not that bad. And depending on your needs and wants, you’ve got some options.
The Dark Ages
Encryption in SQL Server as first introduced in SQL 2005. It was implemented at the column level, and it required that the column be defined as varbinary, and searching on encrypted columns was limited. Add to that the fact that the developer/application had to handle the encryption and decryption of data via stored procedures and, well, you’ve got yourself quite the little nightmare. (Not quite on the nightmare scale of bubonic plague, but definitely on par with medicinal leeches.) This type of encryption definitely has its place; if you’re developing an app with limited sensitive data, for example. But for wholesale data encryption, it’s not the best option.
The Renaissance
SQL 2008 brought database encryption out of the dark ages with Transparent Data Encryption (TDE). With TDE, you can encrypt your entire database without having to make any object changes, without any code changes, and without any loss of functionality. Hence the whole “transparent” thing. And it’s relatively easy to implement.
Start by creating a master key. This key is used to protect private keys of certificates as well as asymmetric keys in the database.
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperStr0ngP@55w0rd!?';
The next step is to create a certificate that will protect the Database Encryption Key (DEK). The DEK is used to encrypt the data, so it’s kinda important that nothing happens to this certificate. Therefore, you want to make sure you back it up to a safe place.
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE DEK Certificate';
BACKUP CERTIFICATE TDECert TO FILE = 'D:securelocatonTDECert.cer'
WITH PRIVATE KEY (
FILE = 'D:securelocatonTDEPrivateKey',
ENCRYPTION BY PASSWORD = 'SuperStr0ngP@55w0rd!?');
Now we need to create the DEK. Again, this tells SQL Server how to encrypt (and decrypt) the database.
USE AdventureWorks2008R2; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert
Note the ALGORITHM clause. SQL Server lets you choose from a variety of encryption algorithms, depending on your needs. For more information on what algorithms are available go here.
The final step in the process is to turn on encryption in the database.
ALTER DATABASE AdventureWorks2008R2 SET ENCRYPTION ON; GO
Once this command is executed, SQL Server starts a background process that scans through the database and encrypts the data. All this can go on while normal database activity takes place. Just don’t try to alter any datafiles while the encryption process is running. You can monitor its progress by querying sys.dm_database_encryption_keys.
SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys
Some things to keep in mind:
- Filestream data is not encrypted
- Database backups are also encrypted. Read that again, it’s important. When you restore an encrypted database, the certificate used to protect the DEK at time of the backup also needs to be present. Backup your certificates with your database!
- Data pages are decrypted when they are read from disk, and encrypted before being written. Therefore, data in memory is not encrypted. Data paged out to disk is also unencrypted.
- When a user database is encrypted using TDE, tempdb is also encrypted.
As you can see, TDE isn’t too scary. It’s definitely a step above the previous alternative. If you want more information on TDE or encryption in general, be sure to check out these resources: