Set-SqlColumnEncryption

SYNOPSIS

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

SYNTAX

ByObject

Set-SqlColumnEncryption -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]> [-InputObject] <Database>
 [-Script] [-InformationAction <ActionPreference>] [-InformationVariable <String>] [<CommonParameters>]

ByPath

Set-SqlColumnEncryption -ColumnEncryptionSettings <SqlColumnEncryptionSettings[]> [[-Path] <String>] [-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.

PARAMETERS

-ColumnEncryptionSettings

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

Type: SqlColumnEncryptionSettings[]
Parameter Sets: (All)
Aliases: 

Required: True
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
Parameter Sets: ByObject
Aliases: 

Required: True
Position: 1
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-Script

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

Type: SwitchParameter
Parameter Sets: (All)
Aliases: 

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

-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
Parameter Sets: (All)
Aliases: infa

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

-InformationVariable

Specifies an information variable.

Type: String
Parameter Sets: (All)
Aliases: iv

Required: False
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
Parameter Sets: ByPath
Aliases: 

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

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

INPUTS

OUTPUTS

String

NOTES

Configure Always Encrypted using PowerShell

SQL Server Cmdlets