Microsoft Workloads on AWS

Deploying SQL Server Always Encrypted with secure enclaves on Amazon EC2 bare metal instances

Always Encrypted with secure enclaves is an encryption technology available in SQL Server 2019, which allows rich computations on encrypted data. With increased focus on data protection, this feature helps customers to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers) stored in SQL Server databases.

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). By using this feature, it protects the confidentiality of data from high-privileged users like DBA’s or system admins.

Always Encrypted with secure enclaves feature is dependent on Virtualization-based security (VBS) and requires access to Input-output memory management unit (IOMMU). Access to the low-level hardware features such as Virtualization-based security (VBS) and Intel VT-x are available only on Amazon EC2 bare metal instances due to lack of IOMMU virtualization in the current hypervisor generation.

In this tutorial, we implement Always Encrypted with secure enclaves on Amazon EC2 Windows bare metal instances.

1. Prerequisites

  • Amazon EC2 Windows instance to run Host Guardian Service, which is required for enclave attestation.
  • Amazon EC2 Windows bare metal instance with SQL Server installed.

2. Tutorial

Host Guardian Service (HGS) is a Windows Server role introduced in Windows Server 2016 for configuration of guarded hosts and shielded VM’s. This role provides Attestation and Key Protection services. Attestation service validates Hyper-V host. Key Protection service is used to provide the transport key to unlock and run shielded VM’s.

AWS supports Bare metal instances to allow customers to run applications that benefit from deep performance analysis tools, specialized workloads that require direct access to bare metal infrastructure, legacy workloads not supported in virtual environments, and licensing-restricted applications. We are using Bare metal instances, as secure enclaves depend on the low-level hardware features such as Virtualization-based security (VBS) and Intel VT-x.

Now let us set up Always Encrypted with secure enclaves on AWS. For this scenario, I’m using an Amazon EC2 m5.xlarge instance type for deploying the Host Guardian Service and an Amazon EC2 m5zn.metal instance for deploying SQL Server.

2.1. Configure the Host Guardian Service on Amazon EC2 HGS instance

Log into the Amazon EC2 HGS instance as an administrator and run the following steps using an elevated Windows PowerShell console:

  1. Install HostGuardianServiceRole by running the following command. This will restart the instance.
    Install-WindowsFeature -Name HostGuardianServiceRole -IncludeManagementTools -Restart
  1. After the reboot, log back into Amazon EC2 HGS instance as an administrator. Configure Host guardian service and its domain by running the following command using appropriate Password and Domain Name. This will restart the instance.
    $adminPassword = ConvertTo-SecureString -AsPlainText 'Password' -Force
    Install-HgsServer -HgsDomainName 'Domain Name' -SafeModeAdministratorPassword $adminPassword -Restart
  2. After the reboot, log back into Amazon EC2 HGS instance as an administrator. Configure host key attestation by running the following command.
    Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey

2.2. Set up Amazon EC2 SQL Server bare metal instance

Login to the Amazon EC2 SQL Server instance as an administrator. Run the following steps using an elevated Windows PowerShell console:

  1.  Install Windows feature HostGuardian by running the following command. This will install HostGuardian feature and bring up dialog presented in Figure 1; click Yes to restart the computer
    Enable-WindowsOptionalFeature -Online -FeatureName HostGuardian-All

    Restart Computer

    Figure 1. Restart Computer

  2. After the reboot, log back into Amazon EC2 SQL Server instance as an administrator. Run the following command to disable the requirement for secure boot VBS feature in Windows by changing the value of RequirePlatformSecurityFeatures in the registry.
    Set-ItemProperty -Path HKLM:\SYSTEM\CurrentControlSet\Control\DeviceGuard -Name RequirePlatformSecurityFeatures -Value 0
  3. Restart the Amazon EC2 SQL Server instance after the registry change to start VBS with new settings. Run the following command to restart the instance.
    Restart-Computer
  4. After the reboot, log back into Amazon EC2 SQL Server instance as an administrator. Run the following command to create hostkey certificate which will be used for attestation between Amazon EC2 HGS instance and Amazon EC2 SQL Server instance. Create hostkey certificate in your desired location.
    Set-HgsClientHostKey
    Get-HgsClientHostKey -Path $HOME\Desktop\hostkey.cer
  5. After running the above commands, as shown in Figure 2 hostkey certificate is created in the location specified.

    Creation of Host Key certificate

    Figure 2. Creation of Host Key certificate

  6. Copy and paste the hostkey certificate from Amazon EC2 SQL Server instance to desktop on the Amazon EC2 HGS instance.
  7. Run the following command to initialize the attestation on Amazon EC2 HGS instance.
    Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey
  8. Log back into Amazon EC2 SQL Server instance and run the following command to set up attestation between Amazon EC2 SQL Server instance and Amazon EC2 HGS instance.10.50.3.43 is the IP address of my Amazon EC2 HGS instance.
    Set-HgsClientConfiguration -AttestationServerUrl http://10.50.3.43/Attestation -KeyProtectionServerUrl http://10.50.3.43/KeyProtection/
Attestation Status

Figure 3. Attestation Status

After executing the above command, as shown in Figure 3, the attestation status should be passed. That signifies trust between SQL EC2 instance and HGS EC2 instance is successfully established.

2.3. Configure Always Encrypted with Secure Enclaves on SQL Server Database

  1. Log into Amazon EC2 SQL Server instance and connect to SQL Database Engine using SQL Server Management Studio(SSMS). Open a new query window and execute the following script to set enclave type to Virtualization Based Security (VBS), which is needed for Secure Enclaves:
    EXEC sys.sp_configure 'column encryption enclave type', 1;
    RECONFIGURE;
  2. Restart SQL Server instance by right-clicking on SQL instance name in Object Explorer and select Restart for the previous configuration change to take effect. Once the instance restarts, reconnect to it.
  3. Open new query window in SSMS and execute the following script to create a database named AESEDemo with test records to encrypt later.
    CREATE DATABASE [AESEDemo] 
    Go
    USE [AESEDemo] 
    GO 
    CREATE TABLE [dbo].[AESETest] 
    (
    [AESETestID] [int] IDENTITY(1,1) NOT NULL, 
    [SSN] [char](11) NOT NULL, 
    FullName] [nvarchar](50) NOT NULL 
    ) 
    USE [AESEDemo] 
    GO 
    INSERT INTO [dbo].[AESETest] 
    ([SSN],[FullName]) 
    VALUES('123-45-6789', N'Vikas'), 
    ('145-93-3242', N'Steve'), 
    ('134-12-4356', N'John');
  4. To use Always Encrypted, Column Master Key (CMK) and Column Encryption Key (CEK) needs to be created. Users who have access to these certificates can decrypt the data. CMK is used to protect one or more CEK’s, which are used to protect and encrypt data in the columns.
  1.  To create CMK, expand the Security tab in database AESEDemo and navigate to option called Always Encrypted Keys.
  1. As shown in Figure 4, right-click on Always Encrypted Keys and select New Column Master Key
SSMS Always Encrypted Keys option

Figure 4. SSMS Always Encrypted Keys option

  1. After selecting New Column Master Key, a new dialog will appear with an option to enter the name of the master key and option to select Key store as shown in Figure 5. For this tutorial we defined Master Key name as CMK and selected Windows Certificate Store – Current User as Key Store. Enable Allow enclave computations. Select Generate certificate and then choose Ok.
Creation of Column Master Key

Figure 5. Creation of Column Master Key

  1. Next step is to create Column Encryption Key. Expand the Security tab in database AESEDemo and navigate to option called Always Encrypted Keys. Under Always Encrypted option right-click on Column Encryption Keys to create CEK as shown in Figure 6.
Creation of Column Encryption Key

Figure 6. Creation of Column Encryption Key

After creating CMK and CEK’s successfully, it’s time to encrypt the data in the database using these keys.

  1. To encrypt the column data in a table, connect to new SSMS instance and connect to SQL server Database Engine with Always Encrypted option selected as shown in Figure 7.
Enable Always Encrypted Attestation Option on SSMS

Figure 7. Enable Always Encrypted Attestation Option on SSMS

  1. After connecting to SQL server instance, open a new query window and execute the following SQL statements to encrypt the SSN column. There are two encryption types – Deterministic and Randomized. Deterministic encryption will generate the same cipher-text value for the same clear-text value every time. Randomized will generate different cipher-text value every time we run a select statement. For this tutorial, I used Randomized encryption type.
    USE AESEDemo;
    GO
    ALTER TABLE [dbo].[AESETest]
    ALTER COLUMN [SSN] [char] (11) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
  2. To test whether data is encrypted or not, open a new SSMS instance and connect to SQL Database Engine without Always Encrypted option selected and execute the following query. As shown in Figure 8, the information in SSN Column is encrypted.

    select [AESETestID], [SSN], [FullName] from AESETest
Select Encrypted Data

Figure 8. Select Encrypted Data

  1. Execute the same query in SSMS instance with Always Encrypted option selected. As shown in Figure 9, the information in SSN Column is decrypted.
    select [AESETestID], [SSN], [FullName] from AESETest
Select Encrypted Data with Always Encrypted Option

Figure 9. Select Encrypted Data with Always Encrypted Option

Conclusion

In this tutorial, we have seen how to set up Always Encrypted with secure enclaves in Amazon EC2 bare metal instances. Always Encrypted protects the confidentiality of sensitive data and Always Encrypted with secure enclaves expands confidential computing capabilities of Always Encrypted by enabling in-place encryption and richer confidential queries.


AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.

Vikas Babu Gali

Vikas Babu Gali

Vikas Babu Gali is a Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. As a native of India, Vikas enjoys playing Cricket and spending time with his family and friends outdoors.