In this post, we will discuss the primary encryption options available to SQL Server.  Each comes with benefits and costs and require careful consideration of not only what is being encrypted but why it needs to be encrypted.  None of the options out here should be solely relied on for data security but can be complimentary to other steps taken to keep sensitive data private.

Encrypted Connections

SQL Server can use a certificate installed on the server for secure connections to the server.  In SQL Server 2016+, only Transport Layer Security (TLS) can be used for secure connections.  Previous versions may use Secure Sockets Layer (SSL).  Additional processing is required to handle the encryption and decryption of the packets.

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a database-level encryption that protects data at rest.  This means that the data and log files stored on the server are protected, however, the data is not encrypted while in motion.  This allows data to be encrypted using AES and 3DES encryption without having to modify existing applications allowing compliance with certain government or industry guidelines.

TDE does real-time I/O encryption and decryption using of data and log files using a database encryption key (DEK).  The DEK is a symmetric key that is stored by the database boot record for availability during recovery.  It is secured by a certificate stored in the server’s master database or an asymmetric key protected by an extensible key management (EKM) module.  This additional processing comes at a cost in terms of CPU usage and increase reads/writes.

TDE does not provide encryption across communication channels.  See the Encrypted Connections below for information about encrypting data across communication channels.

Always Encrypted

Starting with SQL Server 2016, Always Encrypted allows a more granular approach to encrypting data.  You can configure Always Encrypted on specific columns rather than the entire database.

The data is encrypted and decrypted via a client-side driver and the keys are never stored in plaintext on the SQL Server itself.  This key difference keeps the data encrypted in-motion and prevents those with access to the database from being able to read it directly.  It also creates the need for changes to the application layer to support the change.

Unlike TDE, the data remains encrypted across communication channels, but its end-to-end encryption makes this a bad option for key columns that need to be searchable.

Secure Enclaves

Now available in SQL Server 2019, Always Encrypted with secure enclaves expands the abilities of Always Encrypted with in-place encryption and rich computations.  This allows the possibility pattern matching on Always Encrypted columns not previously possible.

Like the previous version of Always Encrypted, the data is encrypted end-to-end.  The key difference is a protected region of memory within the Database Engine process that can safely access sensitive data in

plaintext without being compromised.  This secure enclave cannot be viewed from the outside, even with a debugger.

This feature is a little more complex to set up than Always Encrypted as it requires a separate machine with the Host Guardian Service as an attestation service.  This service ensures that the SQL Server VBS enclave is in the correct state and can be trusted.

Additional Considerations

The encryption options available in SQL Server come with advantages and disadvantages that should be considered.  When choosing an option to secure and protect data, answer the following questions:

• Who does the data need to be secured from?
• Does the data need to be encrypted according to specific regulations or guidelines?
• Can existing code be modified?
• Can the server handle the additional overhead required?
• Does this data really need to be stored?

The answers to these questions will help identify which encryption option is best.

In the next post in this series, we will look at how to encrypt your connections to SQL Server.