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

lock icon, encryptionChances 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.

Oracle Data Encryption Options

Oracle Data Encryption Options

Oracle offers various authentication and audit features to protect data from unauthorized access. But what about data at rest in operating system files, backups or other storage media?

Protect Oracle Data At Rest With TDE

To protect data at rest, Oracle offers Transparent Data Encryption (TDE). With TDE you can encrypt sensitive data so that it is unreadable if the file it is stored in is exfiltrated or breached.

Data you encrypt with TDE is “transparently” decrypted when it is accessed by authorized users and applications. That is, decryption takes place without users even being aware that data is encrypted. Likewise, applications that process sensitive data can offer data encryption via TDE with little or no code changes.

Why use TDE? It helps ensure that your sensitive data is secure, supports compliance with a wide range of regulations like Sarbanes-Oxley (SOX), HIPAA and PCI, and can simplify your overall encryption/decryption policy and operations.

Another benefit of TDE is that it is pretty fine-grained. You can encrypt data at the column level or the tablespace level. Column-level encryption is perfect for confidential data like social security numbers or credit card numbers that are stored in table columns.

When you encrypt a tablespace, all objects created in that tablespace are encrypted automatically. Tablespace level encryption works well for tables that store sensitive data in multiple columns, or for when you want to protect an entire table and not just individual columns. It’s also handy anytime you want to avoid doing a nitty-gritty analysis of each table column to determine which ones require encryption.

To enable decryption and prevent unauthorized decryption, TDE uses a two-tiered, key-based encryption architecture. It stores encryption keys in a keystore, a hardware or software security module separate from the database. You can centrally (and automatically) manage these keystores using Oracle Key Vault.

To encrypt a tablespace, TDE uses an externally stored master key to encrypt the TDE tablespace encryption key, which is used to encrypt/decrypt tablespace data. For column-level encryption, Oracle transparently accesses a TDE master encryption key to encrypt or decrypt the TDE table key, which then encrypts/decrypts column-level data in the table.

Encryption Best Practices

Of course, your encryption strategy should be integrated with your overall information security program. Best-practice security tips related to encryption include:

      • Start by determining how sensitive the data is. Data that requires the strongest protection can be encrypted using the AES256 algorithm. Conversely, you can encrypt less sensitive data in several ways that offer performance benefits.
      • You also need to determine your approach to keystore protection based on data sensitivity. Options range from auto-login software keystores to hardware keystores. A separate keystore for TDE only is ideal if possible.
      • To limit damage from compromised admin credentials or insider threats, consider assigning separate security admins for TDE and for the database(s).
      • Backup your sensitive data using protected backup procedures.
      • Be aware that column-level encrypted data is decrypted during expression evaluation and could potentially be accessed in the associated on-disk swap file.
      • Also be aware that your Oracle data files could contain plaintext fragments (aka “ghost records” that were deleted logically from the table but still exist physically on-disk. These could potentially be accessed similarly to finding data on-disk after it has been deleted at the operating system level.

For more information on TDE, see the Oracle Advanced Security Guide online.

For expert help and guidance with encryption, backup/recovery, high availability and other business continuity and security concerns, contact Buda Consulting for a security risk assessment—the first step to finding and closing the gaps in your database security.