If it was not clear by my recent post Auditing SQL Server, I have compliance and security on my mind lately. This post is no different.
While SQL Server has had encryption for some time, implementation takes significant planning and can require modification of application code. With SQL Server 2008, Microsoft introduced Transparent Data Encryption (TDE). This feature allows for encryption of the actual data and log files and any subsequent backups of those files. TDE avoids the need for application modification by performing the encryption and decryption in real time and at the database level.
Note: TDE is an Enterprise Only feature
Before covering how to enable TDE and encrypt the database, it needs to be noted that the certificate used to encrypt the database needs to be backed up and available in the event a recovery is necessary. This needs to be included in DR planning before TDE is enabled.
The first step toward TDE is creation and subsequent backup of the Master Key.
USE masterGO--Create the Master Key EncryptionCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyP@$$w0rd'GO--Backup the Master KeyBACKUP MASTER KEY TO FILE = N'C:SQLCertBackupMasterKey' ENCRYPTION BY PASSWORD = 'MyP@$$w0rd'GO
Once the Master Key has been created, the Server Certificate can be created. This certificate is stored in the master database.
USE masterGO--Create the Server CertifiacateCREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Certificate'GO--Backup the certificateBACKUP CERTIFICATE MyServerCert TO FILE = N'C:SQLCertBackupMyServerCert'GO
With the Master Key and the Certificate created, the database is ready to be secured through the creation of the database encryption key. This key is secured by the certificate created above.
--Create the Database Encryption KeyUSE AdventureWorks2012GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCertGO
The final step is to actually enable encryption of the database.
--Enable encryption on the databaseALTER DATABASE AdventureWorks2012SET ENCRYPTION ONGO
TDE and Performance
Understanding that TDE works by encrypting and decrypting in realtime naturally leads to questions regarding performance. Microsoft BOL claims that it will only increase CPU utilization by 3-5%. Some independent testing by Rob Garrison seems to support Microsoft’s claim. You can check out his testing here.
Additional items to consider when implementing TDE:
- During the initial implementation of TDE, the entire database and log files will need to be encrypted. How long this will take is dependent upon many factors including size of the database and server specifications.
- Enabling TDE on an instance will encrypt the tempdb for the entire instance.
- Backup compression will not be as effective because encrypted data does not compress well.
- FILESTREAM data is not encrypted using TDE.
TDE gives the DBA a way to secure the database without having to alter applications connecting to it. It is important to understand the effects before implementing. If applications connecting to the database already cause a heavy load on the CPU it is important to test how TDE will effect performance.
If you are using TDE in production, I would love to hear about your experience in the comments below.