Mengonfigurasi enkripsi kolom menggunakan Always Encrypted dengan PowerShell

Artikel
04/07/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Artikel ini menyediakan langkah-langkah untuk mengatur konfigurasi Always Encrypted target untuk kolom database menggunakan cmdlet Set-SqlColumnEncryption (dalam modul SqlServer PowerShell). Cmdlet Set-SqlColumnEncryption memodifikasi skema database target serta data yang disimpan di kolom yang dipilih. Data yang disimpan dalam kolom dapat dienkripsi, dienkripsi ulang, atau didekripsi, tergantung pada pengaturan enkripsi target yang ditentukan untuk kolom dan konfigurasi enkripsi saat ini.

Catatan

Jika Anda menggunakan SQL Server 2019 (15.x) dan instans SQL Server Anda dikonfigurasi dengan enklave aman, Anda dapat menjalankan operasi kriptografi di tempat, tanpa memindahkan data dari database. Lihat Mengonfigurasi enkripsi kolom di tempat menggunakan Always Encrypted dengan enklave aman. Perhatikan bahwa PowerShell tidak mendukung enkripsi di tempat.

Untuk informasi selengkapnya tentang dukungan Always Encrypted dalam modul SqlServer PowerShell, lihat Mengonfigurasi Always Encrypted menggunakan PowerShell.

Prasyarat

Untuk mengatur konfigurasi enkripsi target, Anda perlu memastikan:

kunci enkripsi kolom dikonfigurasi dalam database (jika Anda mengenkripsi atau mengenkripsi ulang kolom). Untuk detailnya, lihat Mengonfigurasi kunci Always Encrypted menggunakan PowerShell.
Anda dapat mengakses kunci master kolom untuk setiap kolom yang ingin Anda enkripsi, enkripsi ulang, atau dekripsi, dari komputer yang menjalankan cmdlet PowerShell.
Anda menggunakan modul SqlServer PowerShell versi 22.0.50 atau yang lebih baru.

Pertimbangan Performa dan Ketersediaan

Untuk menerapkan pengaturan enkripsi target yang ditentukan untuk database, cmdlet Set-SqlColumnEncryption secara transparan mengunduh semua data dari kolom yang berisi tabel target, mengunggah data kembali ke sekumpulan tabel sementara (dengan pengaturan terenkripsi target), dan akhirnya mengganti tabel asli dengan versi baru tabel. Penyedia Data .NET Framework yang mendasar untuk SQL Server mengenkripsi atau/dan mendekripsi data pada unduhan atau/dan unggah, tergantung pada konfigurasi enkripsi kolom target saat ini adalah dan pengaturan enkripsi target yang ditentukan untuk kolom target. Operasi untuk memindahkan data mungkin memakan waktu lama, tergantung pada ukuran data dalam tabel yang terkena dampak dan bandwidth jaringan.

Cmdlet Set-SqlColumnEncryption mendukung dua pendekatan untuk menyiapkan konfigurasi enkripsi target: online dan offline.

Dengan pendekatan offline, tabel target (dan tabel apa pun yang terkait dengan tabel target, misalnya, tabel apa pun yang memiliki hubungan kunci asing dengan tabel) tidak tersedia untuk menulis transaksi selama durasi operasi. Semantik batasan kunci asing (CHECK atau NOCHECK) selalu dipertahankan saat menggunakan pendekatan offline.

Dengan pendekatan online (memerlukan modul SqlServer PowerShell versi 21.x atau yang lebih baru), operasi menyalin dan mengenkripsi, mendekripsi, atau mengenkripsi ulang data dilakukan secara bertahap. Aplikasi dapat membaca dan menulis data dari dan ke tabel target di seluruh operasi pergerakan data, kecuali perulangan terakhir, yang durasinya dibatasi oleh parameter MaxDownTimeInSeconds (yang dapat Anda tentukan). Untuk mendeteksi dan memproses perubahan yang dapat dilakukan aplikasi saat data sedang disalin, cmdlet memungkinkan Pelacakan Perubahan di database target. Karena itu, pendekatan online kemungkinan akan menggunakan lebih banyak sumber daya di sisi server daripada pendekatan offline. Operasi ini mungkin juga membutuhkan lebih banyak waktu dengan pendekatan online, terutama jika beban kerja write-heavy berjalan terhadap database. Pendekatan online dapat digunakan untuk mengenkripsi satu tabel sekaligus dan tabel harus memiliki kunci utama. Secara default, batasan kunci asing dibuat ulang dengan opsi NOCHECK untuk meminimalkan dampak pada aplikasi. Anda dapat menerapkan pelestarian semantik batasan kunci asing dengan menentukan opsi KeepCheckForeignKeyConstraints .

Berikut adalah panduan untuk memilih antara pendekatan offline dan online:

Gunakan pendekatan offline:

Untuk meminimalkan durasi operasi.
Untuk mengenkripsi/mendekripsi/mengenkripsi ulang kolom dalam beberapa tabel secara bersamaan.
Jika tabel target tidak memiliki kunci primer.

Gunakan pendekatan online:

Untuk meminimalkan waktu henti/tidak tersedianya database ke aplikasi Anda.

Pertimbangan Keamanan

Cmdlet Set-SqlColumnEncryption, digunakan untuk mengonfigurasi enkripsi untuk kolom database, menangani kunci Always Encrypted dan data yang disimpan dalam kolom database. Oleh karena itu, penting bagi Anda untuk menjalankan cmdlet di komputer yang aman. Jika database Anda berada di SQL Server, jalankan cmdlet dari komputer yang berbeda dari komputer yang menghosting instans SQL Server Anda. Karena tujuan utama Always Encrypted adalah untuk memastikan data sensitif terenkripsi aman bahkan jika sistem database disusupi, menjalankan skrip PowerShell yang memproses kunci dan/atau data sensitif di komputer SQL Server dapat mengurangi atau mengalahkan manfaat fitur.

Tugas	Artikel	Mengakses kunci teks biasa/penyimpanan kunci	Mengakses database
Langkah 1. Mulai lingkungan PowerShell dan impor modul SqlServer.	Mengimpor modul SqlServer	Tidak	Tidak
Langkah 2. Menyambungkan ke server dan database Anda	Menyambungkan ke database	Tidak	Ya
Langkah 3. Autentikasi ke Azure, jika kunci master kolom Anda (melindungi kunci enkripsi kolom, untuk diputar), disimpan di Azure Key Vault	Connect-AzAccount	Ya	Tidak
Langkah 4. Dapatkan token akses untuk Azure Key Vaults, jika kunci master kolom Anda disimpan di Azure Key Vault.	Get-AzAccessToken	Tidak	Tidak
Langkah 5. Buat array objek SqlColumnEncryptionSettings - satu untuk setiap kolom database, Anda ingin mengenkripsi, mengenkripsi ulang, atau mendekripsi. SqlColumnMasterKeySettings adalah objek yang ada dalam memori (di PowerShell). Ini menentukan skema enkripsi target untuk kolom.	New-SqlColumnEncryptionSettings	Tidak	Tidak
Langkah 6. Atur konfigurasi enkripsi yang diinginkan, yang ditentukan dalam array objek SqlColumnMasterKeySettings, yang Anda buat di langkah sebelumnya. Kolom akan dienkripsi, dienkripsi ulang, atau didekripsi, tergantung pada pengaturan target yang ditentukan dan konfigurasi enkripsi kolom saat ini.	Set-SqlColumnEncryption Catatan: Langkah ini mungkin memakan waktu lama. Aplikasi Anda tidak akan dapat mengakses tabel melalui seluruh operasi atau sebagiannya, tergantung pada pendekatan (online vs. offline), yang Anda pilih.	Ya	Ya

Mengenkripsi Kolom menggunakan Pendekatan Offline - Contoh

Contoh di bawah ini menunjukkan pengaturan konfigurasi enkripsi target untuk beberapa kolom. Jika salah satu kolom belum dienkripsi, kolom tersebut akan dienkripsi. Jika ada kolom yang sudah dienkripsi menggunakan kunci yang berbeda dan/atau jenis enkripsi yang berbeda, kolom tersebut akan didekripsi dan kemudian dienkripsi ulang dengan kunci/jenis target yang ditentukan.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

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

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Mengenkripsi Kolom menggunakan Pendekatan Online - Contoh

Contoh di bawah ini menunjukkan pengaturan konfigurasi enkripsi target untuk beberapa kolom menggunakan pendekatan online. Jika salah satu kolom belum dienkripsi, kolom tersebut akan dienkripsi. Jika ada kolom yang sudah dienkripsi menggunakan kunci yang berbeda dan/atau jenis enkripsi yang berbeda, kolom tersebut akan didekripsi dan kemudian dienkripsi ulang dengan kunci/jenis target yang ditentukan.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

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

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Kolom Dekripsi - Contoh

Contoh berikut menunjukkan cara mendekripsi semua kolom yang saat ini dienkripsi dalam database.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

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

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Langkah berikutnya

Mengembangkan aplikasi menggunakan Always Encrypted