Putar tombol Always Encrypted menggunakan PowerShell

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menyediakan langkah-langkah untuk memutar kunci untuk Always Encrypted menggunakan modul SqlServer PowerShell. Untuk informasi tentang cara mulai menggunakan modul SqlServer PowerShell untuk Always Encrypted, lihat Mengonfigurasi Always Encrypted menggunakan PowerShell.

Memutar kunci Always Encrypted adalah proses mengganti kunci yang ada dengan yang baru. Anda mungkin perlu memutar kunci jika disusupi, atau untuk mematuhi kebijakan organisasi Anda atau peraturan kepatuhan yang mengamanatkan rotasi kunci kriptografi reguler.

Always Encrypted menggunakan dua jenis kunci, sehingga ada dua proses rotasi kunci tingkat tinggi: rotasi kunci master kolom dan rotasi kunci enkripsi kolom.

• Rotasi kunci enkripsi kolom - melibatkan dekripsi data yang dienkripsi dengan kunci saat ini, dan mengenkripsi ulang data menggunakan kunci enkripsi kolom baru. Karena memutar kunci enkripsi kolom memerlukan akses ke kunci dan database, rotasi kunci enkripsi kolom hanya dapat dilakukan tanpa pemisahan peran.
• Rotasi kunci master kolom - melibatkan dekripsi kunci enkripsi kolom yang dilindungi dengan kunci master kolom saat ini, mengenkripsi ulang menggunakan kunci master kolom baru, dan memperbarui metadata untuk kedua jenis kunci. Rotasi kunci master kolom dapat diselesaikan dengan atau tanpa pemisahan peran (saat menggunakan modul PowerShell SqlServer).

Rotasi Kunci Master untuk Kolom tanpa Pemisahan Peran

Metode yang dijelaskan di bagian ini untuk memutar kunci master kolom tidak mendukung pemisahan peran antara administrator keamanan dan DBA. Beberapa langkah berikut menggabungkan operasi pada kunci fisik dengan operasi pada metadata kunci. Gunakan alur kerja ini jika organisasi Anda menggunakan model DevOps, atau saat database Anda dihosting di cloud dan tujuan utamanya adalah membatasi administrator cloud (tetapi bukan DBA lokal) agar tidak mengakses data sensitif. Jangan gunakan metode ini jika pihak yang berpotensi menjadi ancaman mencakup administrator basis data (DBA), atau jika administrator basis data (DBA) tidak boleh memiliki akses ke data sensitif.

Memutar Kunci Master Kolom tanpa Pemisahan Peran (Contoh Sertifikat Windows)

Skrip di bawah ini adalah contoh end-to-end yang menggantikan kunci master kolom (CMK1) yang ada dengan kunci master kolom baru (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.'

Rotasi Kunci Induk Kolom dengan Pemisahan Peran

Alur kerja rotasi kunci master kolom yang dijelaskan di bagian ini memastikan pemisahan antara Administrator Keamanan dan DBA.

Bagian 1: DBA

DBA mengambil metadata tentang kunci master kolom yang harus diganti, serta tentang kunci enkripsi kolom yang terpengaruh, yang terkait dengan kunci master kolom saat ini. DBA berbagi semua informasi ini dengan Administrator Keamanan.

Bagian 2: Administrator Keamanan

Administrator Keamanan menghasilkan kunci master kolom baru, mengenkripsi ulang kunci enkripsi kolom yang terkena dampak dengan kunci master kolom baru, dan berbagi informasi tentang kunci master kolom baru serta kumpulan nilai terenkripsi baru untuk kunci enkripsi kolom yang terkena dampak, dengan DBA.

Bagian 3: DBA

DBA membuat metadata untuk kunci master kolom baru dan memperbarui metadata kunci enkripsi kolom yang terkena dampak, untuk menambahkan kumpulan nilai terenkripsi baru. Dalam langkah ini, DBA juga berkoordinasi dengan administrator aplikasi yang mengkueri kolom enkripsi, yang memastikan aplikasi dapat mengakses kunci master kolom baru. Setelah semua aplikasi disiapkan untuk menggunakan kunci master kolom baru, DBA menghapus set lama nilai terenkripsi dan metadata kunci master kolom lama.

Memutar Kunci Master Kolom dengan Pemisahan Peran (Contoh Sertifikat Windows)

Skrip di bawah ini adalah contoh end-to-end untuk menghasilkan kunci master kolom baru berupa sertifikat di penyimpanan Sertifikat Windows, mengganti kunci master kolom yang ada saat ini dengan kunci master kolom baru. Skrip mengasumsikan, database target berisi kunci master kolom, bernama CMK1 (untuk diputar), yang mengenkripsi beberapa kunci enkripsi kolom.

Bagian 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"

Bagian 2: Administrator Keamanan

[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"

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

Memperbarui Kunci Enkripsi Kolom

Memutar kunci enkripsi kolom melibatkan dekripsi data di semua kolom, dienkripsi dengan kunci yang akan diputar, dan mengenkripsi ulang data menggunakan kunci enkripsi kolom baru. Alur kerja rotasi ini memerlukan akses ke kunci dan database, dan karenanya tidak dapat dilakukan dengan pemisahan peran. Memutar kunci enkripsi kolom dapat memakan waktu lama jika tabel yang berisi kolom-kolom terenkripsi dengan kunci yang sedang diganti memiliki ukuran besar. Oleh karena itu, organisasi Anda perlu merencanakan rotasi kunci enkripsi kolom dengan hati-hati.

Anda dapat memutar kunci enkripsi kolom menggunakan pendekatan offline atau online. Metode sebelumnya cenderung lebih cepat, tetapi aplikasi Anda tidak dapat menulis ke tabel yang terkena dampak. Pendekatan terakhir kemungkinan akan memakan waktu lebih lama, tetapi Anda dapat membatasi interval waktu, di mana tabel yang terkena dampak tidak tersedia untuk aplikasi. Untuk informasi selengkapnya, lihat Mengonfigurasi enkripsi kolom menggunakan Always Encrypted dengan PowerShell dan Set-SqlColumnEncryption.

Contoh - Memutar Kunci Enkripsi Kolom

Skrip di bawah ini menunjukkan memutar kunci enkripsi kolom. Skrip mengasumsikan, database target berisi beberapa kolom yang dienkripsi dengan kunci enkripsi kolom, bernama CEK1 (untuk diputar), yang dilindungi menggunakan kunci master kolom, bernama CMK1 (kunci master kolom tidak disimpan di Azure Key Vault).

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

Langkah berikutnya

• Kolom kueri menggunakan Always Encrypted dengan SQL Server Management Studio
• Mengembangkan aplikasi menggunakan Always Encrypted

Lihat Juga

• Always Encrypted
• Ringkasan manajemen kunci untuk Always Encrypted
• Mengonfigurasi Always Encrypted menggunakan PowerShell
• Memutar kunci Always Encrypted menggunakan 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 (T-SQL)
• sys.column_encryption_keys (T-SQL)