Enable Always Encrypted with Secure Enclaves in SQL Server Management Studio

By:   |   Comments (1)   |   Related: > Encryption


Problem

Microsoft released Always Encrypted with SQL Server 2016 to allow encrypted data "in flight" as well as "at rest".  You can find more information in this article SQL Server 2016 Always Encrypted. With SQL Server 2019, Microsoft introduced Always Encrypted with Secure Enclaves that provides extended functionality to the Always Encrypted feature and this tip covers how to set this up.

Solution

In this tip, I would like to show how to deploy Always Encrypted with Secure Enclaves on SQL Server 2019.

The steps are:

  1. Configure Host Guardian Service on your Windows server
  2. Configure Windows Server with SQL Server as a Guarded Host
  3. Configure Always Encrypted with Secure Enclaves on SQL Server and Database

My Configuration

  • Windows Server 2019 Datacenter (Computer Name: WinSrv2019Dtc)
  • Windows Server 2019 Datacenter with SQL Server 2019 Developer (Computer Name: SQLServer2019)
  • SQL Server Management Studio (SSMS) 18.4

Configure Host Guardian Service on your Windows server

NOTE: This machine cannot be joined to the domain before deploying Host Guardian Service!

First of all, we have to install a new feature HostGuardianServiceRole on our Windows server (without SQL Server - WinSrv2019Dtc) using PowerShell.

Open PowerShell (run as Administrator) and run the following:

Install-WindowsFeature -Name HostGuardianServiceRole -IncludeManagementTools -Restart 
powershell window

As soon as your Windows server is back online, we will need to install the Host Guardian Service, open PowerShell (run as Administrator) and run the following:

$AdminPassword = ConvertTo-SecureString -AsPlainText 'passwordofadministrator' -ForceInInstall-HgsServer -HgsDomainName 'domain.local' -SafeModeAdministratorPassword $AdminPassword -Restart
powershell window

As you can see, this command created a new Active Directory Domain and joined our HGS computer to the domain and installed a Failover Cluster.

In my case, the name of the cluster is HgsCluster50E07 in domainlab.local. 

failover cluster manager

After a reboot, we need to configure key attestation for your HGS. Open PowerShell (run as Administrator) and run the following.

Initialize-HgsAttestation -HgsServiceName 'hgs' -TrustHostKey 
powershell window

If finished successfully, you can check the results in the log file, in my case the file is: C:\Windows\Logs\HgsServer\200106094415\WINSRV2019STD

log entries

Configure Windows Server with SQL Server as a Guarded Host

Connect to the Windows server with SQL Server and install the Guarded Host feature, if Hyper-V is not installed on the machine it will be installed automatically with this feature:

Enable-WindowsOptionalFeature -Online -FeatureName HostGuardian -All
powershell window

Then restart the computer.

powershell window

After the restart, on the Windows server with SQL Server, you have to check if SQL Server is running on a virtual machine or physical machine without support UEFI Secure Boot, you must remove RequirePlatformSecurityFeatures in the registry, by using the following commands via PowerShell:

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

Then restart the Windows server to apply the changes.

Open PowerShell, generate the host key and export as a file to the desktop.

Set-HgsClientHostKeyntGet-HgsClientHostKey -Path $HOME\Desktop\hostkey.cer 
powershell window

Then run the following command where -Name parameter is the name of your SQL Server machine.

Add-HgsAttestationHostKey -Name SQLSERVER2019 -Path $HOME\Desktop\hostkey.cer
powershell window

Then sign in to your Windows server with SQL Server (SQLServer2019) and attest the machine to the HGS machine (WinSrv2019Dtcrv2019Dtc) using the following commands via PowerShell.

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

Configure Always Encrypted with Secure Enclaves on SQL Server and Database

Log on to your Windows server with SQL Server, open SQL Server Management Studio (SSMS) and enable column encryption enclave as follows.

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

Restart the SQL Server Engine service.

Then check if column encryption enclave type was enabled using the following query.

SELECT [name], [value], [value_in_use] 
FROM sys.configurations
WHERE [name] = 'column encryption enclave type';
sql query

I then restored the AdventureWorks2017 database to my SQL Server for testing.

Create Column Master Key

First create a column Master Key by expanding the database AdventureWorks2017 > Security > Always Encrypted Keys and right click and choose New Column Master Key.

ssms

I used the following settings:

  • Name: CMK1
  • KeyStore: Windows Certification Store – Local Machine
  • Check Allow enclave computations
  • Click OK
new column master key

Create Column Encryption Key

Expand database AdventureWorks2017 > Security > Always Encrypted Keys > Column Encryption Keys and right click and choose New Column Encryption Key.

ssms

I used the following settings:

  • Name: CEK1
  • Column master Key: CMK1
  • Click OK
new column encryption key

The results look as follows:

ssms

Connect and Encrypt Column

Connect to SQL Server via SSMS with these options:

connect to sql server

If we are successfully connected (with Always Encrypted enabled), we will able to encrypt a column. In my case, I have chosen Person.Password table and column PasswordSalt which is a varchar column.

USE [AdventureWorks2017];
GO
ALTER TABLE [Person].[Password]
ALTER COLUMN [PasswordSalt] [varchar](10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], 
ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH(ONLINE = ON);

Now, the column should be encrypted and you can confirm this with a new connection to SQL Server without Always Encrypted enabled and select data from the table [AdventureWorks2017].[Person].[Password].

sql query

Summary

As we can see, there are a couple of differences in configuring Always Encrypted with Secure Enclaves. It’s not easy to configure, honestly. We will probably rarely see this in a production environment – IMHO. I’m always excited about new features in SQL Server and in some cases this could be very helpful.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Filip Holub Filip Holub is a big SQL Server enthusiast from the Czech Republic with 5 years of DBA experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, June 3, 2021 - 4:11:08 AM - Simonn Back To Top (88792)
I want to implement Always encrypted but my concern now is how to I allow my stored procedures to insert data into encrypted columns? I know its possible if the commands are run from SSMS along with allowing parimiterization but this does not solve the issue of a .net page calling an sp to bulk import data from a flat file. I could change the import to happen from .net but that reduces performance severely thus want to avoid that at all costs as some import files we receive are flat files 50mb in size. Please tell me there is a solution for this?














get free sql tips
agree to terms