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 $keyVaultAccessTokenExample 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