Samouczek: wprowadzenie do funkcji Always Encrypted

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

W tym samouczku pokazano, jak rozpocząć pracę z funkcją Always Encrypted. Zostaną wyświetlone:

• Jak zaszyfrować wybrane kolumny w bazie danych.
• Jak wykonywać zapytania dotyczące zaszyfrowanych kolumn.

Uwaga / Notatka

Jeśli szukasz informacji na temat funkcji Always Encrypted z bezpiecznymi enklawami, zobacz poniższe samouczki.

• Wprowadzenie do korzystania z Always Encrypted w bezpiecznych enklawach
• Samouczek: wprowadzenie do korzystania z funkcji Always Encrypted z bezpiecznymi enklawami w programie SQL Server

Wymagania wstępne

Na potrzeby tego samouczka potrzebne są następujące elementy:

• Pusta baza danych w usłudze Azure SQL Database, usłudze Azure SQL Managed Instance lub programie SQL Server. W poniższych instrukcjach założono, że nazwa bazy danych to ContosoHR. Musisz być właścicielem bazy danych (członkiem roli db_owner ). Aby uzyskać informacje na temat tworzenia bazy danych, zobacz Szybki start: Tworzenie pojedynczej bazy danych — Azure SQL Database lub Tworzenie bazy danych w programie SQL Server.
• Opcjonalne, ale zalecane, zwłaszcza jeśli baza danych znajduje się na platformie Azure: magazyn kluczy w usłudze Azure Key Vault. Aby uzyskać informacje na temat tworzenia magazynu kluczy, zobacz Szybki start: tworzenie magazynu kluczy przy użyciu witryny Azure Portal.
  • Jeśli magazyn kluczy korzysta z modelu uprawnień zasad dostępu, upewnij się, że masz następujące uprawnienia klucza w magazynie kluczy: get, , listcreate, unwrap keywrap key, , . verifysign Zobacz Przypisywanie zasad dostępu do usługi Key Vault.
  • Jeśli używasz modelu uprawnień kontroli dostępu opartej na rolach (RBAC) platformy Azure, upewnij się, że jesteś członkiem roli Crypto Officer usługi Key Vault dla magazynu kluczy. Zobacz Zapewnianie dostępu do kluczy, certyfikatów i wpisów tajnych usługi Key Vault przy użyciu kontroli dostępu opartej na rolach platformy Azure.
• Najnowsza wersja programu SQL Server Management Studio (SSMS) lub najnowsza wersja modułów SqlServer i Az programu PowerShell. Moduł Az programu PowerShell jest wymagany tylko wtedy, gdy używasz usługi Azure Key Vault.

Krok 1. Tworzenie i wypełnianie schematu bazy danych

W tym kroku utworzysz schemat KADR i tabelę Employees . Następnie wypełnisz tabelę danymi.

SSMS

1. Połącz się z bazą danych. Aby uzyskać instrukcje dotyczące nawiązywania połączenia z bazą danych za pomocą programu SSMS, zobacz Szybki start: nawiązywanie połączenia z usługą Azure SQL Database lub zarządzanym wystąpieniem Azure SQL Database lub Azure SQL Managed Instance przy użyciu programu SQL Server Management Studio (SSMS), lub Szybki start: nawiązywanie połączenia i wykonywanie zapytań dotyczących wystąpienia programu SQL Server przy użyciu programu SQL Server Management Studio (SSMS).

2. Otwórz nowe okno zapytania dla bazy danych ContosoHR .

3. Wklej i wykonaj poniższe instrukcje, aby utworzyć nową tabelę o nazwie Employees.

   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. Wklej i wykonaj poniższe instrukcje, aby dodać kilka rekordów pracowników do tabeli Employees .

   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
   );
   

PowerShell

W sesji programu PowerShell wykonaj następujące polecenia. Upewnij się, że parametry połączenia zostały zaktualizowane przy użyciu adresu serwera i ustawień uwierzytelniania, które są prawidłowe dla bazy danych.

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

Krok 2. Szyfrowanie kolumn

W tym kroku utworzysz klucz główny kolumny i klucz szyfrowania kolumny dla funkcji Always Encrypted. Następnie zaszyfrujesz kolumny SSN i Salary w tabeli Employees .

SSMS

Program SSMS udostępnia kreatora, który ułatwia konfigurację Always Encrypted, umożliwiając ustawienie klucza głównego kolumny, klucza szyfrowania kolumny oraz szyfrowanie wybranych kolumn.

1. W Eksploratorze obiektów rozwiń węzeł Bazy danych>ContosoHR>Tabele.

2. Kliknij prawym przyciskiem myszy tabelę Employees (Pracownicy ) i wybierz polecenie Encrypt Columns (Szyfruj kolumny ), aby otworzyć kreatora Always Encrypted.

   

3. Wybierz pozycję Dalej na stronie Wprowadzenie kreatora.

4. Na stronie Wybór kolumny .

   1. Wybierz kolumny SSN i Salary . Wybierz szyfrowanie deterministyczne dla kolumny SSN i losowe szyfrowanie dla kolumny Wynagrodzenie . Szyfrowanie deterministyczne obsługuje zapytania, takie jak wyszukiwanie punktów, które obejmują porównania równości w zaszyfrowanych kolumnach. Szyfrowanie losowe nie obsługuje żadnych obliczeń w zaszyfrowanych kolumnach.
   2. Pozostaw CEK-Auto1 (nowy) jako klucz szyfrowania kolumny dla obu kolumn. Ten klucz jeszcze nie istnieje i zostanie wygenerowany przez kreatora.
   3. Wybierz Dalej.

   

5. Na stronie Konfiguracja klucza głównego skonfiguruj nowy klucz główny kolumny, który zostanie wygenerowany przez kreatora. Najpierw musisz wybrać miejsce przechowywania klucza głównego kolumny. Kreator obsługuje dwa typy magazynów kluczy:

   • Azure Key Vault — zalecane, jeśli baza danych znajduje się na platformie Azure
   • Magazyn certyfikatów systemu Windows

   Ogólnie rzecz biorąc, usługa Azure Key Vault jest zalecaną opcją, zwłaszcza jeśli baza danych znajduje się na platformie Azure.

   • Aby użyć usługi Azure Key Vault:

     1. Wybierz pozycję Azure Key Vault.
     2. Wybierz pozycję Zaloguj się i zakończ logowanie do platformy Azure.
     3. Po zalogowaniu, na stronie zostanie wyświetlona lista subskrypcji oraz magazynów kluczy, do których masz dostęp. Wybierz subskrypcję platformy Azure zawierającą magazyn kluczy, której chcesz użyć.
     4. Wybierz magazyn kluczy.
     5. Wybierz Dalej.

     

   • Aby użyć magazynu certyfikatów systemu Windows:

     1. Wybierz Magazyn certyfikatów systemu Windows.

     2. Pozostaw domyślny wybór Bieżący użytkownik — spowoduje to, że kreator wygeneruje certyfikat (twój nowy klucz główny kolumny) w repozytorium Bieżący użytkownik.

        

     3. Wybierz Dalej.

6. Na stronie Ustawienia szyfrowania na miejscu nie jest wymagana żadna dodatkowa konfiguracja, ponieważ baza danych nie ma włączonej enklawy. Wybierz Dalej.

7. Na stronie Ustawienia uruchamiania zostanie wyświetlony monit o kontynuowanie szyfrowania lub wygenerowanie skryptu programu PowerShell do wykonania później. Pozostaw ustawienia domyślne i wybierz pozycję Dalej.

8. Na stronie Podsumowanie kreator informuje o akcjach, które zostaną wykonane. Sprawdź, czy wszystkie informacje są poprawne, a następnie wybierz pozycję Zakończ.

9. Na stronie Wyniki możesz monitorować postęp operacji kreatora. Poczekaj, aż wszystkie operacje zakończą się pomyślnie, a następnie wybierz pozycję Zamknij.

   

10. (Opcjonalnie) Zapoznaj się ze zmianami, które kreator wprowadził w bazie danych.

    1. Rozwiń ContosoHR>Security>Klucze Always Encrypted, aby eksplorować obiekty metadanych dla klucza głównego kolumny i szyfrowanie kolumny, które utworzył kreator.

    2. Możesz również uruchomić poniższe zapytania względem widoków wykazu systemu, które zawierają metadane klucza.

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. W Eksploratorze obiektów kliknij prawym przyciskiem myszy tabelę Employees i wybierz polecenie Skryptuj tabelę jako>Utwórz do>Nowe okno edytora zapytań. Spowoduje to otwarcie nowego okna zapytania z instrukcją CREATE TABLE dla tabeli Employees . Zwróć uwagę na klauzulę ENCRYPTED WITH wyświetlaną w definicjach kolumn SSN i Salary .

    4. Możesz również uruchomić poniższe zapytanie względem elementu sys.columns , aby pobrać metadane szyfrowania na poziomie kolumny dla dwóch zaszyfrowanych kolumn.

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. Utwórz klucz główny kolumny w magazynie kluczy.

   • Jeśli używasz usługi Azure Key Vault, wykonaj poniższe polecenia, aby utworzyć klucz asymetryczny w magazynie kluczy. Upewnij się, że podajesz prawidłowy identyfikator subskrypcji, nazwę grupy zasobów zawierającej magazyn kluczy i nazwę magazynu kluczy.

     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
     

   • Jeśli używasz magazynu certyfikatów systemu Windows, wykonaj poniższe polecenia, aby utworzyć certyfikat w magazynie Bieżący użytkownik.

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

2. Nawiąż połączenie z bazą danych przy użyciu modułu SqlServer PowerShell. Upewnij się, że podaj prawidłowe parametry połączenia dla bazy danych.

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. Udostępnij w bazie danych obiekt metadanych klucza głównego kolumny, który odwołuje się do fizycznego klucza głównego kolumny utworzonego w magazynie kluczy.

   • Jeśli używasz usługi Azure Key Vault, wykonaj poniższe polecenia.

     # 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
     

   • Jeśli używasz magazynu certyfikatów systemu Windows, wykonaj poniższe polecenia.

     # 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. Wygeneruj klucz szyfrowania kolumny, zaszyfruj go przy użyciu utworzonego klucza głównego kolumny i utwórz obiekt metadanych klucza szyfrowania kolumny w bazie danych.

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

5. Szyfruj kolumny SSN i Wynagrodzenia w tabeli Employees . Wybierz szyfrowanie deterministyczne dla kolumny SSN i losowe szyfrowanie dla kolumny Wynagrodzenie . Szyfrowanie deterministyczne obsługuje zapytania, takie jak wyszukiwanie punktów, które obejmują porównania równości w zaszyfrowanych kolumnach. Szyfrowanie losowe nie obsługuje żadnych obliczeń w zaszyfrowanych kolumnach.

   # 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. (Opcjonalnie) Zapoznaj się ze zmianami, które zostały wprowadzone w bazie danych.

   • Uruchom poniższe polecenia, aby wykonać zapytanie dotyczące widoków wykazu systemu zawierającego metadane dotyczące klucza głównego kolumny i utworzonego klucza szyfrowania kolumny.

     $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
     

   • Uruchom poniższe polecenia, aby wykonać zapytanie sys.columns , aby pobrać metadane szyfrowania na poziomie kolumny dla dwóch zaszyfrowanych kolumn.

     $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
     

Krok 3. Wykonywanie zapytań względem zaszyfrowanych kolumn

SSMS

1. Nawiąż połączenie z bazą danych z wyłączoną funkcją Always Encrypted dla połączenia.

   1. Otwórz nowe okno zapytania.
   2. Kliknij prawym przyciskiem myszy w dowolnym miejscu w oknie zapytania i wybierz polecenie Zmień połączenie>. To spowoduje otwarcie okna dialogowego Łączenie z silnikiem bazy danych.
   3. Wybierz Opcje<<. Spowoduje to wyświetlenie dodatkowych kart w oknie dialogowym Łączenie z silnikiem bazy danych.
   4. Wybierz kartę Always Encrypted .
   5. Upewnij się, że pozycja Włącz funkcję Always Encrypted (szyfrowanie kolumn) nie jest zaznaczona.
   6. Wybierz i podłącz.

   

2. Wklej i wykonaj następujące zapytanie. Zapytanie powinno zwracać dane zaszyfrowane binarne.

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

   

3. Połącz się z bazą danych z włączoną funkcją Always Encrypted dla połączenia.

   1. Kliknij prawym przyciskiem myszy w dowolnym miejscu w oknie zapytania i wybierz Zmień połączenie. Spowoduje to otwarcie okna dialogowego Łączenie z silnikiem bazy danych.
   2. Wybierz Opcje<<. Spowoduje to wyświetlenie dodatkowych kart w oknie dialogowym Łączenie z silnikiem bazy danych.
   3. Wybierz kartę Always Encrypted .
   4. Wybierz pozycję Włącz funkcję Always Encrypted (szyfrowanie kolumn).
   5. Wybierz i podłącz.

   

4. Uruchom ponownie to samo zapytanie. Ponieważ połączenie z funkcją Always Encrypted jest włączone dla połączenia z bazą danych, sterownik klienta w programie SSMS podejmie próbę odszyfrowania danych przechowywanych w obu zaszyfrowanych kolumnach. Jeśli używasz usługi Azure Key Vault, może zostać wyświetlony monit o zalogowanie się do platformy Azure.

   

5. Włącz parametryzacja dla funkcji Always Encrypted. Ta funkcja umożliwia uruchamianie zapytań filtrujących dane według zaszyfrowanych kolumn (lub wstawiania danych do zaszyfrowanych kolumn).

   1. Wybierz pozycję Zapytanie z menu głównego programu SSMS.
   2. Wybierz pozycję Opcje zapytania....
   3. Przejdź do wykonywanie>Zaawansowane.
   4. Upewnij się, że opcja Włącz parametryzacja dla funkcji Always Encrypted jest zaznaczona.
   5. Kliknij przycisk OK.

   

6. Wklej i wykonaj poniższe zapytanie, które filtruje dane według zaszyfrowanej kolumny SSN . Zapytanie powinno zwrócić jeden wiersz zawierający wartości zwykłego tekstu.

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

7. Opcjonalnie, jeśli używasz usługi Azure Key Vault skonfigurowanej z modelem uprawnień zasad dostępu, wykonaj poniższe kroki, aby zobaczyć, co się stanie, gdy użytkownik spróbuje pobrać dane w postaci zwykłego tekstu z zaszyfrowanych kolumn bez dostępu do klucza głównego kolumny chroniącego dane.

   1. Usuń kluczowe uprawnienie unwrap dla siebie w zasadach dostępu dla sejfu kluczy. Aby uzyskać więcej informacji, zobacz Przypisywanie zasad dostępu do usługi Key Vault.
   2. Ponieważ sterownik klienta w programie SSMS buforuje klucze szyfrowania kolumny uzyskane z magazynu kluczy przez 2 godziny, zamknij program SSMS i otwórz go ponownie. Zapewni to, że pamięć podręczna kluczy jest pusta.
   3. Połącz się z bazą danych z włączoną funkcją Always Encrypted dla połączenia.
   4. Wklej i wykonaj następujące zapytanie. Zapytanie powinno zakończyć się niepowodzeniem z komunikatem o błędzie wskazującym, że brakuje wymaganych unwrap uprawnień.

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

PowerShell

1. Połącz się z bazą danych z wyłączoną funkcją Always Encrypted i uruchom zapytanie w celu odczytu danych z zaszyfrowanych kolumn. Zapytanie powinno zwracać zaszyfrowane dane jako tablice binarne.

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

2. Połącz się z bazą danych z włączoną funkcją Always Encrypted i uruchom zapytanie w celu odczytu danych z zaszyfrowanych kolumn. Ponieważ masz dostęp do klucza głównego kolumny chroniącego zaszyfrowane kolumny, zapytanie powinno zwracać dane w postaci zwykłego tekstu.

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

Uwaga / Notatka

Funkcja Invoke-SqlCmd nie obsługuje zapytań, które mogą filtrować według lub wstawiać dane do zaszyfrowanych kolumn. Takie zapytania muszą być sparametryzowane, a funkcja Invoke-SqlCmd nie obsługuje zapytań sparametryzowanych.

Dalsze kroki

• Tworzenie aplikacji przy użyciu usługi Always Encrypted

Zobacz także

• Dokumentacja funkcji Always Encrypted
• Dokumentacja funkcji Always Encrypted z użyciem bezpiecznych enklaw
• Aprowizuj klucze Always Encrypted przy użyciu programu SQL Server Management Studio
• Konfigurowanie funkcji Always Encrypted przy użyciu programu PowerShell
• Asystent Always Encrypted
• Kolumny zapytań przy użyciu funkcji Always Encrypted z programem SQL Server Management Studio