In-place encryption with the Sqlserver PowerShell Module

[Guest PieterVanhove]

Always Encrypted with secure enclaves supports in-place cryptographic operations on database columns inside a secure enclave in SQL Server and Azure SQL Database. In-place encryption eliminates the need to move the data for cryptographic operations outside of the database, making such operations secure, faster and more reliable.

 

Currently, customers can trigger in-place encryption using T-SQL. PowerShell did not leverage in-place-encryption. Instead, our SQL tools had to load the data on the client-side to perform cryptographic operations.

 

 

 

Until now!

 

The release of SqlServer PowerShell Module 22 enables our customers to take advantage of in-place encryption for Always Encrypted with secure enclaves.

 

 

 

 

The Set-SqlColumnEncryption cmdlet in the SqlServer PowerShell module encrypts, decrypts, or re-encrypts specified database columns. The cmdlet accepts an array of New-SqlColumnEncryptionSettings objects, each of which specifies the target encryption configuration for one column in the database. The cmdlet will encrypt, decrypt, or re-encrypt each specified column, depending on what the current encryption configuration of the column is and the specified target encryption settings.

 

See Configure column encryption using Always Encrypted with PowerShell - SQL Server | Microsoft Docs for more details.

 

 

 

To trigger in-place cryptographic operations using an enclave, Set-SqlColumnEncryption must use a database connection using a connection string with the Attestation Protocol and optionally the Attestation URL keywords. We have added two new optional cmdlet parameters, one for each attestation keyword. The cmdlet will use the values of these parameters to open a new database connection with Always Encrypted with secure enclaves and optionally attestation enabled.

 

 

 

New parameters for Set-SqlColumnEncryption

 

 

-EnclaveAttestationProtocol

 

Specifies an enclave attestation protocol for Always Encrypted with secure enclaves.

 

This parameter is required for the cmdlet to perform cryptographic operations in-place – inside a server-side secure enclave – to avoid the expense of downloading and uploading the data. Note that in-place encryption has other pre-requisites: your database must have an enclave configured and you need to use enclave-enabled cryptographic keys.

 

Possible values are: AAS (Azure Attestation Service), HGS (Host Guardian Service) or None.

 

• Attesting VBS enclaves in SQL Server 2019 (15.x) and later requires HGS. You can also use VBS enclaves without attestation (the latest client drivers are required).
  
• With Intel SGX enclaves (in DC-series databases) in Azure SQL Database, attestation is mandatory and it requires Microsoft Azure Attestation.
  
• VBS enclaves in Azure SQL Database (in preview) currently don't support attestation.
  

 

-EnclaveAttestationUrl

 

Specifies an enclave attestation URL for in-place encryption when using Always Encrypted with secure enclaves.

Required if EnclaveAttestationProtocol is set to “AAS” or “HGS”.

 

 

 

Example - Encrypt columns using VBS enclaves

 

 

 

 

 

 

# Import modules

Import-Module "SqlServer" -MinimumVersion 22.0.59

Import-Module Az.Accounts -MinimumVersion 2.2.0

 

Set-StrictMode -Version Latest

 

#Connect to Azure

Connect-AzAccount

 

# Select subscription, if you have more than one..

Select-AzSubscription -Subscription <your_subscripion>

 

# Obtain an access token for key vaults.

$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token

 

# Obtain an access token to access the database

$dbAccessToken = Get-AzAccessToken -ResourceUrl https://database.azure.net

 

# Connect to your database using the AccessToken.

$serverName = "<servername>.database.windows.net"

$databaseName = "<DatabaseName>"

$database = Get-SqlDatabase -ServerInstance $serverName -Database $databaseName -AccessToken $dbAccessToken

 

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.

$ces = @()

$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Employees.SSN" -EncryptionType "Randomized" -EncryptionKey "CEK"

$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey "CEK"

Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -EnclaveAttestationProtocol “None” -KeyVaultAccessToken $keyVaultAccessToken

 

 

 

 

 

 

 

 

Next steps

 

 

For more information and to get started with Always Encrypted with secure enclaves in Azure SQL Database, see:

 

• Sqlserver PowerShell Module 22.0.59 or later
  
• Always Encrypted with secure enclaves - documentation
  
• Tutorial: Getting started with Always Encrypted with secure enclaves in Azure SQL Database
  

 

We’d love to hear your feedback – please contact us at alwaysencryptedpg@microsoft.com

 

Continue reading...