Set-SqlColumnEncryption

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

Syntax

Set-SqlColumnEncryption
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-UseOnlineApproach]
   [-KeepCheckForeignKeyConstraints]
   [-MaxDowntimeInSeconds <Int32>]
   [-MaxIterationDurationInDays <Int32>]
   [-MaxDivergingIterations <Int32>]
   [-MaxIterations <Int32>]
   [-LogFileDirectory <String>]
   [-InputObject] <Database>
   [-Script]
   [<CommonParameters>]
Set-SqlColumnEncryption
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-UseOnlineApproach]
   [-KeepCheckForeignKeyConstraints]
   [-MaxDowntimeInSeconds <Int32>]
   [-MaxIterationDurationInDays <Int32>]
   [-MaxDivergingIterations <Int32>]
   [-MaxIterations <Int32>]
   [-LogFileDirectory <String>]
   [[-Path] <String>]
   [-Script]
   [<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.

Examples

Example 1: Apply target encryption settings to multiple columns using the offline approach.

PS C:\> $Ces1 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.Id -EncryptionType Deterministic -EncryptionKey MyCek
PS C:\> $Ces2 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.LastName -EncryptionType Randomized -EncryptionKey MyCek
PS C:\> $Ces3 = New-SqlColumnEncryptionSettings -ColumnName dbo.Student.FirstName -EncryptionType Plaintext
PS C:\> Set-SqlColumnEncryption -ColumnEncryptionSettings $Ces1,$Ces2,$Ces3 -LogFileDirectory .

This example applies the target encryption settings to three database columns. As a result, 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.

Example 2: Apply target encryption settings to multiple columns using the online approach.

PS C:\> $Ces1 += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.Id -EncryptionType Deterministic -EncryptionKey MyCek
PS C:\> $Ces2 += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.LastName -EncryptionType Randomized -EncryptionKey MyCek
PS C:\> $Ces3 += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.FirstName -EncryptionType Plaintext
PS C:\> Set-SqlColumnEncryption -ColumnEncryptionSettings $Ces1,$Ces2,$Ces3 -UseOnlineApproach -MaxDowntimeInSeconds 30 -LogFileDirectory .

This example applies the target encryption settings to three database columns using the online approach, which means the Student table will be unavailable for reads and writes for up to 30 seconds (the value specified using the MaxDowntimeInSeconds parameter.)

Required Parameters

-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
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
Accept pipeline input:True (ByValue)
Accept wildcard characters:False

Optional Parameters

-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
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
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
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
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
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
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
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
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
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.Database

Outputs

String