New-SqlColumnEncryptionKeyEncryptedValue

Reference

Module:: SQLServer

Creates the encrypted value of a column encryption key.

Syntax

New-SqlColumnEncryptionKeyEncryptedValue
   [-TargetColumnMasterKeySettings] <SqlColumnMasterKeySettings>
   [[-ColumnMasterKeySettings] <SqlColumnMasterKeySettings>]
   [[-EncryptedValue] <String>]
   [-KeyVaultAccessToken <String>]
   [-ManagedHsmAccessToken <String>]
   [<CommonParameters>]

Description

The New-SqlColumnEncryptionKeyEncryptedValue cmdlet creates the encrypted value of a column encryption key. The returned encrypted value is a hexadecimal string.

The cmdlet supports two modes of operation:

If no encrypted value is specified, the cmdlet generates a new plaintext symmetric key and encrypts the key with the specified column master key.
If an encrypted value is specified, the cmdlet first decrypts the specified encrypted value and then re-encrypts the obtained plaintext key with the specified column master key. The cmdlet communicates with a key store holding the column master key. If the key is stored in Azure, you need to specify a valid authentication token for a key vault or a managed HSM holding the key. Alternatively, you can authenticate to Azure with Add-SqlAzureAuthenticationContext before calling this cmdlet.

Module requirements: version 21+ on PowerShell 5.1; version 22+ on PowerShell 7.x.

Examples

Example 1: Generate a key and encrypt it using a certificate

$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation 'CurrentUser' -certificateThumbprint 'f2260f28d909d21c642a3d8e0b45a830e79a1420'
New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings

Example 2: Generate a column encryption key value and encrypt it using a column master key that is a key stored in a key vault in Azure Key Vault.

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token

# Pass the token to the cmdlet. It will use the token to communicate with the key vault containing the column master key to sign the column master key metadata properties.
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl 'https://myvault.vault.azure.net/keys/CMK/4c05f1a41b12488f9cba2ea964b6a700' -AllowEnclaveComputations -KeyVaultAccessToken $keyVaultAccessToken

# Generate a column encryption key value and encrypt it with the column master key. Pass the access token, so that the cmdlet can communicate with Azure Key Vault.
New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings -KeyVaultAccessToken $keyVaultAccessToken

Example 3: Decrypt an existing encrypted key value, which was produced using a column master key that is a certificate. Re-encrypt the key value using a column master key that is a key 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

# Create a SqlColumnMasterKeySettings object referencing a certificate.
$currentCmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation 'CurrentUser' -certificateThumbprint 'f2260f28d909d21c642a3d8e0b45a830e79a1420'

# Create a SqlColumnMasterKeySettings object, referencing a column master key in a key vault in Azure Key Vault.
$targetCmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl 'https://myvault.vault.azure.net/keys/CMK/4c05f1a41b12488f9cba2ea964b6a700'

# Decrypt a column encryption key value using the current column master key and re-encrypt it with the new column master key. Pass the access token, so that the cmdlet can communicate with Azure Key Vault. 
New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $targetCmkSettings -ColumnMasterKeySettings $currentCmkSettings -KeyVaultAccessToken $keyVaultAccessToken -EncryptedValue '0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006200330039003900340035006200370031003100330037003700350032006400380061003100310033003900660035006200640036006400380066003700330038006600320033006200360032003000307925663D2C3E275DD272E15E606927DA4326F5735C2C8E84F91B9EFE44F503ED01C130984E83AF4513F8A4A8D0878D42364E958291AE25111A868D25B69FC5143EEC04131DA27D05F3442CB665ACB4BB3F6A7A9F07DBD5D212A772414A2CCA03BEBEB7BF0E22C644C715D739B983872AFB2D390229A0B5311BCA07E3C1D857EE8982320BBBE9382C960B9674E3CC3D618AD623D6A362BEAEF68B1B1BB49660DD643A4375A9285CD9EAA5B13BFE2792DA92025351E7B6067BA07B6178D03041F40F00D84326627094C9D6944DD912497B080058A529D2DA11C8D609604449714420B4E44ECD1EB26DEE18BF712146A51DD99A02E3D4EE692A503CF02F874497010772DE743DDFB2A74801AC9A94C876D1F93554B70CE0ECC437E7FC28BC11A08222977CDA807E256ED536C41700C631878226E513AFE1199A1DB4732F975AA09A1E75B8A19802AE018871A7A0AD5B1E29B942F30490EDABD310A4170B991EBCFDA2AFE43285D5406476204B381D8A33EEB0B967073B4C0127B1C7F0281AB310EE4B9A3C2D3EAB44A1F5D15D4739FFAEF6110ED4808446F6A05DBF4121B2B33A0AF5A457CD38F895B8F7ABDF792E3ADBC3AF55B1442625F88F80127D08DE9E4AC1BB2AAA46843A477135053CEEFA4327D8C999C16D8B49C225F34AD7588A5F9E93FB5532B1F1DC5AFB3CE23DDC8DC12327DD6B5985104D14F4A1BC0F61F0AACD'

Parameters

-ColumnMasterKeySettings

Specifies the SqlColumnMasterKeySettings object that this cmdlet uses to find where the column master key is stored.

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

-EncryptedValue

Specifies the existing encrypted value.

If you specify a value for this parameter, the cmdlet will first decrypt this value using the column master key referenced by the ColumnMasterKeySettings parameter and then re-encrypt it using the column master key referenced by the TargetColumnMasterKeySettings parameter.

Type:	String
Position:	2
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-KeyVaultAccessToken

Specifies an access token for key vaults in Azure Key Vault. Use this parameter if the column master key for encrypting or decrypting a symmetric column encryption key is stored in a key vault in Azure Key Vault.

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

-ManagedHsmAccessToken

Specifies an access token for managed HSMs in Azure Key Vault. Use this parameter if the column master key for encrypting or decrypting a symmetric column encryption key is stored in a managed HSM in Azure Key Vault.

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

-TargetColumnMasterKeySettings

Specifies the SqlColumnMasterKeySettings object that this cmdlet uses to determine where the column master key, to be used to encrypt the new encrypted value, is stored.

Type:	SqlColumnMasterKeySettings
Position:	0
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

Outputs

String