Set-SqlColumnEncryption
Encrypts, decrypts, or re-encrypts specified columns in the database.
Syntax
Set-SqlColumnEncryption
-ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
[-UseOnlineApproach]
[-KeepCheckForeignKeyConstraints]
[-MaxDowntimeInSeconds <Int32>]
[-LockTimeoutInSeconds <Int32>]
[-MaxIterationDurationInDays <Int32>]
[-MaxDivergingIterations <Int32>]
[-MaxIterations <Int32>]
[-LogFileDirectory <String>]
[-InputObject] <Database>
[-Script]
[<CommonParameters>] Set-SqlColumnEncryption
-ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
[-UseOnlineApproach]
[-KeepCheckForeignKeyConstraints]
[-MaxDowntimeInSeconds <Int32>]
[-LockTimeoutInSeconds <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.
Note: This cmdlet is only available in PowerShell 5.
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.)
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 |
| 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 |
| 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 |
| 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