Always Encrypted Token-based authorization with PowerShell

[Guest PieterVanhove]

Some Always Encrypted cmdlets in the SqlServer PowerShell module need to communicate with key stores holding SQL Server column master keys. When a key store is an Azure service, e.g., Azure Key Vault, the cmdlet needs to present an Azure AD-issued authorization token when connecting to the service. Currently, the SqlServer PowerShell module provides its own cmdlet for authenticating to Azure AD - Add-SqlAzureAuthenticationContext, which stores the acquired tokens in a token cache internal to the SqlServer module. The cmdlet suffers from problems like limited authentication methods and currently it supports only Azure Key Vault, etc.

 

 

 

Until now!

 

 

 

 

The release of SqlServer PowerShell Module 22 enables our customers to take advantage of OAuth2 token-based authorization for Always Encrypted with secure enclaves.

 

 

 

 

The new improvements address the above problems of the Add-SqlAzureAuthenticationContext cmdlet by allowing customers to pass tokens for Azure key store services, acquired with Connect-AzAccount, to the SqlServer cmdlets. Each SqlServer cmdlet that communicates with Azure key stores services is extended by adding the following parameters, each of which corresponds to an Azure key store service.

 

• KeyVaultAccessToken - specifies an access token, the cmdlet can use to communicate with key vaults in Azure Key Vault.
  
• ManagedHSMAccessToken - specifies an access token, the cmdlet can use to communicate with managed HSMs in Azure Key Vault
  

Example

 

 

 

 

# Connect to Azure account.

Import-Module SQLServer -MinimumVersion 22.0.59

Import-Module Az.Accounts -MinimumVersion 2.2.0

Connect-AzAccount

 

Set-StrictMode -Version Latest

 

# Obtain access tokens for services, Always Encrypted cmdlets need to use

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

$managedHSMAccessToken = (Get-AzAccessToken -ResourceUrl "https://managedhsm.azure.net").Token

 

# Pass the token to an Always Encrypted cmdlet in SqlServer.

Set-SqlColumnEncryption -ColumnEncryptionSettings $ces1,$ces2,$ces3 LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken - ManagedHSMAccessToken $managedHSMAccessToken

 

 

 

 

 

 

Remark: The Add-SqlAzureAuthenticationContext cmdlet is continued to be supported.

 

 

 

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...