Set-​Sql​Column​Encryption

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

Syntax

Set-SqlColumnEncryption
   [-InputObject] <Database>
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-Script]
   [-InformationAction <ActionPreference>]
   [-InformationVariable <String>]
   [<CommonParameters>]
Set-SqlColumnEncryption
   [[-Path] <String>]
   -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]>
   [-Script]
   [-InformationAction <ActionPreference>]
   [-InformationVariable <String>]
   [<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

PS C:\ >$Ces = @()
PS C:\> $Ces += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.Id -EncryptionType Deterministic -EncryptionKey MyCek
PS C:\> $Ces += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.LastName -EncryptionType Randomized -EncryptionKey MyCek
PS C:\> $Ces += New-SqlColumnEncryptionSettings -ColumnName dbo.Student.FirstName -EncryptionType Plaintext
PS C:\> Set-SqlColumnEncryption $Ces ?Data Source=myServerName;Initial Catalog=myDatabaseName;Integrated Security=True -Verbose

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 that uses randomized encryption and the column encryption key, named MyCEK. The dbo.StudentFirstName column is not encrypted, if the column is initially encrypted, it is decrypted.

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

-InformationAction

Specifies how this cmdlet responds to an information event.

The acceptable values for this parameter are:

  • Continue
  • Ignore
  • Inquire
  • SilentlyContinue
  • Stop
  • Suspend
Type:ActionPreference
Aliases:infa
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-InformationVariable

Specifies an information variable.

Type:String
Aliases:iv
Position:Named
Default value:None
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

Outputs

String