SQL Server Always Encrypted—Is It Right for My Data?

by Robert Buda | Oct 23, 2020 | Best Practices, Database Security, SQL Server

Chances are your company holds sensitive transactional data like personal, financial or health records. This data is often subject to regulations and is also coveted by cybercriminals. Unfortunately, unless encrypted it is vulnerable to compromise both at rest on the server that stores it, and in transit to/from clients that request it.

For Microsoft SQL Server users, Transparent Data Encryption (TDE) has long been available to protect data at rest in the event that database files or backups are compromised. But TDE still leaves encrypted data vulnerable to a privileged user who can access it via the database.

Further, TDE offers nothing to secure SQL Server data in transit. HTTPS can help, but still leaves data exposed to man-in-the-middle (MITM) and other attacks.

To help mitigate these security weaknesses, SQL Server 2016 introduced the Always Encrypted feature to protect sensitive data at rest, in motion and even in memory. Outside an application client’s connection, the data remains encrypted—across the network, server, storage and database.

With Always Encrypted, only users and applications with access to valid keys can decrypt the data, so MTM attacks, insider threats, etc. are nullified. In addition, Always Encrypted lets you encrypt data at the column level, whereas TDE requires encrypting the entire database.

Who should use Always Encrypted?

New privacy regulations and other compliance and data governance mandates are driving more and more SQL Server users to leverage Always Encrypted. It can be a big help with separating those who own and can view the data from those who manage the data but should otherwise not access it.

For example, Always Encrypted lets you protect Personal Information (PI) that you store in the cloud. Even when the data is outside your direct control, the cloud service provider, third-party DBAs, your on-premises admins and other privileged users can’t access it.

Because it is available with all SQL Server 2016 and newer editions (as of SP1), you can try Always Encrypted without purchasing additional licenses. However, the real investment lies in modifying the client application, which will handle the encryption/decryption outside the SQL Server or Azure SQL Database environment.

Always Encrypted cryptography

Always Encrypted is aptly named: from the standpoint of the database engine the data is literally “always encrypted.” But some queries on encrypted data are still supported, depending on the column-level encryption setting.

You can choose either of two encryption types:

1. Deterministic encryption, which always generates the same encrypted values. This enables the database engine to perform specific operations (point lookups, grouping, equality joins, and also supports indexing.
2. Randomized encryption, which is even more secure but makes the data write/display-only. Thus, it can’t be evaluated or used in any database operations and cannot be indexed.

The downside of deterministic encryption is that unauthorized users could potentially extrapolate information about the data in encrypted columns by examining patterns within the encrypted values, especially if the set of possible values is small (e.g., true/false). Randomized encryption generates a less predictable result, but precludes indexing, searching, grouping or joining operations on encrypted columns.

For columns of sensitive data that will be used as search or grouping parameters (e.g., US Social Security Numbers), you’ll need to use deterministic encryption. Randomized encryption can be used for data that isn’t grouped with other records and won’t be used to join tables.

History and enhancements

As noted above, Always Encrypted has been available across all SQL Server and Azure SQL Database editions and SQL Database service tiers since SQL Server 2016 (13.x) SP1. Prior to that, it was only available in the Enterprise and Developer Editions.

Beginning with SQL Server 2019 (15.x), the new Always Encrypted with secure enclaves feature extends the database operations you can perform on encrypted data to include pattern matching, other comparison operators and in-place encryption.

A secure enclave is basically a protected memory area specifically for processing Always Encrypted data within SQL Server. To the rest of the SQL Server engine, and to other processes on the host machine, a secure enclave is a “black box” that shields all code and data inside from outside processes, including debuggers.

To validate a secure enclave before sending it encryption keys, etc., both the client-side driver and SQL Server must contact an external attestation service. The process also relies on “enclave-enabled” column master and encryption keys.

While not without complexities and performance overhead, secure enclaves significantly extend the kinds of operations you can perform on Always Encrypted data.

How Always Encrypted works

Before it can process Always Encrypted data, a client application must be configured to use an approved driver, which will automatically encrypt and decrypt columns of sensitive data. The driver encrypts the data before passing it to the database engine. It also needs to automatically rewrite queries to preserve their semantics. When the database returns query results, the driver transparently decrypts these before storing them.

This works because only the client-side application, not the SQL Server environment, has access to the encryption keys. Other applications can retrieve the encrypted values but can do nothing with them.

To encrypt a column of data with Always Encrypted, you must first generate a column encryption key and a column master key. The former encrypts the data and the latter encrypts the former.

The column encryption key resides on the SQL Server instance, while the database engine stores metadata pointing to the column master key’s location. The column master key itself resides in the Windows Certificate Store, Azure Key Vault, a hardware security module or some other trusted external key store. The database engine never sees or stores either key as plaintext.

Always Encrypted use cases

What are some good reasons to try Always Encrypted? Here a few of the top use cases:

• To improve security of data in transit beyond what SSL can provide.
• To meet the demands of regulated industries like financial services and telecommunications around protecting Personally Identifiable Information (PII) like credit card numbers, customer names/addresses, etc.
• To improve security when outsourcing SQL Server DBA services, Always Encrypted ensures separation of duties between third-party DBAs and in-house application administrators.
• To improve security in scenarios where on-premises client instances need to access sensitive data stored on Microsoft Azure. Because the column master key resides on-premises in a trusted key store, Microsoft admins cannot access the cloud-based data.
• To improve security in situations where both the client application and database environment are hosted on Azure (as SQL Database or SQL Server instances running in a virtual machine). In this case, both the data and keys are potentially exposed to Microsoft admins on the cloud platform hosting the client tier. However, the data is still always encrypted in the cloud database.

In short, Always Encrypted makes the most sense for protecting PII and other types of data that you need to store, but don’t want to search on or display to application users.

Limitations with Always Encrypted

As noted above, Always Encrypted limits the kinds of operations you can perform on data; e.g., copying data between columns using UPDATE, SELECT INTO, etc. It also restricts the data types you can encrypt. Some of the excluded types include XML, IMAGE, TEXTNTEXT and GEOGRAPHY, as well as user-defined data types.

If you need to query Always Encrypted data from multiple applications, such as reporting tools and data warehouses, you will need to give them access to the appropriate database drivers and decryption certificates. This can be more challenging if you need to replicate encrypted data to other SQL Server instances.

Performance can also be a limiting factor with using Always Encrypted. Encryption/decryption obviously involve compute and storage overhead that will impact both the duration of queries and the volume of storage your application needs. You’ll probably want to do some testing with your specific CPU, RAM and disk setup to gauge the production impact.

Next steps

Want to talk with a database security expert before you dive into Always Encrypted? Contact Buda Consulting to schedule a free consultation.