The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Test Driving Always Encrypted with Secure Enclaves in SQL Server 2019

In this article, I’ll walk through the steps for setting up your environment so that you can start working with Always Encrypted using secure enclaves. This is a new security feature in SQL Server 2019 (still in preview at press time) that protects sensitive data from highly privileged but unauthorized users (such as DBAs and machine admins), while preserving SQL Server’s ability to perform rich queries against the very same data.

For an introduction to this feature, see my previous article that explains all the concepts. This article is about the “how-to,” and guides you through the process of getting started with this new technology.

Setup your Environment

We’ll configure a server machine for SQL Server 2019, but we’ll also need to configure a second machine for the attestation server. Fortunately, these can both be virtual machines, so setting this up in Azure is quite doable. Still, it’s a bit of a process with quite a few reboots, so be prepared to spend a good hour or so to get your environment ready.

Create the Server VMs

Start by creating a VM for the database server. In the left side navigation bar of the Azure portal, click Create a Resource. Under Azure Marketplace, click Compute, and then under Featured, click Virtual Machine:

Other than the VM image and size, you can set most of the inputs (e.g., subscription, resource group, VM name) as desired. For the VM image, select Windows Server 2019 Datacenter. And for the VM size, it needs to be one that supports nested virtualization in order to enable secure enclaves on the machine, which can be any size in the Dv3 and Ev3 series. For this demo, I’ve chosen Standard D2s_v3.

Then supply a username and password that you’ll use to login to the VM. For Public Inbound Ports, click Allow Selected Ports, and then check RDP (3389) in the Select Inbound Ports dropdown. Finally, click Review + Create and then Create.

It will take a few minutes to provision the VM. While you’re waiting, you can go ahead and create the VM for the attestation server. Just follow similar steps to create another VM with a different name in the same resource group as the first VM. This VM doesn’t need to support nested virtualization, but does require 2 CPUs and 8 GB RAM, which you get with the same Standard D2s_v3 size we used for the database server VM.

After Azure finishes deploying the new VMs, they will be in a running state, and you can login to each one of them. To login, go to each VM’s overview page, click Connect, and then click Download RDP File. When prompted to enter your credentials, click More Choices, and then click Use a Different Account. Supply the credentials you defined for the VM, and click OK to open a remote desktop session.

Configure the Attestation Server

On the attestation server VM, we need to configure the Host Guardian Service (HGS). To do this:

  • Run PowerShell as an administrator:
    • Click Start, then right-click Windows PowerShell, choose More, and then Run as Administrator
  • Add the HGS role
    • Type the following command:
    • This will reboot the VM. After it reboots, login and start PowerShell as an administrator again.

Install-WindowsFeature -Name HostGuardianServiceRole -IncludeManagementTools -Restart

  • Install the HGS service
    • Type the following commands:

$adminPassword = ConvertTo-SecureString -AsPlainText 'S0me5trongP@$$word' -Force

Install-HgsServer -HgsDomainName 'somedomain.local' -SafeModeAdministratorPassword $adminPassword -Restart

  • The password you specify for -SafeModeAdministratorPassword only applies to the Directory Services Repair Mode password for Active Directory; it does not change your login password for the VM.
  • You can provide any domain name you wish for -HgsDomainName.
  • This will turn the server into a domain controller and reboot the VM. After it reboots, login and start PowerShell as an administrator again.
  • Configure host key attestation
    • Type the following command:

Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey

Configure the Database Server

On the database server, let’s first install SQL Server 2019, which is still in preview at the time of this writing. So this part of the instructions can vary by the time you read this; as long as one way or another, you get SQL Server 2019 installed.

We’ll need to browse freely, so first turn off IE enhanced security. In Server Manager (which started up automatically), click Local Server on the left. Click the On link after IE Enhanced Security Configuration, and turn it off for administrators.

Open IE and head over to https://www.microsoft.com/en-us/sql-server/sql-server-2019. Click Download Now, choose Windows, and then click Preview SQL Server 2019 for Windows. While still in preview, you’ll need to register for an evaluation edition that expires in 180 days. When setup starts, choose a Basic installation and wait for setup to complete.

The setup won’t include SSMS however, so you’ll need to download that separately from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. Like many of the upcoming steps, this also requires logging in after a reboot and starting up PowerShell again as an administrator.

Next, we need to configure the database server as a guarded host, which registers the machine with HGS using host key attestation. To do this, run PowerShell as an administrator and type the following command:

Enable-WindowsOptionalFeature -Online -FeatureName HostGuardian -All

Then answer Y for the prompt to reboot.

Because this is a VM, you also need to remove the VBS (virtualization based security) requirement for platform security features. Back in PowerShell, type:

Set-ItemProperty -Path HKLM:\SYSTEM\CurrentControlSet\Control\DeviceGuard -Name RequirePlatformSecurityFeatures -Value 0

Restart-Computer

Now, generate a unique host key certificate for the database server and export its public key to the attestation server. In PowerShell, type:

Set-HgsClientHostKey

Get-HgsClientHostKey -Path $HOME\Desktop\hostkey.cer

With two remote desktops open to the two servers, it’s easy to get the public key transferred over to the attestation server, just by copying and pasting between desktops. Right click the hostkey.cer file on the desktop and choose Copy. Then…

Register the Database Server with HGS

…back on the attestation server, right-click on the desktop and choose Paste. Then, in PowerShell, type:

Add-HgsAttestationHostKey -Name <sql-server-machine-name> -Path $HOME\Desktop\hostkey.cer

Be sure to plug in the name of your SQL Server machine, which is now registered by it’s public host key for attestation.

Finally, discover the IPv4 address of the attestation server with:

ipconfig

Jot down the IPv4 address, which you’ll need for attestation in the next step. You’ll also need it when connecting from client applications whenever you want to enable secure enclaves. Now switch back once more to the database server; you won’t need to work directly with the attestation server any more (but it needs to remain running, of course).

Enable Always Encrypted with Secure Enclaves

Back in PowerShell on the database server, tell SQL Server where to attest by typing:

Set-HgsClientConfiguration -AttestationServerUrl http://<ipaddress>/Attestation
-KeyProtectionServerUrl http://<ipaddress>/KeyProtection/

Be sure to plug in the IPv4 address of your attestation server in both places, which you noted in the previous step. If everything is working properly, you should see that AttestationStatus has passed:

We can now enable Always Encrypted with Secure Enclaves. Start SSMS, open a new query window, and type the following commands:

EXEC sys.sp_configure 'column encryption enclave type', 1
RECONFIGURE

Restart SQL Server by right-clicking on the connection in Object Explorer and choosing Restart.

There is one last step that won’t be necessary with the final SQL Server 2019 release. But while in preview, rich computations are disabled and must be explicitly enabled with the following command:

DBCC traceon(127, -1)

Test-Drive the Technology

Your environment is all setup, and you’re ready to start working with Always Encrypted using secure enclaves.

Create a Database

Execute the following commands in SSMS to create a new database with a few rows in the Customer table:

Create Enclave-Enabled Keys

We’ll encrypt the SSN column, and we’ll use secure enclaves so that we can also run rich queries over that column on the server. To do this, we need to create keys that are enabled for enclaves. Specifically, you create an enclave-enabled CMK, and then any CEKs encrypted by that CMK are implicitly enabled for enclaves as well.

In Object Explorer, refresh the Database node. Then drill down on MyEncryptedDB to Security, Always Encrypted Keys, Column Master Keys. Right-click Column Master Keys and choose New Column Master Key. The key store should already be set to Windows Certificate Store – Current User, and the Allow Enclave Computations checkbox should already be checked. Type CMK1 for the name, and click Generate Certificate to create the in the current user’s certificate store:

Click OK to create the CMK.

Now we’ll create the CEK. In Object Explorer, right-click Column Encryptions Keys and choose New Column Encryption Key. Type CEK1 for the name and select CMK1 from the Column Master Key dropdown. Notice the dialog indicates that the CEK will be enabled for enclave computations:

Click OK to create the CEK.

Encrypt Columns In-Place

And now for the first real test. Without secure enclaves, we needed to use a special wizard and round-trip all the data between SQL Server and SSMS in order to encrypt data in this table. But with secure enclaves, we can encrypt “in-place,” that is, without a network round-trip.

For this, we’ll need a special connection to the database, one which also references the attestation server. Right-click in the query window and choose Connection > Change Connection. In the connection dialog, click Options >> and then click the Always Encrypted tab. Select the Enable Always Encrypted checkbox, which effectively sets “column encryption setting=enabled” in the connection string, just like Always Encrypted V1. But by supplying our enclave attestation URL, we’re also enabling Always Encrypted with secure enclaves. This URL is based on the IP address of the attestation server. In my case, this was 10.0.6.5, so the full URL is http://10.0.6.5/Attestation:

Click Connect. This also changes the default database back to master, so be sure to run this USE statement right after:

SSMS will now prompt you to enabled parameterization for Always Encrypted:

Click Enable, which will let us perform client-side encryption of parameterized T-SQL statements right inside of SSMS.

Now run the following ALTER statements:

This ALTER statement encrypts the SSN column using randomized encryption with CEK1. Yet this is achieved without the elaborate process of the Always Encrypted wizard in SSMS, which required round-tripping the entire table. Instead, as I’ve explained, SQL Server can pass plain text into the enclave, the enclave can then encrypt that data using the CEK which it obtains from the client machine via the secure tunnel (after attestation succeeds), and then the enclave can pass the encrypted value back to SQL Server which simply updates the data in-place. And all this without SQL Server ever having access to the CEK.

After encrypting the table, it’s also important to clear all query plans to refresh parameter encryption information:

If you query the table now, the SSN column still appears in clear text, but that’s just because this SSMS window has Always Encrypted enabled on the connection. If you try to query the table on without Always Encrypted enabled, or indeed, without the CMK available, the results return the SSN column in its raw encrypted form:

If we wanted to query on SSN with Always Encrypted V1, we’d need to use deterministic encryption, and even then, we’d be limited to equality comparisons. Yet this SSN column is randomly encrypted, which is more secure than deterministic encryption, and we’ll still be able to perform rich computations over this randomly encrypted data thanks to the secure enclave.

Let me prove it. Back in the query window with Always Encrypted enabled, issue the following query:

Sure enough, the query succeeds even though we’ve randomly encrypted the SSN column:

Now let’s try a range search, which was never possible with V1:

But now, the query works just fine, and returns the two rows where the first character of the SSN column is alphabetically higher than 5:

Finally, let's test out pattern matching with LIKE:

And just like magic, this succeeds as well:

In each case, SQL Server was able to execute the query, and relied on the secure enclave for the cryptography operations that were needed by the query. At the same time, SQL Server never gained access to the CEK, but rather, delegated the portions of the query requiring decryption to the secure enclave. Remember, this enclave is essentially – from a trust perspective – the client itself. But it’s located physically on the server machine, rather than the client machine.

Conclusion

In this article, you installed SQL Server 2019 on an Azure VM to work with Always Encrypted and secure enclaves. You also configured another VM as an attestation server for clients to trust the enclave on the SQL Server VM. I hope this helps you jumpstart your adventures with Always Encrypted and secure enclaves. Thanks for reading, and happy coding!

Lenni Lobel
About the author

Leonard Lobel (Microsoft MVP, Data Platform) is CTO and co-founder of Sleek Technologies, Inc., a New York-based development shop with an early adopter philosophy toward new technologies. He is also a principal consultant at Tallan, Inc., a Microsoft National Systems Integrator and Gold Competency Partner.

Programming since 1979, Lenni specializes in Microsoft-based solutions, with experience that spans a variety of business domains, including publishing, financial, wholesale/retail, health care, and e-commerce. Lenni has served as chief architect and lead developer for various organizations, ranging from small shops to high-profile clients. He is also a consultant, trainer, and frequent speaker at local group meetings, VSLive, SQL PASS, and other industry conferences.

Lenni has also authored several MS Press books and Pluralsight courses on SQL Server programming.

Please login or register to post comments.

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids