Category Archives: Always Encrypted

SQL 2019: Always Encrypted with Secure Enclaves, Part 2

4 Replies

In the first post of this series, we explored the requirements for using Always Encrypted with secure enclaves, as well as some of the limitations.

For this post, we’ll be using Powershell to install and configure the HGS server (required for “attestation”) as well as executing the steps required to configure the SQL 2019 server to work with HGS.

Full requirements for running Always Encrypted with secure enclaves can be found here. Operating system requirements are:

  • HGS server running Windows Server 2019, Standard or Datacenter edition
  • SQL Server running Windows Server 2019 Datacenter edition
  • SSMS 18.0 and later for testing (we won’t be running an application server)

Both the client and SQL Server must have connectivity to the HGS environment, and we’ll be using SSMS as the client. SSMS 18.0+ is required because it has the ability to alter the connection string to support Always Encrypted.

We’ll be using VMs for this scenario, and we could use SSMS on the host, but to keep things simple, we’ll be using SSMS on the SQL instance. However, you should never do this in the real world. Why?

The first reason is that Always Encrypted is designed to allow for role separation between those who own the data, and those who manage the data environment (DBAs). In the examples that follow, if a single person is using SSMS, generating the certificates, and subsequently encrypting data, there isn’t any separation of roles.

Again, I want to reiterate that for real-world usage, you should never have a single person do what’s outlined in the examples that follow – sample code is for proving concepts only. Details about using SSMS to deploy Always Encrypted can be found here.

The second reason you should not use SSMS on the SQL instance to provision keys and deploy encryption is that data can be leaked. More details about that here.

The main point from that link is:

“Make sure you run key provisioning or data encryption tools in a secure environment, on a computer that is different from the computer hosting your database. Otherwise, sensitive data or the keys could leak to the server environment, which would reduce the benefits of the using Always Encrypted.”

HGS

HGS can perform attestation using more than one method:

  1. Host Key
  2. Trusted Platform Module (TPM)

HGS and its implementation in your organization are vast topics, and detailed information about HGS attestation can be found here.

Microsoft recommends TPM for production environments and Host Key for lower environments, but it should be noted that many of the benefits of using TPM are reduced or eliminated if SQL Server runs virtualized.

Using an enclave with Always Encrypted is new to SQL 2019. Virtualization Based Security (VBS) is used to create the enclave, and is a feature of the Windows Hypervisor.

From the documentation:

“In the VBS enclave trust model, the encrypted queries and data are evaluated in a software-based enclave to protect it from the host OS. Access to this enclave is protected by the hypervisor in the same way two virtual machines running on the same computer can’t access each other’s memory. In order for a client to trust that it’s talking to a legitimate instance of VBS, you must use TPM-based attestation that establishes a hardware root of trust on the SQL Server computer. The TPM measurements captured during the boot process include the unique identity key of the VBS instance, ensuring the health certificate is only valid on that exact computer. Further, when a TPM is available on a computer running VBS, the private part of the VBS identity key is protected by the TPM, preventing anyone from trying to impersonate that VBS instance.

 Secure Boot is required with TPM attestation to ensure UEFI loaded a legitimate, Microsoft-signed bootloader and that no rootkits intercepted the hypervisor boot process. Additionally, an IOMMU device is required by default to ensure any hardware devices with direct memory access can’t inspect or modify enclave memory.

These protections all assume the computer running SQL Server is a physical machine. If you virtualize the computer running SQL Server, you can no longer guarantee that the memory of the VM is safe from inspection by the hypervisor or hypervisor admin. A hypervisor admin could, for example, dump the memory of the VM and gain access to the plaintext version of the query and data in the enclave. Similarly, even if the VM has a virtual TPM, it can only measure the state and integrity of the VM operating system and boot environment. It cannot measure the state of the hypervisor controlling the VM.”

TPM is much more complicated to deploy, which is why we’ll be using Host Key attestation.

When you install the HGS feature, a Windows Server Failover Cluster (WSFC) is created, even if you only have only a single node. Presumably this is to allow you to add HA capabilities, i.e., additional nodes. DNS and Active Directory are also installed on the HGS cluster, and corporate DNS solutions will have to forward relevant requests to the HGS cluster. The HGS node also becomes a Domain Controller.

Here are the steps we’ll be following to deploy and configure the HGS server for for Host Key attestation:

  • install the Host Guardian Service role
  • define a domain name
  • define a password for the Directory Services restore mode
  • install the HGS server, referencing the domain name and password that we defined
  • initialize HGS Attestation, giving a name to the service, and specifying that we want to use TrustHostKey
  • copy the certificate from the SQL 2019 host to the HGS server
  • add that host key to HGS attestation
  • create self-signed certificates for HGS attestation and key protection
  • export those certificates to files
  • display the thumbprints for the each of the new certificates
  • add those thumbprints to protect the encryption and signing certificates within HGS
  • initialize the HGS server with the certificates we just created
  • validate the HGS environment (assumes you have completed configuration on the SQL host, which was not included in these steps)

The first steps are to create two VMs in Hyper-V, and make sure that one of them is running Windows 2019 Datacenter. I did this on my Windows 10 laptop, which has the following specs:

  • 32GB memory
  • 2TB external Samsung T5 connected via Thunderbolt

VM specs:

  • HGS, 3GB of memory, and 2 vCPU
  • SQL Server, 4GB of memory, 4 vCPU (our PoC will use almost zero resources)

On the server running Datacenter, rename the machine to “SQL2019VM”, and install SQL 2019

On the other VM, (where we’ll be installing HGS), open Powershell ISE with Administrator privileges, and save the following code as a script. The code assumes that you will be adding a SQL Server host named SQL2019VM to the HGS environment.

I’m using “supersonic.local” for the HGS domain name.

If you didn’t encounter any errors, you can move on to configuring the SQL2019VM.

There’s no point in going further if both the SQL VM and the HGS VM don’t validate properly. If you have errors on the HGS server or the SQL Server, carefully check the code you ran, and perhaps retry.

Here’s an example of the type of output you would hope to see from executing Get-HgsTrace:

Here’s an example of the type of output you would hope to see from executing Get-HgsClientConfiguration:

Certificates

On the SQL2019VM server, verify that the certificates for the current user were properly created by going to a command prompt and executing:
certmgr.msc /f /y

On the HGS server, verify that the certificates for the local machine were properly created by going to a command prompt and executing:
certlm.msc

Sample output for the current user:

Sample output for the local machine:

Assuming everything looks ok, we can begin to configure SQL Server – we’ll tackle that in the next post.

SQL 2019: Always Encrypted with Secure Enclaves, Part 1

2 Replies

I’m going to start this blog post series with a basic introduction to what’s required in order to deploy Always Encrypted with Secure Enclaves, and how the pieces fit together.

First, some background on using encryption with SQL Server.

Cell/column-level

Column-level encryption is described here.

It uses certificates to encrypt data, and the application must change in order to do the actual encryption/decryption. As you can imagine, changing applications is not too popular. Both the certificates and the data are available to highly privileged technology staff (DBAs).

At rest

Transparent Data Encryption also known as TDE, encrypts data “at rest”. You can’t restore or attach a database or database backup to a server that doesn’t have the same certificate as the source environment.

TDE has been the defacto standard for encrypting SQL Server databases for ages. It’s even been recently enhanced to allow you to pause and resume, which is great functionality to have for large databases.

See this link for more details on pausing and restarting TDE.

TDE is I/O based encryption. That means SQL Server processes that circumvent the I/O stack can’t use it. This was the case for SQL 2016, if you used NVDIMM-N/PMEM, because those hardware solutions for storage latency circumvent the I/O stack (that changes in SQL 2019, where using the Hybrid Buffer Pool allows you to use TDE).

The Database Encryption Key (DEK) resides in the database boot page of the encrypted database, so that it can be referenced during database recovery.

The main issue with TDE from a compliance/auditing perspective, is that highly privileged users (DBAs and sysadmins) have access to both the database and the encryption keys.

In transit

TDE is great for encrypting data “at rest” but doesn’t handle encrypting data “across communication channels”, as the documentation states.

For “across the wire” encryption, you’ll need to use TLS, and configure things properly. Details here.

In use

Always Encrypted resolves the “highly privileged user” issue – the certificates used for encryption don’t reside in the SQL Server environment. Data can only be decrypted on the client, so it’s also encrypted in transit. And unlike TDE, data is also encrypted in memory.

Using Always Encrypted in SQL 2016/2017 has limited functionality, including (but not limited to) the following:

  • Initial encryption and key rotation required moving all data to be encrypted out of the database, encrypting it, and writing it back to the database (encryption happens on the client)
  • Columns that were randomly encrypted could only be referenced by equality predicates
  • It was not possible to index columns with random encryption

Enter the Enclave

SQL 2019 supports an enhanced version of Always Encrypted, known as “Secure Enclaves”. What is an enclave? It’s like a consulate: “….a state that is enclosed within the territory of another state”.

It takes the form of a protected region of memory within the SQL Server environment, requiring special credentials for access. Data in the secure enclave lives in an unencrypted state.

However, as I’ll discuss later in this series, depending on how your organization implements Always Encrypted with Secure Enclaves, it might not be as secure as you had hoped.

HGS and Attestation

Client applications need to be assured that the enclave they are connecting to (on the SQL Server) is safe and secure. That’s were “attestation” comes in – a separate computer/VM assumes the responsibility of attesting to the validity of the enclave. This attestation comes in the form of a Windows service named “Host Guardian Service”, or HGS.

There are at least three required components for encrypting/decrypting data using Always Encrypted with Secure Enclaves:

  • Client/application server
  • SQL Server
  • HGS server

Clients (i.e. an app server) and SQL Server must have connectivity to the HGS server.

Data in the enclave is plaintext, which allows SQL Server to delegate non-equality comparisons to the enclave. A DLL resides in the enclave that allows this to happen.

HA/DR

Just like your production SQL Server instance, your production HGS environment should be redundant. Microsoft supports creating a Windows Server Failover Cluster (WSFC) for HGS, and in this specialized form of a WSFC, there is no shared storage.

For lower environments, you might consider running HGS on a single computer, but you should be aware that if the HGS environment suffers an outage, queries against encrypted data fail.

Unlike other forms of encryption for SQL Server, Always Encrypted uses the client side to encrypt/decrypt data. In this context, “client side” could be an application server, but it could also be a properly configured SSMS session.

What the enclave buys you

Not unlike many 1.0 features, Always Encrypted – which was released in SQL 2016 had many severe limitations that prevented it from being widely adopted. There were no changes to Always Encrypted in SQL 2017.

Some of the limitations of using Always Encrypted without an enclave are addressed when using an enclave:

  • Initial encryption and key rotation allow encryption “in place”
  • Non-equality predicates are supported, i.e. LIKE, >=, <=
  • Creating indexes on columns with random encryption

Under certain circumstances enclaves won’t be able to manage your encrypted data “in place”. So where does it go? The encrypted data to be manipulated must be copied to the memory on the client, unencrypted, manipulated, re-encrypted, and sent back to the enclave, which sends it to the database. That could be incredibly painful, but as usual, it depends.

Under the hood

The initial size of the enclave is 29 kilobytes, and it can grow to a maximum size of 35 megabytes. The amount of memory allocated to the VM or physical server does not affect the size of the enclave.

The enclave can grow, but once it grows it cannot shrink.

The size of the enclave can be interrogated in sys.dm_column_encryption_enclave

Don’t ditch that TDE just yet

You’re not supposed to be using Always Encrypted on your entire database – it’s meant to be used on specific columns, and in conjunction with TDE (not instead of it).

Why, you might ask?

I can think of at least three reasons:

  • Performance impact: client-side encryption has overhead
  • Storage impact: using Always Encrypted on a significant number of columns in your database could drastically increase the storage footprint. See “cyphertext length” at this link for more info.
  • Risk: if you remove TDE, it’s even easier for rogue folks to get a hold of your data. Maybe not the columns encrypted with Always Encrypted – but all the other formerly TDE-encrypted data is exposed to anyone that has access. All they have to do is grab a backup, and restore it on another instance of SQL Server.

SSMS

Microsoft has done a great job of making our lives easier, specifically in SSMS. The client driver knows which columns are encrypted with Always Encrypted (encryption metadata is cached), and if you reference one of those columns in a query and use a variable as a predicate, the client driver converts this to a parameterized query behind the scenes.

More details about how .NET automagically works with Always Encrypted can be found here.

High level limitations

Always Encrypted still has a lot of limitations, but mainly what you have to do is change the way you think about how SQL Server operates on data.

For example, you can’t do the following in a stored procedure, if the column is encrypted with Always Encrypted:

Why will this fail?

Because Always Encrypted is specifically designed to disallow this behavior.

Remember – only the client side can encrypt/decrypt data – SQL Server has no ability to do this. In this scenario, SQL Server must insert data that’s already encrypted. But the data referenced above is “plain text”, so the INSERT will fail. Predicates used withing stored procedures that will be compared with Always Encrypted columns must also be encrypted, and that includes parameters.

SQL Server data masking

Always Encrypted is not compatible with data masking or user-defined types (UDT). That’s unfortunate, as many companies have a requirement to obfuscate data in lower environments.

Linux support

Support for secure enclaves is on the roadmap for Always Encrypted on Linux. Secure enclaves in its present form uses Virtualization Based Security (VBS) which is a Windows thing, so Linux can’t use VBS. Microsoft will implement secure enclaves on Linux based on Intel SGX. More info about SGX can be found here.

Availability Groups

All replicas must be configured to use a secure enclave in order to be fully functional. If a secure enclave is not configured for a replica, and a query is executed on the replica that references encrypted columns, the query will fail.

Backup/Restore

Restore is similar to AGs in that a secure enclave must be configured in order to run queries against encrypted columns. But restore also has an additional requirement. Remember that the enclave must be attested to by a computer running HGS. Therefore, if you create a backup of a production database that uses a secure enclave, and restore it in a lower environment (which likely has its own HGS configuration), unless you re-encrypt the data with the lower environment certificate (this is known as key rotation), your queries will fail. You can rotate the keys without an enclave, but it will not be done “in-place” – all encrypted data will have to be copied to the client, decrypted, re-encrypted, and written back to the database.

Replication

Only columns that are not encrypted with Always Encrypted can be replicated. I tested this with Transactional Replication, and it works.

In the next post, we’ll configure Always Encrypted in SQL 2019 to use a secure enclave.