Rodar as chaves do Always Encrypted usando o PowerShell

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este artigo fornece as etapas para girar chaves para Always Encrypted usando o módulo SqlServer PowerShell. Para obter informações sobre como começar a usar o módulo SqlServer PowerShell para Always Encrypted, consulte Configure Always Encrypted using PowerShell.

A rotação de chaves Always Encrypted é o processo de substituir uma chave existente por uma nova. Pode ser necessário efetuar a rotação de uma chave caso esta seja comprometida, ou para cumprir as políticas da sua organização ou os regulamentos de conformidade que exigem a rotação regular de chaves criptográficas.

Always Encrypted usa dois tipos de chaves, portanto, há dois fluxos de trabalho de rotação de chaves de alto nível: rotação de chaves-mãe de coluna e rotação de chaves de criptografia de coluna.

• Rotação da chave de criptografia de coluna - envolve a descriptografia de dados criptografados com a chave atual e a recriptografia dos dados usando a nova chave de criptografia de coluna. Como a rotação de uma chave de criptografia de coluna requer acesso às chaves e ao banco de dados, a rotação da chave de criptografia de coluna só pode ser executada sem separação de funções.
• Rotação da chave mestra de coluna - envolve a descriptografia de chaves de criptografia de coluna protegidas com a chave mestra de coluna atual, criptografando-as novamente usando a nova chave mestra de coluna e atualizando os metadados para ambos os tipos de chaves. A rotação da chave mestra de coluna pode ser concluída com ou sem separação de funções (ao usar o módulo SqlServer PowerShell).

Rotação da Chave Mestra de Coluna sem Separação de Funções

O método descrito nesta secção para alternar uma chave mestra da coluna não permite a separação de funções entre um administrador de segurança e um DBA. Alguns dos passos seguintes combinam operações sobre as chaves físicas com operações sobre metadados de chaves. Use este fluxo de trabalho se a sua organização utilizar o modelo DevOps, ou quando a sua base de dados estiver alojada na cloud e o objetivo principal for restringir administradores cloud (mas não DBAs locais) o acesso a dados sensíveis. Não utilize este método se os potenciais adversários incluírem DBAs, ou se os DBAs não deverem ter acesso a dados sensíveis.

Rotação de uma chave mestra de coluna sem divisão de funções (exemplo com certificado Windows)

O script abaixo é um exemplo de ponta a ponta que substitui uma chave mestra de coluna existente (CMK1) por uma nova chave mestra de coluna (CMK2).

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[string]$ServerName = '<server name>',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$DatabaseName = '<database name>',

	[Parameter(Mandatory = $false)]
	[string]$CertificateSubject = 'AlwaysEncryptedCertNew',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$OldCmkName = 'CMK1',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$NewCmkName = 'CMK2'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

Write-Host '[AE] Step 1: Creating a new self-signed certificate for the new CMK'
$cert = New-SelfSignedCertificate `
	-Subject $CertificateSubject `
	-CertStoreLocation 'Cert:CurrentUser\My' `
	-KeyExportPolicy Exportable `
	-Type DocumentEncryptionCert `
	-KeyUsage KeyEncipherment `
	-KeySpec KeyExchange `
	-KeyLength 2048
Write-Host "[AE] Certificate created with thumbprint: $($cert.Thumbprint)"

Write-Host "[AE] Step 2: Connecting to SQL Server '$ServerName' / Database '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect to '$ServerName' / '$DatabaseName'. Verify instance, database, and local permissions."
	throw
}

Write-Host "[AE] Step 3: Validating that old CMK '$OldCmkName' exists"
$oldCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $OldCmkName }
if (-not $oldCmk) {
	throw "Old CMK '$OldCmkName' does not exist. Cannot rotate."
}
Write-Host "[AE] Old CMK '$OldCmkName' found."

Write-Host "[AE] Step 4: Creating CMK settings for new certificate"
$newCmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation 'CurrentUser' -Thumbprint $cert.Thumbprint

Write-Host "[AE] Step 5: Registering new CMK '$NewCmkName' in the database"
$newCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $NewCmkName }
if ($newCmk) {
	Write-Host "[AE] New CMK '$NewCmkName' already exists. Skipping creation."
}
else {
	New-SqlColumnMasterKey -Name $NewCmkName -InputObject $database -ColumnMasterKeySettings $newCmkSettings | Out-Null
	Write-Host "[AE] New CMK '$NewCmkName' registered."
}

Write-Host "[AE] Step 6: Initiating CMK rotation from '$OldCmkName' to '$NewCmkName'"
Write-Host "[AE] (This re-encrypts all associated CEKs under the new CMK...)"
Invoke-SqlColumnMasterKeyRotation `
	-SourceColumnMasterKeyName $OldCmkName `
	-TargetColumnMasterKeyName $NewCmkName `
	-InputObject $database
Write-Host "[AE] Rotation initiated."

Write-Host "[AE] Step 7: Completing the CMK rotation"
Complete-SqlColumnMasterKeyRotation `
	-SourceColumnMasterKeyName $OldCmkName `
	-InputObject $database
Write-Host "[AE] Rotation completed."

Write-Host "[AE] Step 8: Verifying CEKs are now under '$NewCmkName'"
$query = "SELECT name FROM sys.column_encryption_keys WHERE name = N'$($NewCmkName)'"
$rotatedCeks = Invoke-SqlCmd -ServerInstance $ServerName -Database $DatabaseName -Query $query -TrustServerCertificate  -ErrorAction SilentlyContinue
if ($rotatedCeks) {
	$cekCount = @($rotatedCeks).Count
	if ($cekCount -eq 0) { $cekCount = 1 }
	Write-Host "[AE] Verified: $cekCount CEK(s) now under '$NewCmkName'"
	@($rotatedCeks) | ForEach-Object { Write-Host "  - $($_.name)" }
}

Write-Host "[AE] Step 9: Removing old CMK metadata '$OldCmkName'"
Remove-SqlColumnMasterKey -Name $OldCmkName -InputObject $database
Write-Host "[AE] Old CMK '$OldCmkName' removed."

Write-Host '[AE] ========== Rotation Complete =========='
Write-Host "[AE] Old CMK: $OldCmkName (deleted)"
Write-Host "[AE] New CMK: $NewCmkName (active)"
Write-Host '[AE] All CEKs have been re-encrypted under the new CMK.'

Rotação da Chave Mestra de Coluna com Separação de Funções

O fluxo de trabalho de rotação de chave mestra de coluna descrito nesta seção garante a separação entre um administrador de segurança e um DBA.

Parte 1: DBA

Um DBA recupera metadados sobre a chave mestra de coluna a ser rodada e sobre as chaves de criptografia de coluna afetadas que estão associadas à chave mestra de coluna atual. O DBA compartilha todas essas informações com um administrador de segurança.

Parte 2: Administrador de Segurança

O administrador de segurança gera uma nova chave mestra de coluna, criptografa novamente as chaves de criptografia de coluna afetadas com a nova chave mestra de coluna e compartilha as informações sobre a nova chave mestra de coluna, bem como o conjunto de novos valores criptografados para as chaves de criptografia de coluna afetadas, com o DBA.

Parte 3: DBA

O DBA cria metadados para a nova chave mestra de coluna e atualiza os metadados das chaves de criptografia de coluna afetadas, para adicionar o novo conjunto de valores criptografados. Nesta etapa, o DBA também se coordena com os administradores dos aplicativos que consultam colunas de criptografia, que garantem que o aplicativo possa acessar a nova chave mestra de coluna. Depois que todos os aplicativos estiverem configurados para usar a nova chave mestra de coluna, o DBA removerá o conjunto antigo de valores criptografados e os metadados da chave mestra de coluna antiga.

Girando uma chave mestra de coluna com separação de funções (exemplo de certificado do Windows)

O script abaixo é um exemplo de ponta a ponta para gerar uma nova chave mestra de coluna que está certificada no Repositório de Certificados do Windows, alternando uma chave mestra de coluna existente (atual) para substituí-la pela nova. O script supõe que o banco de dados de destino contém a chave mestra da coluna, chamada CMK1 (a ser rodada), que criptografa algumas chaves de criptografia de coluna.

Parte 1: DBA

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$ServerName = '<server name>',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$DatabaseName = '<database name>',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$OldCmkName = 'CMK2',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$OutputFolder = 'C:\temp'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

Write-Host "[CEK Export] Starting CMK and CEK data export"

# Validate output folder
if (-not (Test-Path -Path $OutputFolder -PathType Container)) {
	Write-Host "[CEK Export] Creating output folder: $OutputFolder"
	New-Item -Path $OutputFolder -ItemType Directory | Out-Null
}

# Connect to database
Write-Host "[CEK Export] Connecting to '$ServerName' / '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;TrustServerCertificate=True;Connection Timeout=30"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect to '$ServerName' / '$DatabaseName'."
	throw
}

# Retrieve old CMK
Write-Host "[CEK Export] Retrieving CMK '$OldCmkName'"
$oldCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $OldCmkName }
if (-not $oldCmk) {
	throw "CMK '$OldCmkName' not found in database '$DatabaseName'."
}

# Export CMK metadata using fixed text file name
$cmkFile = Join-Path $OutputFolder "oldcmkdata.txt"
Write-Host "[CEK Export] Exporting CMK metadata to: $cmkFile"
"CMKName|KeyStoreProviderName|KeyPath" | Set-Content -Path $cmkFile -Encoding UTF8
"$OldCmkName|$($oldCmk.KeyStoreProviderName)|$($oldCmk.KeyPath)" | Add-Content -Path $cmkFile -Encoding UTF8
Write-Host "[CEK Export]   ✓ CMK metadata exported"

# Discover and export CEKs using fixed text file name
Write-Host "[CEK Export] Discovering CEKs associated with '$OldCmkName'"
$ceks = Get-SqlColumnEncryptionKey -InputObject $database
$cekFile = Join-Path $OutputFolder "oldcekvalues.txt"
"CEKName|CEKEncryptedValue|HasMultipleEncryptedValues" | Set-Content -Path $cekFile -Encoding UTF8

$exportedCount = 0
$multiValueCount = 0

foreach ($cek in $ceks) {
	if (-not $cek.ColumnEncryptionKeyValues) {
		continue
	}

	# Check if this CEK has multiple encrypted values
	if ($cek.ColumnEncryptionKeyValues.Count -gt 1) {
		# CEK has multiple encrypted values - check if any reference the old CMK
		$refersToOldCmk = $cek.ColumnEncryptionKeyValues | Where-Object { $_.ColumnMasterKeyName -eq $OldCmkName }
		if ($refersToOldCmk) {
			Write-Warning "CEK '$($cek.Name)' has $($cek.ColumnEncryptionKeyValues.Count) encrypted values. One references '$OldCmkName'. This CEK cannot be rotated automatically."
			"$($cek.Name)|MULTIPLE_ENCRYPTED_VALUES|True" | Add-Content -Path $cekFile -Encoding UTF8
			$multiValueCount++
		}
	}
	else {
		# CEK has single encrypted value - check if it references the old CMK
		if ($cek.ColumnEncryptionKeyValues[0].ColumnMasterKeyName -eq $OldCmkName) {
			$encryptedValueHex = "0x" + -join ($cek.ColumnEncryptionKeyValues[0].EncryptedValue | ForEach-Object { $_.ToString("X2") })
			"$($cek.Name)|$encryptedValueHex|False" | Add-Content -Path $cekFile -Encoding UTF8
			$exportedCount++
		}
	}
}

Write-Host "[CEK Export]   ✓ CEK encrypted values exported"
Write-Host "[CEK Export]     - Exported: $exportedCount CEK(s)"
if ($multiValueCount -gt 0) {
	Write-Warning "      - Multi-valued CEKs (manual review needed): $multiValueCount"
}

Write-Host "[CEK Export] ===== Export Complete ====="
Write-Host "[CEK Export] CMK Metadata:   $cmkFile"
Write-Host "[CEK Export] CEK Values:     $cekFile"

Parte 2: Administrador de Segurança

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$ShareFolder = 'C:\Temp\',

    [Parameter(Mandatory = $false)]
    [ValidateSet('CurrentUser', 'LocalMachine')]
    [string]$StoreLocation = 'CurrentUser',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$CertificateSubject = 'AlwaysEncryptedCert'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

function Import-DelimitedTextFile {
    param(
        [Parameter(Mandatory = $true)] [string]$Path,
        [Parameter(Mandatory = $true)] [string[]]$RequiredColumns
    )

    if (-not (Test-Path -Path $Path -PathType Leaf)) {
        throw "Required file not found: $Path"
    }

    $raw = Get-Content -Path $Path -Raw
    if ([string]::IsNullOrWhiteSpace($raw)) {
        throw "File is empty: $Path"
    }

    $delimiter = if ($raw -match '\|') { '|' } else { ',' }
    $rows = @(Import-Csv -Path $Path -Delimiter $delimiter)
    if ($rows.Count -eq 0) {
        throw "No data rows found in file: $Path"
    }

    $first = $rows[0]
    $RequiredColumns | ForEach-Object {
        if (-not $first.PSObject.Properties[$_]) {
            throw "Missing required column '$_' in file: $Path"
        }
    }

    return $rows
}

if (-not (Test-Path -Path $ShareFolder -PathType Container)) {
    throw "Share folder does not exist: $ShareFolder"
}

$oldCmkDataFile = Join-Path $ShareFolder 'oldcmkdata.txt'
$oldCekValuesFile = Join-Path $ShareFolder 'oldcekvalues.txt'
$newCmkDataFile = Join-Path $ShareFolder 'newcmkdata.txt'
$newCekValuesFile = Join-Path $ShareFolder 'newcekvalues.txt'

Write-Host "[AE] Reading old CMK data from '$oldCmkDataFile'"
$oldCmkDataRows = Import-DelimitedTextFile -Path $oldCmkDataFile -RequiredColumns @('KeyStoreProviderName', 'KeyPath')
$oldCmkData = $oldCmkDataRows[0]

Write-Host "[AE] Reading old CEK values from '$oldCekValuesFile'"
$oldCekValues = Import-DelimitedTextFile -Path $oldCekValuesFile -RequiredColumns @('CEKName', 'CEKEncryptedValue')

Write-Host "[AE] Finding or creating certificate '$CertificateSubject' in $StoreLocation\\My"
$certPath = "Cert:$StoreLocation\My"
$cert = Get-ChildItem -Path $certPath |
    Where-Object { $_.Subject -eq "CN=$CertificateSubject" } |
    Sort-Object NotAfter -Descending |
    Select-Object -First 1

if (-not $cert) {
    $cert = New-SelfSignedCertificate `
        -Subject $CertificateSubject `
        -CertStoreLocation $certPath `
        -KeyExportPolicy Exportable `
        -Type DocumentEncryptionCert `
        -KeyUsage DataEncipherment `
        -KeySpec KeyExchange
}

Write-Host '[AE] Building CMK settings'
$oldCmkSettings = New-SqlColumnMasterKeySettings `
    -KeyStoreProviderName $oldCmkData.KeyStoreProviderName `
    -KeyPath $oldCmkData.KeyPath

$newCmkSettings = New-SqlCertificateStoreColumnMasterKeySettings `
    -CertificateStoreLocation $StoreLocation `
    -Thumbprint $cert.Thumbprint

Write-Host "[AE] Re-encrypting CEK values and writing '$newCekValuesFile'"
"CEKName|CEKEncryptedValue" | Set-Content -Path $newCekValuesFile -Encoding UTF8

$oldCekValues | ForEach-Object {
    $newValue = New-SqlColumnEncryptionKeyEncryptedValue `
        -TargetColumnMasterKeySettings $newCmkSettings `
        -ColumnMasterKeySettings $oldCmkSettings `
        -EncryptedValue $_.CEKEncryptedValue

    "$($_.CEKName)|$newValue" | Add-Content -Path $newCekValuesFile -Encoding UTF8
}

Write-Host "[AE] Writing new CMK data to '$newCmkDataFile'"
"KeyStoreProviderName|KeyPath" | Set-Content -Path $newCmkDataFile -Encoding UTF8
"$($newCmkSettings.KeyStoreProviderName)|$($newCmkSettings.KeyPath)" | Add-Content -Path $newCmkDataFile -Encoding UTF8

Write-Host '[AE] Completed successfully'
Write-Host "[AE] Output files: $newCmkDataFile , $newCekValuesFile"

Parte 3: DBA

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$ServerName = '<server name>',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$DatabaseName = '<database name>',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$OldCmkName = 'CMK1',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$NewCmkName = 'CMK2',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$InputFolder = 'C:\temp'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

function Import-DelimitedTextFile {
    param(
        [Parameter(Mandatory = $true)] [string]$Path,
        [Parameter(Mandatory = $true)] [string[]]$RequiredColumns
    )

    if (-not (Test-Path -Path $Path -PathType Leaf)) {
        throw "Required file not found: $Path"
    }

    $raw = Get-Content -Path $Path -Raw
    if ([string]::IsNullOrWhiteSpace($raw)) {
        throw "File is empty: $Path"
    }

    $delimiter = if ($raw -match '\|') { '|' } else { ',' }
    $rows = @(Import-Csv -Path $Path -Delimiter $delimiter)
    if ($rows.Count -eq 0) {
        throw "No data rows found in file: $Path"
    }

    $first = $rows[0]
    $RequiredColumns | ForEach-Object {
        if (-not $first.PSObject.Properties[$_]) {
            throw "Missing required column '$_' in file: $Path"
        }
    }

    return $rows
}

if (-not (Test-Path -Path $InputFolder -PathType Container)) {
    throw "Input folder not found: $InputFolder"
}

$newCmkDataFile = Join-Path $InputFolder 'newcmkdata.txt'
$newCekValuesFile = Join-Path $InputFolder 'newcekvalues.txt'

Write-Host "[AE] Reading new CMK data from '$newCmkDataFile'"
$newCmkRows = Import-DelimitedTextFile -Path $newCmkDataFile -RequiredColumns @('KeyStoreProviderName', 'KeyPath')
$newCmkData = $newCmkRows[0]

Write-Host "[AE] Reading new CEK values from '$newCekValuesFile'"
$newCekValues = Import-DelimitedTextFile -Path $newCekValuesFile -RequiredColumns @('CEKName', 'CEKEncryptedValue')

Write-Host "[AE] Connecting to '$ServerName' / '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;TrustServerCertificate=True;Connection Timeout=30"
$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop

Write-Host "[AE] Ensuring target CMK '$NewCmkName' exists"
$newCmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName $newCmkData.KeyStoreProviderName -KeyPath $newCmkData.KeyPath
$existingNewCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $NewCmkName }
if (-not $existingNewCmk) {
    New-SqlColumnMasterKey -Name $NewCmkName -InputObject $database -ColumnMasterKeySettings $newCmkSettings | Out-Null
}

Write-Host "[AE] Adding new encrypted CEK values under '$NewCmkName'"
$ceks = Get-SqlColumnEncryptionKey -InputObject $database

$ceksToRotate = @(
    $ceks | Where-Object {
        $_.ColumnEncryptionKeyValues -and
        @($_.ColumnEncryptionKeyValues | Where-Object { $_.ColumnMasterKeyName -eq $OldCmkName }).Count -gt 0
    }
)

$ceksToRotate | ForEach-Object {
    $cek = $_
    if (@($cek.ColumnEncryptionKeyValues).Count -gt 1) {
        throw "CEK '$($cek.Name)' already has multiple encrypted values and still references '$OldCmkName'."
    }

    $newValueRow = @($newCekValues | Where-Object { $_.CEKName -eq $cek.Name }) | Select-Object -First 1
    if (-not $newValueRow) {
        throw "No new encrypted value found for CEK '$($cek.Name)' in file '$newCekValuesFile'."
    }

    Add-SqlColumnEncryptionKeyValue `
        -ColumnMasterKeyName $NewCmkName `
        -Name $cek.Name `
        -EncryptedValue $newValueRow.CEKEncryptedValue `
        -InputObject $database | Out-Null
}

Write-Host "[AE] Completing rotation for source CMK '$OldCmkName'"
Complete-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $OldCmkName -InputObject $database

Write-Host "[AE] Removing source CMK '$OldCmkName' metadata"
Remove-SqlColumnMasterKey -Name $OldCmkName -InputObject $database

Write-Host '[AE] Completed successfully'

Girando uma chave de criptografia de coluna

Girar uma chave de criptografia de coluna envolve descriptografar os dados em todas as colunas, criptografados com a chave a ser girada e criptografar novamente os dados usando a nova chave de criptografia de coluna. Esse fluxo de trabalho de rotação requer acesso às chaves e ao banco de dados e, portanto, não pode ser executado com separação de funções. Rotacionar uma chave de criptografia de coluna pode levar muito tempo se as tabelas que contêm colunas criptografadas com essa chave, que está a ser rotacionada, forem grandes. Portanto, sua organização precisa planejar cuidadosamente uma rotação de chave de criptografia de coluna.

Você pode girar uma chave de criptografia de coluna usando uma abordagem offline ou online. É provável que o método anterior seja mais rápido, mas seus aplicativos não podem gravar nas tabelas afetadas. A última abordagem provavelmente levará mais tempo, mas você pode limitar o intervalo de tempo, durante o qual as tabelas afetadas não estão disponíveis para aplicativos. Para obter mais informações, consulte Configurar a criptografia de coluna usando Always Encrypted com PowerShell e Set-SqlColumnEncryption.

Exemplo - Rotação de uma chave de encriptação de coluna

O script abaixo demonstra a rotação de uma chave de criptografia de coluna. O script pressupõe que o banco de dados de destino contém algumas colunas criptografadas com uma chave de criptografia de coluna, chamada CEK1 (a ser girada), que é protegida usando uma chave mestra de coluna, chamada CMK1 (a chave mestra de coluna não é armazenada no Cofre de Chaves do Azure).

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$ServerName = '<server name>',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$DatabaseName = '<database name>',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$OldCekName = 'CEK1',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$NewCekName = 'CEK2',

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$CmkName = 'CMK2',

    [Parameter(Mandatory = $false)]
    [ValidateRange(0, 3600)]
    [int]$MaxDowntimeInSeconds = 120,

    [Parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$LogFileDirectory = '.'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

if ($OldCekName -eq $NewCekName) {
    throw 'OldCekName and NewCekName must be different.'
}

if (-not (Test-Path -Path $LogFileDirectory -PathType Container)) {
    New-Item -Path $LogFileDirectory -ItemType Directory | Out-Null
}

Write-Host "[AE] Connecting to '$ServerName' / '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;TrustServerCertificate=True;Connection Timeout=30"
$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop

Write-Host "[AE] Ensuring CMK '$CmkName' exists"
$cmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $cmk) {
    throw "Column master key '$CmkName' was not found."
}

Write-Host "[AE] Ensuring target CEK '$NewCekName' exists"
$existingNewCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $NewCekName }
if (-not $existingNewCek) {
    New-SqlColumnEncryptionKey -Name $NewCekName -InputObject $database -ColumnMasterKey $CmkName | Out-Null
}

Write-Host "[AE] Discovering encrypted columns using '$OldCekName'"
$settings = @()
$tables = @($database.Tables)
$tables | ForEach-Object {
    $table = $_
    @($table.Columns) | ForEach-Object {
        $column = $_
        if ($column.IsEncrypted -and $column.ColumnEncryptionKeyName -eq $OldCekName) {
            $columnName = "{0}.{1}.{2}" -f $table.Schema, $table.Name, $column.Name
            $settings += New-SqlColumnEncryptionSettings -ColumnName $columnName -EncryptionType $column.EncryptionType -EncryptionKey $NewCekName
        }
    }
}

if ($settings.Count -eq 0) {
    Write-Warning "No encrypted columns found that reference '$OldCekName'. Nothing to rotate."
    return
}

Write-Host "[AE] Re-encrypting $($settings.Count) column(s) to '$NewCekName'"
Set-SqlColumnEncryption `
    -ColumnEncryptionSettings $settings `
    -InputObject $database `
    -UseOnlineApproach `
    -MaxDowntimeInSeconds $MaxDowntimeInSeconds `
    -LogFileDirectory $LogFileDirectory

Write-Host "[AE] Validating no columns still reference '$OldCekName'"
$stillUsingOld = $false
@($database.Tables) | ForEach-Object {
    @($_.Columns) | ForEach-Object {
        if ($_.IsEncrypted -and $_.ColumnEncryptionKeyName -eq $OldCekName) {
            $stillUsingOld = $true
        }
    }
}

if ($stillUsingOld) {
    throw "At least one encrypted column still references '$OldCekName'. Aborting CEK removal."
}

Write-Host "[AE] Removing old CEK '$OldCekName'"
Remove-SqlColumnEncryptionKey -Name $OldCekName -InputObject $database

Write-Host '[AE] Completed successfully'

Próximos passos

• Consultar colunas ao usar o Always Encrypted com o SQL Server Management Studio
• Desenvolver aplicações utilizando Always Encrypted

Ver também

• Sempre criptografado
• Visão geral do gerenciamento de chaves para Always Encrypted
• Configurar Always Encrypted usando PowerShell
• Rodar chaves Sempre Encriptadas usando o SQL Server Management Studio
• CREATE COLUMN MASTER KEY (Transact-SQL)
• DROP COLUMN MASTER KEY (Transact-SQL)
• CREATE COLUMN ENCRYPTION KEY (Transact-SQL)
• ALTER COLUMN ENCRYPTION KEY (Transact-SQL)
• DROP COLUMN ENCRYPTION KEY (Transact-SQL)
• sys.column_master_keys (Transact-SQL)
• sys.column_encryption_keys (Transact-SQL)