Set-SqlColumnEncryption

Syntax

Set-SqlColumnEncryption
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-UseOnlineApproach]
   [-KeepCheckForeignKeyConstraints]
   [-MaxDowntimeInSeconds <Int32>]
   [-KeyVaultAccessToken <String>]
   [-ManagedHsmAccessToken <String>]
   [-LockTimeoutInSeconds <Int32>]
   [-MaxIterationDurationInDays <Int32>]
   [-MaxDivergingIterations <Int32>]
   [-MaxIterations <Int32>]
   [-EnclaveAttestationProtocol <SqlConnectionAttestationProtocol>]
   [-EnclaveAttestationURL <String>]
   [-LogFileDirectory <String>]
   [-AllowVerboseLogging]
   [-InputObject] <Database>
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]

Set-SqlColumnEncryption
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-UseOnlineApproach]
   [-KeepCheckForeignKeyConstraints]
   [-MaxDowntimeInSeconds <Int32>]
   [-KeyVaultAccessToken <String>]
   [-ManagedHsmAccessToken <String>]
   [-LockTimeoutInSeconds <Int32>]
   [-MaxIterationDurationInDays <Int32>]
   [-MaxDivergingIterations <Int32>]
   [-MaxIterations <Int32>]
   [-EnclaveAttestationProtocol <SqlConnectionAttestationProtocol>]
   [-EnclaveAttestationURL <String>]
   [-LogFileDirectory <String>]
   [-AllowVerboseLogging]
   [[-Path] <String>]
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]

Description

The Set-SqlColumnEncryption cmdlet encrypts, decrypts, or re-encrypts specified database columns using the Always Encrypted feature.

The cmdlet accepts an array of 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.

The cmdlet communicates with key stores that hold columns master keys. If any column master key protecting the columns to be encrypted, decrypted, or re-encrypted, is stored in Azure, you need to specify a valid authentication token for a key vault or a managed HSM holding the key. Alternatively, you can authenticate to Azure with Add-SqlAzureAuthenticationContext before calling this cmdlet.

Module requirements: version 21+ on PowerShell 5.1; version 22+ on PowerShell 7.x.

Examples

Example 1: Apply the specified target encryption settings to three database columns.

In this example, the dbo.Student.Id column is encrypted using deterministic encryption and the column encryption key, named MyCEK.

The dbo.Student.LastName column is encrypted using randomized encryption and the column encryption key, named MyCEK.

The dbo.StudentFirstName column is not encrypted (if the column is initially encrypted, it gets decrypted).

The example uses the offline approach, which means the Student table will remain unavailable for updates throughout the operation. Assume the column master key, protecting MyCEK, is not stored in Azure Key Vault.

$ces1 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.Id'        -EncryptionType 'Deterministic' -EncryptionKey 'MyCek'
$ces2 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.LastName'  -EncryptionType 'Randomized'    -EncryptionKey 'MyCek'
$ces3 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.FirstName' -EncryptionType 'Plaintext'
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces1,$ces2,$ces3 -LogFileDirectory .

Example 2: Apply the specified target encryption settings to the three database columns (column master key is stored in Azure Key Vault.)

This example is similar to the one above; only difference is that the column master key protecting MyCEK is stored in Azure Key Vault.

# Connect to Azure account.
Import-Module Az.Accounts -MinimumVersion 2.2.0
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token

$ces1 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.Id'        -EncryptionType 'Deterministic' -EncryptionKey 'MyCek'
$ces2 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.LastName'  -EncryptionType 'Randomized'    -EncryptionKey 'MyCek' 
$ces3 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.FirstName' -EncryptionType 'Plaintext'

# Pass the token to the cmdlet. It will use the token to communicate with Azure Key Vault to obtain the plaintext value of the column encryption key.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces1,$ces2,$ces3 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Example 3: Apply the specified target encryption settings to three database columns using the online approach.

In this example Student table will be unavailable for reads and writes for up to 30 seconds (the value specified using the MaxDowntimeInSeconds parameter.) Assume the column master key, protecting MyCEK, is stored outside of Azure (passing an Azure token is not required).

$ces1 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.Id'        -EncryptionType 'Deterministic' -EncryptionKey 'MyCek'
$ces2 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.LastName'  -EncryptionType 'Randomized'    -EncryptionKey 'MyCek'
$ces3 = New-SqlColumnEncryptionSettings -ColumnName 'dbo.Student.FirstName' -EncryptionType 'Plaintext'
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces1,$ces2,$ces3 -UseOnlineApproach -MaxDowntimeInSeconds 30 -LogFileDirectory .

Example 4: Apply target encryption settings to multiple columns using in-place encryption.

$ces1 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.Id        -EncryptionType 'Randomized' -EncryptionKey 'CEK1' 
$ces2 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.LastName  -EncryptionType 'Randomized' -EncryptionKey 'CEK1' 
$ces3 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.FirstName -EncryptionType 'Randomized' -EncryptionKey 'CEK1'
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces1,$ces2,$ces3 -LogFileDirectory . -EnclaveAttestationProtocol 'AAS' -EnclaveAttestationURL 'https://enclavedemoattest.weu.attest.azure.net'

This example applies the target encryption settings to the database columns using in-place encryption, provided all prerequisites for in-place encryption are met, that is, the database has an enclave enabled and the keys used in cryptographic operations, which the cmdlet triggers, are enclave-enabled.

Parameters

-AccessToken

-AllowVerboseLogging

-ColumnEncryptionSettings

-EnclaveAttestationProtocol

-EnclaveAttestationURL

-Encrypt

-HostNameInCertificate

-InputObject

-KeepCheckForeignKeyConstraints

-KeyVaultAccessToken

-LockTimeoutInSeconds

-LogFileDirectory

-ManagedHsmAccessToken

-MaxDivergingIterations

-MaxDowntimeInSeconds

-MaxIterationDurationInDays

-MaxIterations

-Path

-ProgressAction

-Script

-TrustServerCertificate

-UseOnlineApproach

Inputs

Microsoft.SqlServer.Management.Smo.Database

Outputs

String

Related Links

• SQLServer_Cmdlets