Set-SqlColumnEncryption

構文

ByObject

Set-SqlColumnEncryption
    [-InputObject] <Database>
    -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]
    [-DisableFallBackToClientSide]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [<CommonParameters>]

ByPath

Set-SqlColumnEncryption
    [[-Path] <String>]
    -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]
    [-DisableFallBackToClientSide]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [<CommonParameters>]

説明

例

Example 1: Apply the specified target encryption settings to three database columns.

$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.)

# 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 $keyVaultAccessToken

Example 3: Apply the specified target encryption settings to three database columns using the online approach.

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

パラメーター

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-ColumnEncryptionSettings

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

パラメーターのプロパティ

パラメーター セット

-DisableFallBackToClientSide

When specifying this parameter, the cmdlet would error out with an appropriate error message if the operation is ineligible for in-place mode instead of silently falling back to client-side mode of encryption.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-InputObject

Specifies the SQL database object, for which this cmdlet runs the operation.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-LogFileDirectory

If set, the cmdlet will create a log file in the specified directory.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-Script

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

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

-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.

パラメーターのプロパティ

パラメーター セット

CommonParameters

入力

Microsoft.SqlServer.Management.Smo.Database

出力

String

関連リンク

• SQLServer_Cmdlets