Kolomversleuteling configureren met Always Encrypted met PowerShell

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Dit artikel bevat de stappen voor het instellen van de always encrypted-doelconfiguratie voor databasekolommen met behulp van de cmdlet Set-SqlColumnEncryption (in de SqlServer PowerShell-module). De Set-SqlColumnEncryption cmdlet wijzigt zowel het schema van de doeldatabase als de gegevens die zijn opgeslagen in de geselecteerde kolommen. De gegevens die in een kolom zijn opgeslagen, kunnen worden versleuteld, opnieuw versleuteld of ontsleuteld, afhankelijk van de opgegeven instellingen voor doelversleuteling voor de kolommen en de huidige versleutelingsconfiguratie.

Notitie

Als u SQL Server 2019 (15.x) gebruikt en uw SQL Server-exemplaar is geconfigureerd met een beveiligde enclave, kunt u cryptografische bewerkingen uitvoeren zonder gegevens uit de database te verplaatsen. Bekijk Kolomversleuteling ter plekke configureren met Always Encrypted en beveiligde enclaves.

Zie Always Encrypted configureren met behulp van PowerShellvoor meer informatie over Always Encrypted-ondersteuning in de SqlServer PowerShell-module.

Voorwaarden

Als u de configuratie van de doelversleuteling wilt instellen, moet u ervoor zorgen dat:

een kolomversleutelingssleutel wordt geconfigureerd in de database (als u een kolom versleutelt of opnieuw versleutelt). Zie Always Encrypted-sleutels configureren met behulp van PowerShellvoor meer informatie.
u hebt toegang tot de hoofdsleutel van de kolom voor elke kolom die u wilt versleutelen, opnieuw versleutelen of ontsleutelen vanaf de computer waarop de PowerShell-cmdlets worden uitgevoerd.
u sqlServer PowerShell-moduleversie 22.0.50 of hoger gebruikt.

Overwegingen voor prestaties en beschikbaarheid

Als u de opgegeven doelversleutelingsinstellingen voor de database wilt toepassen, downloadt de Set-SqlColumnEncryption cmdlet transparant alle gegevens uit de kolommen met de doeltabellen, uploadt de gegevens terug naar een set tijdelijke tabellen (met de versleutelde doelinstellingen) en vervangt ten slotte de oorspronkelijke tabellen door de nieuwe versies van de tabellen. De onderliggende .NET Framework-gegevensprovider voor SQL Server versleutelt of/en ontsleutelt gegevens bij het downloaden of/en uploaden, afhankelijk van de huidige versleutelingsconfiguratie van de doelkolom, en de opgegeven doelversleutelingsinstellingen voor de doelkolommen. De bewerking voor het verplaatsen van de gegevens kan lang duren, afhankelijk van de grootte van de gegevens in betrokken tabellen en de netwerkbandbreedte.

De cmdlet Set-SqlColumnEncryption ondersteunt twee methoden voor het instellen van de doelversleutelingsconfiguratie: online en offline.

Bij de offlineaanpak zijn de doeltabellen (en eventuele tabellen die gerelateerd zijn aan de doeltabellen, bijvoorbeeld tabellen waarmee een doeltabel relaties heeft via refererende sleutels) niet beschikbaar om transacties te schrijven gedurende de gehele bewerking. De semantiek van foreign key-beperkingen (CHECK of NOCHECK) blijft altijd behouden wanneer u de offline benadering gebruikt.

Met de online benadering (vereist de SqlServer PowerShell-module versie 21.x of hoger), wordt de bewerking voor het kopiëren en versleutelen, ontsleutelen of opnieuw versleutelen van de gegevens stapsgewijs uitgevoerd. Toepassingen kunnen gegevens lezen en schrijven van en naar de doeltabellen tijdens de bewerking voor gegevensverplaatsing, met uitzondering van de laatste iteratie, waarvan de duur wordt beperkt door de MaxDownTimeInSeconds parameter (die u kunt definiëren). De cmdlet schakelt Wijzigingen bijhouden in de doeldatabase in om de veranderingen te detecteren en verwerken die toepassingen kunnen aanbrengen terwijl de gegevens worden gekopieerd. Daarom verbruikt de online benadering waarschijnlijk meer resources aan de serverzijde dan de offlinebenadering. De bewerking kan ook veel meer tijd in beslag nemen met de online aanpak, met name als een werkbelasting met veel schrijfbewerkingen wordt uitgevoerd op de database. De onlinebenadering kan worden gebruikt om één tabel tegelijk te versleutelen en de tabel moet een primaire sleutel hebben. Standaard worden foreign key-beperkingen gerecreëerd met de optie NOCHECK om de impact op toepassingen te minimaliseren. U kunt het behoud van de semantiek van externe-sleutelbeperkingen afdwingen door de optie KeepCheckForeignKeyConstraints op te geven.

Hier volgen de richtlijnen voor het kiezen tussen de offline- en onlinemethoden:

Gebruik de offlinebenadering:

De duur van de bewerking minimaliseren.
Kolommen in meerdere tabellen tegelijk versleutelen/ontsleutelen/opnieuw versleutelen.
Als de doeltabel geen primaire sleutel heeft.

Gebruik de online aanpak:

Om de downtime/onbeschikbaarheid van de database voor uw toepassingen te minimaliseren.

Beveiligingsoverwegingen

De Set-SqlColumnEncryption cmdlet, gebruikt voor het configureren van versleuteling voor databasekolommen, verwerkt zowel Always Encrypted-sleutels als de gegevens die zijn opgeslagen in databasekolommen. Daarom is het belangrijk dat u de cmdlet uitvoert op een beveiligde computer. Als uw database zich in SQL Server bevindt, voert u de cmdlet uit vanaf een andere computer dan de computer die als host fungeert voor uw SQL Server-exemplaar. Omdat het primaire doel van Always Encrypted is om ervoor te zorgen dat versleutelde gevoelige gegevens veilig zijn, zelfs als het databasesysteem wordt aangetast, kan het uitvoeren van een PowerShell-script waarmee sleutels en/of gevoelige gegevens op de SQL Server-computer worden verwerkt, de voordelen van de functie verminderen of verslaan.

Taak	Artikel	Toegang tot onversleutelde sleutels/sleutelopslag	Toegang tot database
Stap 1. Start een PowerShell-omgeving en importeer de SqlServer-module.	Importeer de SqlServer-module	Nee	Nee
Stap 2. Verbinding maken met uw server en database	Verbinding maken met een database	Nee	Ja
Stap 3. Authenticeer bij Azure, wanneer uw kolomhoofdsleutel, die de kolomversleutelingssleutel beschermt en moet worden geroteerd, wordt opgeslagen in Azure Key Vault.	Connect-AzAccount	Ja	Nee
Stap 4. Verkrijg een toegangstoken voor Azure Key Vaults als uw kolomhoofdsleutel is opgeslagen in Azure Key Vault.	Get-AzAccessToken	Nee	Nee
Stap 5. Maak een matrix van SqlColumnEncryptionSettings-objecten: één voor elke databasekolom, u wilt versleutelen, opnieuw versleutelen of ontsleutelen. SqlColumnMasterKeySettings is een object dat zich in het geheugen bevindt (in PowerShell). Hiermee geeft u het doelversleutelingsschema voor een kolom op.	New-SqlColumnEncryptionSettings	Nee	Nee
Stap 6. Stel de gewenste versleutelingsconfiguratie in die is opgegeven in de matrix van SqlColumnMasterKeySettings-objecten die u in de vorige stap hebt gemaakt. Een kolom wordt versleuteld, opnieuw versleuteld of ontsleuteld, afhankelijk van de opgegeven doelinstellingen en de huidige versleutelingsconfiguratie van de kolom.	Set-SqlColumnEncryption Opmerking: Deze stap kan lang duren. Uw toepassingen hebben geen toegang tot de tabellen via de hele bewerking of een deel ervan, afhankelijk van de aanpak (online versus offline), die u selecteert.	Ja	Ja

Kolommen versleutelen met offlinebenadering - voorbeeld

In het onderstaande voorbeeld ziet u hoe u de configuratie van de doelversleuteling instelt voor een aantal kolommen. Als een van beide kolommen nog niet is versleuteld, wordt deze versleuteld. Als een kolom al is versleuteld met een andere sleutel en/of een ander versleutelingstype, wordt deze ontsleuteld en vervolgens opnieuw versleuteld met de opgegeven doelsleutel/-type.

# 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

Kolommen versleutelen met behulp van onlinebenadering - voorbeeld

In het onderstaande voorbeeld ziet u hoe u de doelversleutelingsconfiguratie instelt voor een aantal kolommen met behulp van de onlinebenadering. Als een van beide kolommen nog niet is versleuteld, wordt deze versleuteld. Als een kolom al is versleuteld met een andere sleutel en/of een ander versleutelingstype, wordt deze ontsleuteld en vervolgens opnieuw versleuteld met de opgegeven doelsleutel/-type.

# 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

Kolommen ontsleutelen - voorbeeld

In het volgende voorbeeld ziet u hoe u alle kolommen ontsleutelt die momenteel zijn versleuteld in een 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

Volgende stappen

Toepassingen ontwikkelen met Always Encrypted

Delen via