Set-SqlColumnEncryption

Encrypts, decrypts, or re-encrypts specified columns in the database.

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

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-AllowVerboseLogging

If set, the cmdlet will add verbose messages to the log file (if the 'LogFileDirectory' parameter is set) and retain the dacpac files used by the underlying libraries for performing the operation.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ColumnEncryptionSettings

Specifies an array of SqlColumnEncryptionSettings objects, each of which specifies the target encryption configuration for one column in the database.

Type:SqlColumnEncryptionSettings[]
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-EnclaveAttestationProtocol

Specifies the an enclave's 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 void 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.

Type:SqlConnectionAttestationProtocol
Aliases:AttestationProtocol
Accepted values:NotSpecified, AAS, None, HGS
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

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

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies the SQL database object, for which this cmdlet runs the operation.

Type:Database
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-KeepCheckForeignKeyConstraints

If set, check semantics (CHECK or NOCHECK) of foreign key constraints are preserved.

Otherwise, if not set, and if UseOnlineApproach is not set, foreign key constraints are always recreated with the NOCHECK option to minimize the impact on applications.

KeepCheckForeignKeyConstraints is valid only when UseOnlineApproach is set.

With the offline approach, the semantics of foreign key constraints is always preserved.

Type:SwitchParameter
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-KeyVaultAccessToken

Specifies an access token for key vaults in Azure Key Vault. Use this parameter if any of the column master keys protecting the columns to be encrypted, decrypted, or re-encrypted, are stored in key vaults in Azure Key Vault.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-LockTimeoutInSeconds

Specifies the maximum time (in seconds) the cmdlet will wait for database locks that are needed to begin the last catch-up iteration. A value of -1 (default) indicates no timeout period (that is, wait forever). A value of 0 means to not wait at all. When a wait for a lock exceeds the time-out value, an error is returned. Valid only if UseOnlineApproach is set.

Type:Int32
Position:Named
Default value:-1
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-LogFileDirectory

If set, the cmdlet will create a log file in the specified directory.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ManagedHsmAccessToken

Specifies an access token for managed HSMs in Azure Key Vault. Use this parameter if any of the column master keys protecting the columns to be encrypted, decrypted, or re-encrypted, are stored in managed HSMs in Azure Key Vault.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MaxDivergingIterations

Specifies the maximum number of consecutive catch-up iterations, where the number of processed rows increases. When this limit is reached, the cmdlet assumes that it will not be able to catch up with the changes made in the source table, and it aborts the operation and re-creates the original state of the database. Valid only if UseOnlineApproach is set. Must be less than the value of MaxIterations.

Type:Int32
Position:Named
Default value:5
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MaxDowntimeInSeconds

Specifies the maximum time (in seconds), during which the source table will not be available for reads and writes. Valid only if UseOnlineApproach is set.

Type:Int32
Position:Named
Default value:1800
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MaxIterationDurationInDays

Specifies the maximum time (in days) of seeding or a single catch-up iteration. If seeding or any catch-up iteration takes more than the specified value, the cmdlet aborts the operation and re-creates the original state of the database. Valid only if UseOnlineApproach is set.

Type:Int32
Position:Named
Default value:3
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MaxIterations

Specifies the maximum number of iterations in the catch-up phase. When this limit is reached, the cmdlet aborts the operation and recreates the original state of the database. Valid only if UseOnlineApproach is set.

Type:Int32
Position:Named
Default value:100
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Path

Specifies the path of the SQL database, for which this cmdlet runs the operation. If you do not specify a value for this parameter, the cmdlet uses the current working location.

Type:String
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Script

Indicates that this cmdlet returns a Transact-SQL script that performs the task that this cmdlet performs.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-UseOnlineApproach

If set, the cmdlet will use the online approach, to ensure the database is available to other applications for both reads and writes for most of the duration of the operation.

Otherwise, the cmdlet will lock the impacted tables, making them unavailable for updates for the entire operation. The tables will be available for reads.

Type:SwitchParameter
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.Database

Outputs

String