Enable Always Encrypted with Secure Enclaves in SQL Server Management Studio


By:   |   Updated: 2020-03-18   |   Comments   |   Related: More > 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


Last Updated: 2020-03-18


get scripts

next tip button



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.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools