Microsoft SQL Server customers ask us whether they should use Always Encrypted or Transparent Data Encryption (TDE) to protect sensitive data. It is a relevant question, especially given the concern for the GDPR, and it is relevant to many other compliance regulations and data protection needs. Let’s explore these technologies in more detail and I think the answer will emerge.

Always Encrypted

Always Encrypted is a relatively new, client-side implementation of encryption for SQL Server. That is, data is encrypted on the source system before you insert it into a SQL Server database. Implemented as a Windows driver, Always Encrypted intercepts your SQL statement before it leaves your client-side system (PC, web server, etc.), determines if there are any fields in the SQL statement that need to be encrypted, establishes or retrieves an encryption key, encrypts the field, and sends the modified SQL statement to SQL Server for processing.

One advantage of this approach is that the data is encrypted as it travels over the internal or external network, and as it resides in the database. A retrieval of the encrypted data reverses this process ensuring that the data is protected in transit.

One significant limitation of Always Encrypted is that it can only be applied to a small subset of SQL operations. Many SQL operations are complex and cannot be processed by Always Encrypted.

Transparent Data Encryption and Cell Level Encryption

SQL Server Transparent Data Encryption (TDE) and Cell Level Encryption (CLE) are server-side facilities that encrypt the entire SQL Server database at rest, or selected columns. The client-side application is completely unaware of the implementation of TDE or CLE and no software is installed on the client-side system. All of the encryption tasks are performed by the SQL Server database itself. It is easy to implement and performs very well for most SQL Server customers.

TDE and CLE are a part of the Microsoft SQL Server Extensible Key Management (EKM) strategy which has been a part of SQL Server since the 2008 edition. It is a mature technology and many organizations deploy TDE or CLE to protect their sensitive information.

Encryption Key Management

An encryption strategy is only as good as the encryption key management strategy. Creating, protecting, and using encryption keys is the hardest part of encryption, and for the sake of your overall security, it is important to get key management right.

With Transparent Data Encryption the key management strategy is well defined through the Microsoft Extensible Key Management (EKM) Provider interface. Key management systems such as our Alliance Key Manager for SQL Server provide software written to the EKM interface specification and SQL Server customers get full encryption key management integrated through the EKM Provider interface.

Always Encrypted does not provide a formal interface for key management. Instead, third party vendors must provide Always Encrypted drivers that implement an interface to a key management system. This means that the interface to the key management system is proprietary. Additionally, since the Always Encrypted implementation is a client-side deployment, each client-side application will have to have access to the key manager in order to properly protect and use encryption keys. This can be a challenge in distributed network topologies.

The bottom line: you can achieve proper key management with either approach, but expect to deal with more complexity with Always Encrypted when you have distributed clients.

When to Use Always Encrypted

Because Always Encrypted functions by modifying the SQL operation before it interfaces with the SQL Server database, and because many complex SQL operations will not work with Always Encrypted, I would only recommend using Always Encrypted when the application architecture is very simple. For example, you might want to use Always Encrypted to send data from an internal SQL Server database to a web-hosted SQL Server database and application. The data will be protected in transit and encrypted in the database. As long as your web application makes simple SQL queries to the database this approach can work well.

When to use TDE or CLE

Transparent Data Encryption and Cell Level Encryption are implemented directly in the SQL Server database itself. This means that complex SQL operations are fully supported and you will not find limitations imposed by your encryption or application strategy in the future. The SQL Server implementation of TDE and CLE also support a standardized interface for encryption key management. This means you will have choices in key management vendors and can readily find a solution.  TDE also has good performance metrics for the large majority of SQL Server customers, and is exceptionally easy to implement.

Summary

For the reasons outlined above I believe that Transparent Data Encryption or Cell Level Encryption implemented within SQL Server will almost always be the better choice for most SQL Server customers. You will not be locked into restrictions on the use of SQL operations, key management is well defined, and the encryption performance metrics are impressive.

I hope this overview of the two SQL Server encryption approaches has been helpful. Please feel free to reach out with any questions you have.

Patrick