Enabling Transparent Data Encryption for Microsoft SQL with Vault

At HashiCorp, we hear every day from our customers and users about their journey to dynamic cloud-based infrastructures, with workloads and data distributed across hybrid and/or multiple public clouds.

But as large enterprises embark on this journey to move their workloads and data to various clouds, they have to protect their sensitive workloads and data in their existing infrastructure. Just as data security is important in the public cloud, it is equally important to protect sensitive data even behind a private network perimeter by leveraging defense-in-depth strategies, especially as ransomware attacks, threats from nation states, and insider attacks continue to increase.

Data should be protected both at rest and while in transit. In-transit data protection is usually accomplished by establishing a mutual TLS channel between the two parties before data is transmitted. Protecting data at rest is also important, as database files and backups may be stolen or leaked. If data at rest is encrypted, it cannot be used without having access to decryption keys. This blog post will cover how HashiCorp Vault can help extend data protection for data at rest, specifically data that resides in your Microsoft SQL servers.

»What Is Transparent Data Encryption?

As highlighted above, protecting data at rest is a critical part of employing a defense-in-depth posture. Data at rest is protected by encrypting the data by using data encryption keys (DEKs). But working with encrypted data requires that the user or the application be aware that the data is protected by a DEK and that it must be decrypted using the correct DEK. This means that the application needs access to the encryption key (or knows how to get access to the encryption key).

With transparent data encryption (TDE), the need for the user or the application to be aware that the data is encrypted is obfuscated. When the application wants to read the data, the system (MS SQL in this case) can decrypt the data without user intervention. The system will also encrypt the data in the background when data needs to be stored on the disk. In other words, systems such as SQL servers transparently encrypt/decrypt data at rest without any changes to the end users’ workflow.

TDE comes in several flavors:

• Encrypting fields or columns in a database
• Encrypting tables in the database
• Encrypting files that get stored on the disk

»Why Enable TDE?

Consider a scenario where your SQL servers are accidentally exposed to unauthorized parties or malicious actors are able to gain access to servers storing extremely sensitive customer data. Without encryption, these unauthorized parties would have access to data, potentially leading to untold damage.

With security controls such as TDE, the data stored on disk is encrypted by a data encryption key (DEK), which is required in order to decrypt the data. So even if an attacker accesses the files or disk of the SQL server, they would still need to get the key to unlock the data.

»Vault MS SQL TDE Extensible Key Management (EKM) Module

Now that we understand how TDE enables end users to continue using their existing workflows without modification, what are the challenges around implementing it? As mentioned above, the data being protected is encrypted using a DEK. But the DEK is usually stored alongside the data that is being protected, which poses a problem if the server is compromised. In our earlier scenario, for example, the attacker would have access to the encrypted data and the key to decrypt the data. This is obviously not ideal, so MS SQL servers running on Windows offer the ability to further protect the DEK via an external key-management solution (KMS).

HashiCorp Vault 1.9 introduces the ability for Vault to manage the security of data encryption keys for Microsoft SQL Server. With the Vault MS SQL EKM module, Vault Enterprise customers can leverage Vault as a key-management solution to encrypt and protect the DEK, which in turn protects data that is being stored in SQL servers.

Once the EKM module has been installed and configured on MS SQL Server, the server must be configured to enable transparent data encryption. Once configured, each MS SQL Server instance will generate its own DEK. This DEK is then sent to Vault via the EKM interface to encrypt the DEK. The encrypted DEK is then returned to the SQL server, while Vault safely protects the key encryption key (KEK) behind its barrier. The SQL server can now safely store the encrypted DEK on disk.

When a user or an application wants to read or write data to a database, MS SQL Server will send the encrypted DEK to Vault to be decrypted. The decrypted DEK resides in memory on the SQL server and is used for encryption and decryption operations without the end user’s knowledge.

Let’s examine the attack scenario again: The attacker now has access to the encrypted disk and within that disk resides an encrypted DEK. This means that the attacker still has to acquire the KEK in order to decrypt the DEK to decrypt the data. Adding defense in depth (using a KEK to protect the DEK), makes the attacker’s job that much more difficult and helps protect sensitive data.

»Next Steps

The EKM module is available as part of the Advanced Data Protection Key Management module for HashiCorp Vault and can be downloaded at https://releases.hashicorp.com. For more information about how to install and configure the module, see the Vault EKM Provider for SQL Server documentation.