Lernprogramm: Erste Schritte mit Always Encrypted

1. Stellen Sie eine Verbindung mit Ihrer Datenbank her. Anweisungen zum Herstellen einer Verbindung mit einer Datenbank von SSMS finden Sie in der Schnellstartanleitung: Verbinden und Abfragen einer 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).

2. Öffnen Sie ein neues Abfragefenster für die ContosoHR-Datenbank .

3. Fügen Sie die folgenden Anweisungen ein, und führen Sie sie aus, um eine neue Tabelle namens "Employees" 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];
   

4. Fügen Sie die folgenden Anweisungen ein, und führen Sie sie aus, um der Tabelle "Employees" 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 Ihres Servers und der 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

SSMS bietet einen Assistenten, der Ihnen hilft, Always Encrypted einfach zu konfigurieren, indem Sie einen Spaltenmasterschlüssel, einen Spaltenverschlüsselungsschlüssel und ausgewählte Spalten verschlüsseln.

1. Erweitern Sie im Objekt-Explorer die Datenbanken>ContosoHR-Tabellen.>

2. Klicken Sie mit der rechten Maustaste auf die Tabelle "Mitarbeiter ", und wählen Sie " Spalten verschlüsseln" aus, um den Assistenten "Immer verschlüsselt" zu öffnen.

   

3. Wählen Sie auf der Seite "Einführung" des Assistenten "Weiter" aus.

4. 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. Punktsuchvorgänge, die Gleichheitsvergleiche für verschlüsselte Spalten umfassen. Randomisierte Verschlüsselung unterstützt keine Berechnungen für verschlüsselte Spalten.
   2. Lassen 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.

   

5. Konfigurieren Sie auf der Seite " Masterschlüsselkonfiguration " einen neuen Spaltenmasterschlüssel, der vom Assistenten generiert wird. Zuerst müssen Sie auswählen, wo Sie den Spaltenmasterschlü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 melden Sie sich bei Azure ab.
     3. Nachdem Sie sich angemeldet haben, zeigt die Seite die Liste der Abonnements und Schlüsseltresor 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 den Windows-Zertifikatspeicher aus.

     2. Behalten Sie die Standardauswahl des aktuellen Benutzers bei . Dadurch wird der Assistent angewiesen, ein Zertifikat (den neuen Spaltenmasterschlüssel) im aktuellen Benutzerspeicher zu generieren.

        

     3. Wählen Sie Weiter aus.

6. 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.

7. 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. Behalten Sie die Standardeinstellungen bei, und wählen Sie "Weiter" aus.

8. Auf der Seite "Zusammenfassung " informiert der Assistent Sie über die ausgeführten Aktionen. Überprüfen Sie alle Informationen richtig, und wählen Sie "Fertig stellen" aus.

9. 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.

   

10. (Optional) Erkunden Sie die Änderungen, die der Assistent in Ihrer Datenbank vorgenommen hat.

    1. Erweitern Sie contosoHR>Security>Always Encrypted Keys, um die Metadatenobjekte für den Spaltenmasterschlü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 im Objekt-Explorer mit der rechten Maustaste auf die Tabelle "Mitarbeiter", und wählen Sie "Skripttabelle" als>Fenster "In>neuer Abfrage-Editor erstellen" aus. Dadurch wird ein neues Abfragefenster mit der CREATE TABLE-Anweisung für die Tabelle "Employees " geöffnet. Beachten Sie die ENCRYPTED WITH-Klausel , die in den Definitionen der Spalten "SSN " und "Salary " 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;
       

1. Erstellen Sie einen Spaltenmasterschlü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
     

2. 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
   

3. Stellen Sie ein Metadatenobjekt für Spaltenmasterschlüssel (das auf den physischen Spaltenmasterschlüssel verweist, den Sie in Ihrem Schlüsselspeicher erstellt haben) 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
     

4. Generieren Sie einen Spaltenverschlüsselungsschlüssel, verschlüsseln Sie ihn mit dem von Ihnen erstellten Spaltenmasterschlüssel, und erstellen Sie ein Metadatenobjekt für Spaltenverschlüsselungsschlüssel in der Datenbank.

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. Verschlüsseln Sie SSN - und Gehaltsspalten 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. Punktsuchvorgänge, 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 .
   
   

6. (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 Spaltenmasterschlü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
     

1. 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 auf eine beliebige Stelle im Abfragefenster, und wählen Sie "Verbindung ändern"> aus. Dadurch wird das Dialogfeld "Mit Datenbankmodul verbinden" geöffnet.
   3. Klicken Sie auf Optionen<<. Dadurch werden zusätzliche Registerkarten im Dialogfeld "Mit Datenbankmodul verbinden" angezeigt.
   4. Wählen Sie die Registerkarte Always Encrypted aus.
   5. Stellen Sie sicher, dass "Immer verschlüsselt aktivieren" (Spaltenverschlüsselung) nicht ausgewählt ist.
   6. Wählen Sie Verbinden aus.

   

2. Fügen Sie die folgende Abfrage ein, und führen Sie sie aus. Die Abfrage sollte binäre verschlüsselte Daten zurückgeben.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Stellen Sie eine Verbindung mit Ihrer Datenbank her, wobei "Always Encrypted" für Ihre Verbindung aktiviert ist.

   1. Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle im Abfragefenster, und wählen Sie "Verbindung ändern"> aus. Dadurch wird das Dialogfeld "Mit Datenbankmodul verbinden" geöffnet.
   2. Klicken Sie auf Optionen<<. Dadurch werden zusätzliche Registerkarten im Dialogfeld "Mit Datenbankmodul verbinden" angezeigt.
   3. Wählen Sie die Registerkarte Always Encrypted aus.
   4. Wählen Sie "Immer verschlüsselt aktivieren" (Spaltenverschlüsselung) aus.
   5. Wählen Sie Verbinden aus.

   

4. Führen Sie dieselbe 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.

   

5. Aktivieren Sie die Parameterisierung für "Immer verschlüsselt". 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 die Parameterisierung für Always Encrypted aktiviert ist.
   5. Klicken Sie auf OK.

   

6. Fügen Sie die folgende Abfrage ein, und führen Sie sie aus, die Daten nach der verschlüsselten SSN-Spalte filtert. Die Abfrage sollte eine Zeile zurückgeben, die Nur-Text-Werte enthält.

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. 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, Nur-Text-Daten aus verschlüsselten Spalten abzurufen, ohne Zugriff auf den Spaltenmasterschlüssel zum Schutz der Daten zu haben.

   1. Entfernen Sie die Schlüsselberechtigung unwrap 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 Ihrer Datenbank her, wobei "Always Encrypted" für Ihre Verbindung aktiviert ist.
   4. Fügen Sie die folgende Abfrage ein, und führen Sie sie aus. Die Abfrage sollte mit der Fehlermeldung fehlschlagen, die angibt, dass Die erforderliche unwrap Berechtigung fehlt.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

1. 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 Arrays zurückgeben.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. Stellen Sie eine Verbindung mit Ihrer Datenbank her, und führen Sie eine Abfrage aus, um Daten aus verschlüsselten Spalten zu lesen. Da Sie Zugriff auf den Spaltenmasterschlüssel haben, der Ihre verschlüsselten Spalten schützt, sollte die Abfrage Nur-Text-Daten zurückgeben.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query