Tutorial: Erste Schritte mit Always Encrypted

Artikel
07/15/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Tutorial werden Ihnen die erste Schritte mit Always Encrypted vermittelt. Es wird Folgendes gezeigt:

So verschlüsseln Sie ausgewählte Spalten in Ihrer Datenbank.
So fragen Sie verschlüsselte Spalten ab.

Hinweis

Wenn Sie nach Informationen zu Always Encrypted mit sicheren Enklaven suchen, lesen Sie stattdessen die folgenden Lernprogramme:

Voraussetzungen

Für dieses Tutorial benötigen Sie Folgendes:

Eine leere Datenbank in Azure SQL-Datenbank, Azure SQL Managed Instance oder SQL Server. In den folgenden Anweisungen wird davon ausgegangen, dass der Datenbankname ContosoHR ist. Sie müssen ein Besitzer der Datenbank sein (ein Mitglied der db_owner Rolle). Informationen zum Erstellen einer Datenbank finden Sie in der Schnellstartanleitung: Erstellen einer einzelnen Datenbank – Azure SQL-Datenbank oder Erstellen einer Datenbank in SQL Server.
Optional, aber empfohlen, insbesondere, wenn Sich Ihre Datenbank in Azure befindet: ein Schlüsseltresor in Azure Key Vault. Informationen zum Erstellen eines Schlüsseltresors finden Sie unter Schnellstart: Erstellen eines Schlüsseltresors über das Azure-Portal.
- Wenn für Ihren Schlüsseltresor das Berechtigungsmodell mit Zugriffsrichtlinien gilt, stellen Sie sicher, dass Sie im Schlüsseltresor über die folgenden Berechtigungen verfügen: get, list, create, unwrap key, wrap key, verify, sign. Weitere Informationen finden Sie unter Zuweisen einer Key Vault-Zugriffsrichtlinie.
- Wenn Sie das Berechtigungsmodell mit rollenbasierter Zugriffssteuerung (Role-Based Access Control, RBAC) von Azure verwenden, stellen Sie sicher, dass Sie Mitglied der Rolle Key Vault-Kryptografiebeauftragter für Ihren Schlüsseltresor sind. Weitere Informationen finden Sie unter Gewähren des Zugriffs auf Key Vault-Schlüssel, -Zertifikate und -Geheimnisse mit der rollenbasierten Zugriffssteuerung in Azure.
Die neueste Version von SQL Server Management Studio (SSMS) oder die neueste Version der SqlServer und Az PowerShell-Module. Das Az PowerShell-Modul ist nur erforderlich, wenn Sie Azure Key Vault verwenden.

Schritt 1: Erstellen und Auffüllen des Datenbankschemas

In diesem Schritt erstellen Sie das HR-Schema und die Tabelle Angestellte. Anschließend füllen Sie die Tabelle mit einigen Daten auf.

SSMS
PowerShell

Stellen Sie eine Verbindung mit Ihrer Datenbank her. Anweisungen zum Herstellen einer Verbindung mit einer Datenbank über SSMS finden Sie in der Schnellstartanleitung: Verbinden und Abfragen eines Azure SQL-Datenbank oder einer Azure SQL Managed Instance mit SQL Server Management Studio (SSMS) oder Schnellstart: Verbinden und Abfragen einer SQL Server-Instanz mit SQL Server Management Studio (SSMS).
Öffnen Sie ein neues Abfragefenster für die ContosoHR Datenbank .

Fügen Sie die folgenden Anweisungen ein, und führen Sie sie aus, um eine neue Tabelle namens Mitarbeiter zu erstellen.

CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

Fügen Sie die folgenden Anweisungen ein, und führen Sie sie aus, um der Tabelle Mitarbeiter einige Mitarbeiterdatensätze hinzuzufügen.

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

Führen Sie in einer PowerShell-Sitzung die folgenden Befehle aus. Stellen Sie sicher, dass Sie die Verbindungszeichenfolge mit der Adresse Ihrer Server- und Authentifizierungseinstellungen aktualisieren, die für Ihre Datenbank gültig sind.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Schritt 2: Verschlüsseln von Spalten

In diesem Schritt erstellen Sie einen Spaltenhauptschlüssel und einen Spaltenverschlüsselungsschlüssel, für Always Encrypted. Anschließend verschlüsseln Sie die Spalten SSN und Gehalt in der Tabelle Mitarbeiter.

SSMS
PowerShell

SSMS bietet einen Assistenten, zum einfachen Konfigurieren von Always Encrypted, indem Sie einen Spaltenhauptschlüssel und einen Spaltenverschlüsselungsschlüssel einrichten und ausgewählte Spalten verschlüsseln.

Erweitern Sie im Objekt-Explorer den Eintrag Datenbanken>ContosoHR>Tabellen.
Klicken Sie mit der rechten Maustaste auf die Tabelle Mitarbeiter, und wählen Sie Spalten verschlüsseln aus, um den Always Encrypted-Assistenten zu öffnen.
Wählen Sie Weiter auf der Einführungs-seite des Assistenten.
Auf der Seite Spaltenauswahl.
1. Wählen Sie die Spalten SSN und Gehalt aus. Wählen Sie deterministische Verschlüsselung für die SSN Spalte und die zufällige Verschlüsselung für die Spalte Gehalt aus. Die deterministische Verschlüsselung unterstützt Abfragen, z. B. Punktsuchverweise, die Gleichheitsvergleiche für verschlüsselte Spalten umfassen. Randomisierte Verschlüsselung unterstützt keine Berechnungen für verschlüsselte Spalten.
2. Belassen Sie CEK-Auto1 (Neu) als Spaltenverschlüsselungsschlüssel für beide Spalten. Dieser Schlüssel ist noch nicht vorhanden und wird vom Assistenten generiert.
3. Wählen Sie Weiter aus.
Konfigurieren Sie auf der Seite Hauptschlüsselkonfiguration einen neuen Spaltenhauptschlüssel, der vom Assistenten generiert wird. Zuerst müssen Sie auswählen, wo Sie den Spaltenhauptschlüssel speichern möchten. Der Assistent unterstützt zwei Schlüsselspeichertypen:
- Azure Key Vault – empfohlen, wenn Sich Ihre Datenbank in Azure befindet
- Windows-Zertifikatspeicher
Im Allgemeinen ist Azure Key Vault die empfohlene Option, insbesondere, wenn Sich Ihre Datenbank in Azure befindet.
- So verwenden Sie Azure Key Vault:
  1. Wählen Sie Azure Key Vaultaus.
  2. Wählen Sie Anmelden aus und schließen Sie die Anmeldung bei Azure ab.
  3. Nachdem Sie sich angemeldet haben, zeigt die Seite die Liste der Abonnements und Schlüsseltresore an, auf die Sie Zugriff haben. Wählen Sie ein Azure-Abonnement mit dem Schlüsseltresor aus, das Sie verwenden möchten.
  4. Wählen Sie Ihren Schlüsseltresor aus.
  5. Wählen Sie Weiter aus.
- So verwenden Sie den Windows-Zertifikatspeicher:
  1. Wählen Sie Windows-Zertifikatspeicher aus.
  2. Behalten Sie die Standardauswahl des aktuellen Benutzers bei - Dadurch wird der Assistent angewiesen, ein Zertifikat (den neuen Spaltenhauptschlüssel) im aktuellen Benutzerspeicher zu generieren.
  3. Wählen Sie Weiter aus.
Auf der Seite In-Place-Verschlüsselungseinstellungen ist keine zusätzliche Konfiguration erforderlich, da für die Datenbank keine Enklave aktiviert ist. Wählen Sie Weiter aus.
Auf der Seite Einstellungen ausführen werden Sie gefragt, ob Sie mit der Verschlüsselung fortfahren oder ein PowerShell-Skript generieren möchten, das später ausgeführt werden soll. Übernehmen Sie die Standardeinstellungen, und klicken Sie auf Weiter.
Auf der Seite Zusammenfassung informiert der Assistent Sie über die ausgeführten Aktionen. Überprüfen Sie alle Informationen auf Richtigkeit und wählen Sie Fertig stellen aus.
Auf der Seite Ergebnisse können Sie den Fortschritt der Vorgänge des Assistenten überwachen. Warten Sie, bis alle Vorgänge erfolgreich abgeschlossen wurden, und wählen Sie Schließen aus.
(Optional) Erkunden Sie die Änderungen, die der Assistent in Ihrer Datenbank vorgenommen hat.
1. Erweitern Sie contosoHR>Sicherheit>Always Encrypted Keys, um die Metadatenobjekte für den Spaltenhauptschlüssel und die Spaltenverschlüsselung zu untersuchen, die der Assistent erstellt hat.
2. Sie können auch die folgenden Abfragen für die Systemkatalogansichten ausführen, die Schlüsselmetadaten enthalten.
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. Klicken Sie in Objekt-Explorer mit der rechten Maustaste auf die Tabelle Mitarbeiter und wählen Sie Skripttabelle als>Erstellen in>neues Abfrage-Editor Fenster aus. Dadurch wird ein neues Abfragefenster mit der Anweisung CREATE TABLE- für die Tabelle Mitarbeiter geöffnet. Beachten Sie die ENCRYPTED WITH -Klausel, die in den Definitionen der Spalten SSN und Gehalt angezeigt wird.
4. Sie können auch die folgende Abfrage für sys.columns ausführen, um Verschlüsselungsmetadaten auf Spaltenebene für die beiden verschlüsselten Spalten abzurufen.
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

Erstellen Sie einen Spaltenhauptschlüssel in Ihrem Schlüsselspeicher.

Wenn Sie Azure Key Vault verwenden, führen Sie die folgenden Befehle aus, um einen asymmetrischen Schlüssel in Ihrem Schlüsseltresor zu erstellen. Stellen Sie sicher, dass Sie die richtige ID Ihres Abonnements, den Namen der Ressourcengruppe mit Ihrem Schlüsseltresor und ihren Schlüsseltresornamen angeben.

Import-Module "Az"
Connect-AzAccount
$subscriptionId = "<your Azure subscription ID"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "your key name"

# Switch to your subscription.
Set-AzConteXt -SubscriptionId $subscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

Wenn Sie den Windows-Zertifikatspeicher verwenden, führen Sie die folgenden Befehle aus, um ein Zertifikat in Ihrem aktuellen Benutzerspeicher zu erstellen.

$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

Stellen Sie mithilfe des SqlServer PowerShell-Moduls eine Verbindung mit Ihrer Datenbank her. Stellen Sie sicher, dass Sie eine gültige Verbindungszeichenfolge für Ihre Datenbank angeben.
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Stellen Sie ein Metadatenobjekt (das auf den physischen Spaltenhauptschlüssel verweist, den Sie in Ihrem Schlüsselspeicher erstellt haben) für Spaltenhauptschlüssel in Ihrer Datenbank bereit.

Wenn Sie Azure Key Vault verwenden, führen Sie die folgenden Befehle aus.

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Wenn Sie den Windows-Zertifikatspeicher verwenden, führen Sie die folgenden Befehle aus.

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Generieren Sie einen Spaltenverschlüsselungsschlüssel, verschlüsseln Sie ihn mit dem Spaltenhauptschlüssel, den Sie erstellt haben und erstellen Sie Spaltenverschlüsselungsschlüssel-Metadaten in der Datenbank.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```
Verschlüsseln Sie SSN und Gehalts -Spalten in der Tabelle Mitarbeiter. Wählen Sie deterministische Verschlüsselung für die SSN Spalte und die zufällige Verschlüsselung für die Spalte Gehalt aus. Die deterministische Verschlüsselung unterstützt Abfragen, z. B. Punktsuchverweise, die Gleichheitsvergleiche für verschlüsselte Spalten umfassen. Randomisierte Verschlüsselung unterstützt keine Berechnungen für verschlüsselte Spalten.
```
# Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
```

(Optional) Erkunden Sie die Änderungen, die Sie in Ihrer Datenbank vorgenommen haben.

Führen Sie die folgenden Befehle aus, um Systemkatalogansichten abzufragen, die Metadaten über den Spaltenhauptschlüssel und den von Ihnen erstellten Spaltenverschlüsselungsschlüssel enthalten.

$query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Führen Sie die folgenden Befehle aus, um sys.columns abzufragen, um Verschlüsselungsmetadaten auf Spaltenebene für die beiden verschlüsselten Spalten abzurufen.

$query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Schritt 3: Abfrage verschlüsselter Spalten

SSMS
PowerShell

Stellen Sie eine Verbindung mit Ihrer Datenbank her, wobei Always Encrypted für Ihre Verbindung deaktiviert ist.
1. Öffnen Sie ein neues Abfragefenster.
2. Klicken Sie mit der rechten Maustaste in das Abfragefenster und wählen Sie Verbindung>Verbindung ändern aus. Dadurch wird das Dialogfeld Mit Datenbank-Engine verbinden geöffnet.
3. Klicken Sie auf Optionen<<. Dadurch werden zusätzliche Tabs im Dialogfeld Mit Datenbank-Engine verbinden angezeigt.
4. Wählen Sie die Registerkarte Always Encrypted aus.
5. Stellen Sie sicher, dass das Kontrollkästchen Always Encrypted aktivieren (Spaltenverschlüsselung) nicht ausgewählt ist.
6. Wählen Sie Verbinden.
Fügen Sie die folgende Abfrage ein und führen Sie diese aus. Die Abfrage sollte binäre verschlüsselte Daten zurückgeben.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
Stellen Sie eine Verbindung mit der aktivierten Option Always Encrypted zu Ihrer Datenbank her.
1. Klicken Sie mit der rechten Maustaste in das Abfragefenster und wählen Sie Verbindung>Verbindung ändern aus. Dadurch wird das Dialogfeld Mit Datenbank-Engine verbinden geöffnet.
2. Klicken Sie auf Optionen<<. Dadurch werden zusätzliche Tabs im Dialogfeld Mit Datenbank-Engine verbinden angezeigt.
3. Wählen Sie die Registerkarte Always Encrypted aus.
4. Wählen Sie Always Encrypted aktivieren (Spaltenverschlüsselung)
5. Wählen Sie Verbinden.
Führen Sie die gleiche Abfrage erneut aus. Da Sie mit Always Encrypted für Ihre Datenbankverbindung verbunden sind, versucht der Clienttreiber in SSMS, Daten zu entschlüsseln, die in beiden verschlüsselten Spalten gespeichert sind. Wenn Sie Azure Key Vault verwenden, werden Sie möglicherweise aufgefordert, sich bei Azure anzumelden.
Aktivieren Sie die Parametrisierung für Always Encrypted. Mit diesem Feature können Sie Abfragen ausführen, die Daten nach verschlüsselten Spalten filtern (oder Daten in verschlüsselte Spalten einfügen).
1. Wählen Sie im Hauptmenü von SSMS die Option Abfrage aus.
2. Wählen Sie Abfrageoptionen...aus.
3. Navigieren Sie zu Ausführung>Erweitert.
4. Stellen Sie sicher, dass Parametrisierung für Always Encrypted aktiviert überprüft ist.
5. Wählen Sie OK aus.
Fügen Sie die folgende Abfrage ein um die Daten nach der verschlüsselten SSN -Spalte zu filtern und führen Sie sie aus. Die Abfrage sollte eine Zeile zurückgeben, die Klartext- Werte enthält.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
Wenn Sie optional Azure Key Vault verwenden, das mit dem Zugriffsrichtlinienberechtigungsmodell konfiguriert ist, führen Sie die folgenden Schritte aus, um zu sehen, was passiert, wenn ein Benutzer versucht, Klartext-Daten aus verschlüsselten Spalten abzurufen, ohne Zugriff auf den Spaltenhauptschlüssel zum Schutz der Daten zu haben.
1. Entfernen Sie die Schlüsselunwrap -berechtigung für sich selbst in der Zugriffsrichtlinie für Ihren Schlüsseltresor. Weitere Informationen finden Sie unter Zuweisen einer Key Vault-Zugriffsrichtlinie.
2. Da der Clienttreiber in SSMS die aus einem Schlüsseltresor erworbenen Spaltenverschlüsselungsschlüssel 2 Stunden zwischenspeichert, schließen Sie SSMS, und öffnen Sie sie erneut. Dadurch wird sichergestellt, dass der Schlüsselcache leer ist.
3. Stellen Sie eine Verbindung mit der aktivierten Option Always Encrypted zu Ihrer Datenbank her.
4. Fügen Sie die folgende Abfrage ein und führen Sie diese aus. Die Abfrage sollte mit der Fehlermeldung misslingen, die angibt, dass die erforderliche unwrap Berechtigung fehlt.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

Stellen Sie eine Verbindung mit Ihrer Datenbank her, wobei Always Encrypted deaktiviert ist, und führen Sie eine Abfrage aus, um Daten aus verschlüsselten Spalten zu lesen. Die Abfrage sollte verschlüsselte Daten als binäre Matrix zurückgeben.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
Stellen Sie eine Verbindung mit Always Encrypted aktiviert zu Ihrer Datenbank her, und führen Sie eine Abfrage aus, um Daten aus verschlüsselten Spalten zu lesen. Da Sie Zugriff auf den Spaltenhauptschlüssel haben, der Ihre verschlüsselten Spalten schützt, sollte die Abfrage Klartext-Daten zurückgeben.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

Hinweis

Invoke-SqlCmd unterstützt keine Abfragen, die nach verschlüsselten Spalten filtern oder Daten einfügen können. Solche Abfragen müssen parametrisiert werden, und Invoke-SqlCmd unterstützt keine parametrisierten Abfragen.

Nächste Schritte

Entwickeln von Anwendungen mit Always Encrypted

Freigeben über