Konfigurowanie szyfrowania kolumn przy użyciu funkcji Always Encrypted za pomocą programu PowerShell

Artykuł
2025-03-05

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance

W tym artykule przedstawiono kroki ustawiania docelowej konfiguracji funkcji Always Encrypted dla kolumn bazy danych przy użyciu polecenia cmdlet Set-SqlColumnEncryption (w module SqlServer PowerShell). Polecenie cmdlet Set-SqlColumnEncryption modyfikuje zarówno schemat docelowej bazy danych, jak i dane przechowywane w wybranych kolumnach. Dane przechowywane w kolumnie mogą być szyfrowane, ponownie szyfrowane lub odszyfrowywane w zależności od określonych docelowych ustawień szyfrowania dla kolumn i bieżącej konfiguracji szyfrowania.

Notatka

Jeśli używasz programu SQL Server 2019 (15.x), a wystąpienie programu SQL Server jest skonfigurowane przy użyciu bezpiecznej enklawy, możesz uruchamiać operacje kryptograficzne w miejscu bez przenoszenia danych z bazy danych. Zobacz , jak skonfigurować szyfrowanie kolumn in-place, wykorzystując Always Encrypted oraz bezpieczne enklawy.

Aby uzyskać więcej informacji na temat obsługi funkcji Always Encrypted w module SqlServer PowerShell, zobacz Configure Always Encrypted using PowerShell.

Warunki wstępne

Aby ustawić docelową konfigurację szyfrowania, należy upewnić się, że:

Klucz szyfrowania kolumny jest skonfigurowany w bazie danych (jeśli szyfrujesz lub ponownie szyfrujesz kolumnę). Aby uzyskać szczegółowe informacje, zobacz Konfiguracja kluczy Always Encrypted za pomocą PowerShell.
Dostęp do klucza głównego kolumny dla każdej kolumny, którą chcesz zaszyfrować, ponownie zaszyfrować lub odszyfrować, możesz uzyskać z komputera z uruchomionymi poleceniami cmdlet programu PowerShell.
używasz modułu SqlServer PowerShell w wersji 22.0.50 lub nowszej.

Zagadnienia dotyczące wydajności i dostępności

Aby zastosować określone ustawienia szyfrowania docelowego dla bazy danych, polecenie cmdlet Set-SqlColumnEncryption w sposób niewidoczny pobiera wszystkie dane z kolumn zawierających docelowe dane, przekazuje dane z powrotem do zestawu tymczasowych tabel (z docelowymi ustawieniami szyfrowania), a następnie zastępuje oryginalne tabele ich nowymi wersjami. Podstawowy dostawca danych frameworka .NET dla SQL Server szyfruje lub/i odszyfrowuje dane podczas pobierania lub/i przesyłania, w zależności od bieżącej konfiguracji szyfrowania kolumny oraz określonych ustawień szyfrowania dla kolumn docelowych. Operacja przenoszenia danych może zająć dużo czasu, w zależności od rozmiaru danych w tabelach, których to dotyczy, i przepustowości sieci.

Polecenie cmdlet Set-SqlColumnEncryption obsługuje dwa podejścia do konfigurowania docelowej konfiguracji szyfrowania: online i offline.

W przypadku podejścia w trybie offline tabele docelowe (oraz wszelkie tabele związane z tabelami docelowymi, na przykład wszystkie tabele, z którymi tabela docelowa ma relacje klucza obcego) są niedostępne do zapisywania transakcji przez cały czas trwania operacji. Semantyka ograniczeń klucza obcego (CHECK lub NOCHECK) jest zawsze zachowywana podczas korzystania z podejścia offline.

W przypadku podejścia online (wymaga modułu SqlServer PowerShell w wersji 21.x lub nowszej), operacja kopiowania i szyfrowania, odszyfrowywania lub ponownego szyfrowania danych jest wykonywana przyrostowo. Aplikacje mogą odczytywać i zapisywać dane z i do tabel docelowych w całej operacji przenoszenia danych, z wyjątkiem ostatniej iteracji, czas trwania którego jest ograniczony przez MaxDownTimeInSeconds parametr (który można zdefiniować). Aby wykrywać i przetwarzać zmiany, które aplikacje mogą wprowadzać podczas kopiowania danych, polecenie cmdlet włącza śledzenie zmian w docelowej bazie danych. Z tego powodu podejście online może zużywać więcej zasobów po stronie serwera niż podejście offline. Operacja może również zająć znacznie więcej czasu w przypadku podejścia online, zwłaszcza jeśli obciążenie intensywnie zapisujące działa na bazie danych. Podejście online może służyć do szyfrowania jednej tabeli jednocześnie, a tabela musi mieć klucz podstawowy. Domyślnie ograniczenia klucza obcego są tworzone ponownie za pomocą NOCHECK opcji, aby zminimalizować wpływ na aplikacje. Zachowanie semantyki ograniczeń klucza obcego można wymusić, określając opcję KeepCheckForeignKeyConstraints.

Poniżej przedstawiono wskazówki dotyczące wybierania między podejściami offline i online:

Użyj podejścia offline:

Aby zminimalizować czas trwania operacji.
Aby szyfrować/odszyfrowywać/ponownie szyfrować kolumny w wielu tabelach jednocześnie.
Jeśli tabela docelowa nie ma klucza podstawowego.

Użyj podejścia online:

Aby zminimalizować przestój/niedostępność bazy danych w aplikacjach.

Zagadnienia dotyczące zabezpieczeń

Polecenie cmdlet Set-SqlColumnEncryption służące do konfigurowania szyfrowania kolumn bazy danych, obsługuje zarówno klucze Always Encrypted, jak i dane przechowywane w kolumnach bazy danych. Dlatego ważne jest, aby uruchamiać cmdlet na bezpiecznym komputerze. Jeśli baza danych znajduje się w programie SQL Server, wykonaj polecenie cmdlet z innego komputera niż komputer hostowania wystąpienia programu SQL Server. Ponieważ głównym celem funkcji Always Encrypted jest zapewnienie bezpieczeństwa zaszyfrowanych poufnych danych, nawet jeśli system bazy danych zostanie naruszony, wykonanie skryptu programu PowerShell, który przetwarza klucze i/lub poufne dane na komputerze z programem SQL Server, może zmniejszyć lub pokonać korzyści wynikające z tej funkcji.

Zadanie	Artykuł	Uzyskuje dostęp do kluczy w postaci zwykłego tekstu/magazynu kluczy	Uzyskuje dostęp do bazy danych
Krok 1. Uruchom środowisko programu PowerShell i zaimportuj moduł SqlServer.	Importowanie modułu SqlServer	Nie	Nie
Krok 2. Nawiązywanie połączenia z serwerem i bazą danych	Nawiązywanie połączenia z bazą danych	Nie	Tak
Krok 3. Uwierzytelnij się na platformie Azure, jeśli klucz główny kolumny, chroniący klucz szyfrowania kolumny, który ma zostać obrócony, jest przechowywany w usłudze Azure Key Vault	Connect-AzAccount	Tak	Nie
Krok 4. Uzyskaj token dostępu dla usługi Azure Key Vault, jeśli klucz główny kolumny jest przechowywany w usłudze Azure Key Vault.	Get-AzAccessToken	Nie	Nie
Krok 5. Utwórz tablicę obiektów SqlColumnEncryptionSettings — jedną dla każdej kolumny bazy danych, którą chcesz zaszyfrować, ponownie zaszyfrować lub odszyfrować. SqlColumnMasterKeySettings to obiekt, który istnieje w pamięci (w programie PowerShell). Określa docelowy schemat szyfrowania dla kolumny.	New-SqlColumnEncryptionSettings	Nie	Nie
Krok 6. Ustaw żądaną konfigurację szyfrowania określoną w tablicy obiektów SqlColumnMasterKeySettings utworzonych w poprzednim kroku. Kolumna zostanie zaszyfrowana, ponownie zaszyfrowana lub odszyfrowana w zależności od określonych ustawień docelowych i bieżącej konfiguracji szyfrowania kolumny.	Set-SqlColumnEncryption Uwaga: ten krok może zająć dużo czasu. Aplikacje nie będą mogły uzyskiwać dostępu do tabel za pośrednictwem całej operacji lub jej części, w zależności od wybranego podejścia (online a offline).	Tak	Tak

Szyfrowanie kolumn przy użyciu podejścia offline — przykład

W poniższym przykładzie pokazano ustawienie docelowej konfiguracji szyfrowania dla kilku kolumn. Jeśli którakolwiek kolumna nie jest jeszcze zaszyfrowana, zostanie zaszyfrowana. Jeśli jakakolwiek kolumna jest już zaszyfrowana przy użyciu innego klucza i/lub innego typu szyfrowania, zostanie odszyfrowana, a następnie ponownie zaszyfrowana przy użyciu określonego klucza docelowego/typu.

# 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

Szyfrowanie kolumn przy użyciu podejścia online — przykład

W poniższym przykładzie pokazano ustawienie docelowej konfiguracji szyfrowania dla kilku kolumn przy użyciu podejścia online. Jeśli którakolwiek kolumna nie jest jeszcze zaszyfrowana, zostanie zaszyfrowana. Jeśli jakakolwiek kolumna jest już zaszyfrowana przy użyciu innego klucza i/lub innego typu szyfrowania, zostanie odszyfrowana, a następnie ponownie zaszyfrowana przy użyciu określonego klucza docelowego/typu.

# 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

Odszyfrowywanie kolumn — przykład

W poniższym przykładzie pokazano, jak odszyfrować wszystkie kolumny, które są obecnie szyfrowane w bazie danych.

# 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

Następne kroki

Tworzenie aplikacji przy użyciu usługi Always Encrypted

Udostępnij za pośrednictwem