Securing Data with Transparent Data Encryption

Securing sensitive data is a critical concern for organizations of all types and sizes. Credit card numbers, medical and health records, and other personal information must be stored and secured in such a way that only authorized personnel is able to access the information. In this blog post you will get an overview of the possibilities, availability, pros and cons, and evolution of Transparent Data Encryption (TDE). I hope this answers (most of) your questions.

What is TDE?
Transparent Data Encryption (TDE) protects your data at rest by performing real-time I/O encryption and decryption of SQL Server database data and log files. One of the biggest benefits of TDE is that the SQL Server engine handles all of the encryption and decryption work.

TDE uses the AES and 3DES encryption algorithms, and the encryption and decryption operations run on background threads by SQL Server. No application changes are required to take advantage of TDE. Backups of databases protected by TDE are also encrypted.

TDE does not prevent admins from seeing data. To protect data from users who access the database, you need cell-level encryption. TDE will also not protect you against SQL injection attacks. TDE encrypts the data stored on disk at the page and log level. The data that’s sent across the wire to the client connections isn’t encrypted. If you want to encrypt the link between SQL Server and the client systems, then you need to use Secure Sockets Layer (SSL). Another way to encrypt data at all levels (also in memory and on your network is by using the “Always Encrypted” starting from SQLServer 2016 (Column level).

Is there a difference with using BitLocker?
BitLocker Drive Encryption is a data protection feature available in Windows Server 2008 R2 and in some editions of Windows 7. Having BitLocker integrated with the operating system, addresses the threats of data theft or exposure from lost, stolen, or inappropriately decommissioned computers. BitLocker helps mitigate unauthorized data access by enhancing file and system protections.

Differences
• If you use TDE, backups will also be encrypted, as will any copy you restore to test/QA/dev. This means that you will need to make sure that the encryption key is available on the environment you restore the database.
• Bit locker encrypts everything (on the volume), TDE only encrypts the database files that you select. If you are encrypting and decrypting less stuff, it should be faster. It also gives more flexibility to only encrypt 1 or 2 of the most important databases on a server while sharing the same volume(s) with other DB’s.
• BitLocker doesn’t fully satisfy the Payment Card Industry Data Security Standard (PCI DSS) requirement (specifically, 3.4 and 3.5.2) for data encryption at rest, TDE does fulfill that requirement.

TDE availibility
TDE is available in the following versions/editions
• SQL Server 2008/2008R2 (Datacenter and Enterprise Edition)
• SQL Server 2012 (Enterprise Edition)
• SQL Server 2014 (Enterprise Edition)
• SQL Server 2016 (Enterprise Edition)

How to set up TDE up?
“Traditional” Encryption Key Hierarchy
1. Create a master key using the CREATE MASTER KEY T-SQL statement.
2. Create a certificate using the CREATE CERTIFICATE command.
3. Use the CREATE DATABASE ENCRYPTION KEY statement to encrypt the Database Encryption Key (DEK).
4. Use the ALTERDATABASE SET ENCRYPTION ON statement to actually encrypt the user database with the DEK. As soon as you use TDE for a user database, TempDB is automatically encrypted as well.

Extensible Key Management (EKM) with “Azure Key Vault” (AKV)
Note: EKM is only available on Enterprise Edition!
1. Create an Azure Key Vault (AKV + AAD application)
All detailed steps can be found here.
In short the list is:
a. Install Azure Powershell.
b. Create an Azure Active Directory (AAD).
c. Register an application with this ADD, this gives you a Service Principal account (spName) that has access to your Key Vault. A sample application is available from the Microsoft Download Center. After the application is added you can find the ClientId on the Configure Tab.
d. Create the Key Vault: Use the New-AzureRmKeyVault cmdlet to create a key vault. This cmdlet has three mandatory parameters: a resource group name, a key vault name, and the geographic.
e. Add a key or secret to the key vault.

2. Configure AKV Integration (Mostly Powershell)
a. Install the SQL Server IaaS Extension on your Azure VM, which can be done by Powershell, should be visible in the Administration of your VM > Extensions.
b. Create an asymmetric key (the DMK) and store it in the hardware portion of the key vault.
c. Install a small piece of connector software on each SQL server, allowing it to interface with the AKK. Download this.
d. Run SQL scripts on each SQL instance to create a cryptographic provider, create credentials, and connect to the AKV.
e. Run some SQL scripts on each database to create DEKs and enable TDE.

3. AKV Pricing
Azure Key Vault, like most cloud applications has a fee to use this service. Both storage and operation which have their specific price.

A graphical overview of the difference between both:

Is there a performance impact?
Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 10 percent, depending on the type of workload. SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

Can I do backup compression on TDE databases?
Before SQL Server 2016, you had to choose between TDE or backup compression (although the option is available, and you can put it on, it’s not really compressed when using TDE). Since SQL Server 2016 you can have both!

Egon Vandenrijdt is Senior SQL Developer bij Kohera in de Azure Architecture and Data Technology Group.