Deploying SQL Server Always Encrypted with Secure Enclaves on Amazon EC2 Instances

More Info

Always Encrypted with secure enclaves is an advanced encryption solution present in SQL Server 2019, facilitating complex computations on encrypted data. With an increasing emphasis on safeguarding sensitive information, this feature empowers users to secure data such as credit card information or national identification numbers (for instance, U.S. social security numbers) stored within SQL Server databases.

Always Encrypted enables clients to encrypt sensitive information directly within their applications, ensuring that encryption keys are never exposed to the Database Engine (SQL Database or SQL Server). This mechanism protects the confidentiality of the data from high-privileged users like database administrators or system administrators.

The Always Encrypted with secure enclaves feature relies on Virtualization-based Security (VBS). You can explore and launch instances with compatible Amazon-provided AMIs through the Amazon EC2 console.

In this guide, we will implement Always Encrypted with secure enclaves on Amazon EC2 Windows instances.

1. Prerequisites

You will need an Amazon EC2 Windows instance to run the Host Guardian Service, which is essential for enclave attestation. Launch an Amazon EC2 instance that supports VBS and install SQL Server on it.

2. Tutorial

The Host Guardian Service (HGS) is a role introduced in Windows Server 2016 for configuring protected hosts and shielded VMs. This role provides Attestation and Key Protection services, with the Key Protection service delivering the transport key required to unlock and operate shielded VMs.

For this demonstration, I will be utilizing an Amazon EC2 m5.xlarge instance for the Host Guardian Service and an Amazon EC2 m5n.xlarge instance for SQL Server.

2.1 Configuring the Host Guardian Service on Amazon EC2 HGS Instance

Log into the Amazon EC2 HGS instance as an administrator and execute the following commands in an elevated Windows PowerShell console:

Install the Host Guardian Service role with the command below, which will restart the instance:

Install-WindowsFeature -Name HostGuardianServiceRole -IncludeManagementTools -Restart

After the reboot, log back into the Amazon EC2 HGS instance as an administrator. Configure the Host Guardian Service and its domain using the command below, replacing ‘Password’ and ‘Domain Name’ with your actual values. This will also restart the instance:

$adminPassword = ConvertTo-SecureString -AsPlainText 'Password' -Force
Install-HgsServer -HgsDomainName 'Domain Name' -SafeModeAdministratorPassword $adminPassword -Restart

Once the instance has rebooted, log back in and run the following command to set up host key attestation:

Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey

2.2 Setting Up Amazon EC2 SQL Server Instance

Sign into your Amazon EC2 SQL Server instance as an administrator. Use an elevated Windows PowerShell console to run the following command to install the Host Guardian feature, which will prompt a dialog to restart the computer:

Enable-WindowsOptionalFeature -Online -FeatureName HostGuardian -All

After restarting, log back into the Amazon EC2 SQL Server instance as an administrator. Create a host key certificate for attestation between the HGS and SQL Server instances with the command below, specifying your desired location:

Set-HgsClientHostKey
Get-HgsClientHostKey -Path $HOMEDesktophostkey.cer

After executing the commands, you should see that the host key certificate is successfully created.

Transfer the host key certificate from the Amazon EC2 SQL Server instance to the desktop of the Amazon EC2 HGS instance. Next, run the following command on the HGS instance to initialize attestation:

Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey

Now, return to the Amazon EC2 SQL Server instance and execute the following command to establish the attestation link. Ensure to replace ‘10.50.3.43’ with the actual IP address of your Amazon EC2 HGS instance:

Set-HgsClientConfiguration -AttestationServerUrl http://10.50.3.43/Attestation -KeyProtectionServerUrl http://10.50.3.43/KeyProtection/

After running the command, the attestation status should indicate a successful connection between the SQL EC2 instance and the HGS EC2 instance.

2.3 Configuring Always Encrypted with Secure Enclaves on SQL Server Database

Log into your Amazon EC2 SQL Server instance and connect to the SQL Database Engine using SQL Server Management Studio (SSMS). Open a new query window and run the following script to set the enclave type to Virtualization Based Security (VBS):

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

Restart the SQL Server instance by right-clicking on the SQL instance name in Object Explorer and selecting Restart for the changes to take effect. After the restart, reconnect to the instance.

Open a new query window in SSMS and execute the following script to create a database named AESEDemo, which will later hold test records for encryption:

CREATE DATABASE [AESEDemo];
USE [AESEDemo];
CREATE TABLE [dbo].[AESETest] (
    [AESETestID] [int] IDENTITY(1,1) NOT NULL, 
    [SSN] [char](11) NOT NULL, 
    [FullName] [nvarchar](50) NOT NULL 
);
INSERT INTO [dbo].[AESETest] ([SSN],[FullName]) VALUES
('123-45-6789', N'Alex'), 
('145-93-3242', N'Sarah'), 
('134-12-4356', N'David');

To utilize Always Encrypted, you must create a Column Master Key (CMK) and a Column Encryption Key (CEK). Access to these certificates is necessary for decrypting the data. The CMK protects one or more CEKs, which in turn encrypt and safeguard the data within the columns.

To create a CMK, expand the Security tab in the AESEDemo database and navigate to the Always Encrypted Keys option. Right-click on Always Encrypted Keys and select New Column Master Key.

After this, a dialog will open allowing entry of the master key name and key store selection. For this guide, I will designate the Master Key name as CMK and select Windows Certificate Store – Current User as the Key Store. Ensure to enable Allow enclave computations, generate the certificate, and then confirm your selection.

If you encounter issues enabling Allow enclave computations, consult the correct EC2 AMI listed here.

The subsequent step involves creating a Column Encryption Key. Under the Always Encrypted section, right-click on Column Encryption Keys to generate the CEK.

After successfully creating the CMK and CEKs, you can proceed to encrypt the data within the database using these keys.

For additional insights on this topic, check out this blog post or explore this excellent resource on Reddit here.