Wykonywanie zapytań dotyczących kolumn przy użyciu funkcji Always Encrypted w programie SQL Server Management Studio

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

W tym artykule opisano sposób wykonywania zapytań dotyczących kolumn zaszyfrowanych przy użyciu Always Encrypted przy użyciu programu SQL Server Management Studio (SSMS). Za pomocą programu SSMS można wykonywać następujące czynności:

• Pobierz wartości szyfrowania tekstu przechowywane w zaszyfrowanych kolumnach.
• Pobierz wartości w postaci zwykłego tekstu przechowywane w zaszyfrowanych kolumnach.
• Wysyłaj wartości w postaci zwykłego tekstu przeznaczone dla zaszyfrowanych kolumn (na przykład w instrukcjach INSERT lub UPDATE oraz jako parametr odnośnika klauzuli WHERE w instrukcjach SELECT).

Notatka

Używanie kluczy wzorca kolumn przechowywanych w zarządzanym module HSM w usłudze Azure Key Vault wymaga programu SSMS 18.9 lub nowsza wersja.

Pobieranie wartości szyfrowania przechowywanych w zaszyfrowanych kolumnach

Uruchamianie zapytań SELECT pobierających szyfr danych przechowywanych w zaszyfrowanych kolumnach (bez odszyfrowywania danych) nie wymaga dostępu do kluczy głównych kolumn chroniących dane. Aby pobrać wartości z zaszyfrowanej kolumny jako szyfrowanego tekstu w programie SSMS:

1. Upewnij się, że masz dostęp do metadanych dotyczących kluczy chroniących kolumny, przeciwko którym uruchamiasz zapytanie. Mimo że nie musisz mieć dostępu do fizycznych kluczy głównych kolumn, musisz mieć uprawnienia na poziomie bazy danych, aby wyświetlić obiekty metadanych klucza głównego kolumny i klucza szyfrowania kolumny w bazie danych. Aby uzyskać szczegółowe informacje, zobacz Uprawnienia do wykonywania zapytań dotyczących zaszyfrowanych kolumn poniżej.
2. Upewnij się, że dla połączenia z bazą danych w oknie Edytora zapytań wyłączyłeś funkcję Always Encrypted, z którego uruchomisz zapytanie SELECT, aby pobrać wartości zaszyfrowanego tekstu. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.
3. Uruchom zapytanie SELECT. Wszystkie dane pobrane z zaszyfrowanych kolumn będą zwracane jako wartości binarne (zaszyfrowane).

Przykład pobierania tekstu szyfrowego

Zakładając, że SSN jest zaszyfrowaną kolumną w tabeli Patients, zapytanie pokazane poniżej pobierze wartości binarnego szyfrowania, jeśli funkcja Always Encrypted jest wyłączona dla połączenia z bazą danych.

Pobieranie wartości w postaci zwykłego tekstu przechowywanego w zaszyfrowanych kolumnach

Aby pobrać wartości z zaszyfrowanej kolumny jako zwykły tekst (aby odszyfrować wartości):

1. Upewnij się, że masz dostęp do kluczy głównych kolumn i metadanych dotyczących kluczy chroniących kolumny, względem których uruchamiasz zapytanie. Aby uzyskać szczegółowe informacje, zobacz Uprawnienia do wykonywania zapytań dotyczących zaszyfrowanych kolumn poniżej.
2. Upewnij się, że włączono funkcję Always Encrypted dla połączenia bazy danych w oknie Edytor zapytań, z którego uruchomisz zapytanie SELECT pobierania i odszyfrowywania danych. Spowoduje to poinstruowanie dostawcy danych programu .NET Framework dla programu SQL Server (używanego przez program SSMS) do odszyfrowania zaszyfrowanych kolumn w zestawie wyników zapytania. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.
3. Uruchom zapytanie SELECT. Wszystkie dane pobrane z zaszyfrowanych kolumn będą zwracane jako wartości w postaci zwykłego tekstu oryginalnych typów danych.

Pobieranie przykładu zwykłego tekstu

Zakładając, że SSN jest zaszyfrowaną kolumną char(11) w tabeli Patients, zapytanie przedstawione poniżej zwróci wartości w postaci zwykłego tekstu, jeśli funkcja Always Encrypted jest włączona dla połączenia z bazą danych i jeśli masz dostęp do klucza głównego kolumny skonfigurowanego dla kolumny SSN.

Wysyłanie wartości w postaci zwykłego tekstu dla zaszyfrowanych kolumn

Aby wykonać zapytanie, które wysyła wartość docelową zaszyfrowanej kolumny, na przykład zapytanie, które wstawia, aktualizuje lub filtruje według wartości przechowywanej w zaszyfrowanej kolumnie:

1. Upewnij się, że masz dostęp do kluczy głównych kolumn oraz metadanych kluczy, które chronią kolumny, na których wykonywane jest zapytanie. Aby uzyskać więcej informacji, zobacz Uprawnienia do wykonywania zapytań dotyczących zaszyfrowanych kolumn poniżej.

2. Upewnij się, że włączono funkcję Always Encrypted dla połączenia bazy danych w oknie Edytor zapytań, z którego uruchomisz zapytanie SELECT pobierania i odszyfrowywania danych. Spowoduje to poinstruowanie dostawcy danych programu .NET Framework dla programu SQL Server (używanego przez program SSMS) do odszyfrowania zaszyfrowanych kolumn w zestawie wyników zapytania. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.

3. Upewnij się, że parametryzacja funkcji Always Encrypted jest włączona w oknie Edytor zapytań. (Wymaga co najmniej programu SSMS w wersji 17.0). Zadeklaruj zmienną Transact-SQL i zainicjuj ją przy użyciu wartości, którą chcesz wysłać (wstawić, zaktualizować lub filtrować według) do bazy danych. Aby uzyskać szczegółowe informacje, zobacz Parameterization for Always Encrypted poniżej.

4. Uruchom zapytanie wysyłające wartość zmiennej Transact-SQL do bazy danych. Program SSMS przekonwertuje zmienną na parametr zapytania i zaszyfruje jego wartość przed wysłaniem jej do bazy danych.

Przykład

Zakładając, że SSN jest zaszyfrowaną kolumną char(11) w tabeli Patients, poniższy skrypt podejmie próbę znalezienia wiersza zawierającego '795-73-9838' w kolumnie SSN i zwróci wartość kolumny LastName, zapewniając, że funkcja Always Encrypted jest włączona dla połączenia z bazą danych, parametryzacja funkcji Always Encrypted jest włączona dla okna Edytor zapytań i masz dostęp do klucza głównego kolumny skonfigurowanego dla kolumny SSN.

Uprawnienia do wykonywania zapytań dotyczących zaszyfrowanych kolumn

Aby uruchamiać zapytania dotyczące zaszyfrowanych kolumn, w tym zapytań pobierających dane w postaci szyfrowania, potrzebne są uprawnienia VIEW ANY COLUMN MASTER KEY DEFINITION i VIEW ANY COLUMN ENCRYPTION KEY DEFINITION w bazie danych.

Oprócz powyższych uprawnień, aby odszyfrować wszystkie wyniki zapytania lub zaszyfrować wszystkie parametry zapytania (generowane przez parametryzowanie zmiennych Transact-SQL), musisz również mieć uprawnienia magazynu kluczy, aby uzyskać dostęp do klucza głównego kolumny i używać go do ochrony kolumn docelowych. Aby uzyskać szczegółowe informacje na temat uprawnień magazynu kluczy, przejdź do Tworzenie i przechowywanie kluczy głównych kolumn dla usługi Always Encrypted i znajdź sekcję odpowiednią dla magazynu kluczy.

Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych

Po nawiązaniu połączenia z bazą danych w programie SSMS można włączyć lub wyłączyć funkcję Always Encrypted dla połączenia z bazą danych. Domyślnie funkcja Always Encrypted jest wyłączona.

Włączenie Always Encrypted dla połączenia z bazą danych powoduje, że dostawca danych .NET Framework dla SQL Server używany przez SQL Server Management Studio próbuje transparentnie:

• Odszyfruj wszystkie wartości pobierane z zaszyfrowanych kolumn i zwracane w wynikach zapytania.
• Szyfruj wartości sparametryzowanych zmiennych Transact-SQL przeznaczonych dla zaszyfrowanych kolumn bazy danych.

Jeśli nie włączysz funkcji Always Encrypted dla połączenia, dostawca danych .NET Framework dla SQL Server, używany przez SSMS, nie będzie próbował szyfrować parametrów zapytania ani odszyfrowywać wyników.

Funkcję Always Encrypted można włączyć lub wyłączyć podczas tworzenia nowego połączenia lub zmienić istniejące połączenie przy użyciu okna dialogowego Połącz z serwerem.

Aby włączyć (wyłączyć) funkcję Always Encrypted:

1. Otwórz okno dialogowe Połącz z serwerem (aby uzyskać szczegółowe informacje, zobacz Połącz z instancją serwera SQL).
2. Wybierz Opcje .
3. Wybierz kartę Always Encrypted. Aby włączyć funkcję Always Encrypted, wybierz pozycję Włącz funkcję Always Encrypted (szyfrowanie kolumn). Aby wyłączyć Always Encrypted, upewnij się, że opcja Włącz Always Encrypted (szyfrowanie kolumn) nie jest zaznaczona.
4. Wybierz Connect.

Wskazówka

Aby przełączać się między włączoną i wyłączoną funkcją Always Encrypted dla istniejącego okienka Edytora zapytań:

1. Kliknij prawym przyciskiem myszy w dowolnym miejscu w oknie Edytor zapytań.
2. Wybierz pozycję Połączenie>Zmień połączenie.... Spowoduje to otwarcie okna dialogowego Połącz z serwerem dla bieżącego połączenia w oknie Edytora zapytań.
3. Włącz lub wyłącz funkcję Always Encrypted, wykonując powyższe kroki, a następnie kliknij pozycję Connect.

Notatka

Aby uruchomić instrukcje korzystające z bezpiecznej enklawy po stronie serwera podczas korzystania z Always Encrypted z bezpiecznymi enklawami, zobacz instrukcje Run Transact-SQL using secure enlaves.

Parametryzacja funkcji Always Encrypted

Parametryzacja funkcji Always Encrypted to funkcja w programie SQL Server Management Studio, która automatycznie konwertuje zmienne Transact-SQL na parametry zapytania (wystąpienia klasy SqlParameter). (Wymaga co najmniej programu SSMS w wersji 17.0). Dzięki temu podstawowy dostawca danych programu .NET Framework dla programu SQL Server wykrywa dane przeznaczone dla zaszyfrowanych kolumn i szyfruje takie dane przed wysłaniem ich do bazy danych.

Bez zastosowania parametryzacji dostawca danych programu .NET Framework przekazuje każde wyrażenie, które napiszesz w Edytorze zapytań, jako zapytanie niesparametryzowane. Jeśli zapytanie zawiera literały lub Transact-SQL zmienne przeznaczone dla zaszyfrowanych kolumn, dostawca danych programu .NET Framework dla programu SQL Server nie będzie mógł ich wykryć i zaszyfrować przed wysłaniem zapytania do bazy danych. W rezultacie zapytanie zakończy się niepowodzeniem z powodu niezgodności typu (między zmienną literału zwykłego tekstu Transact-SQL a zaszyfrowaną kolumną). Na przykład następujące zapytanie zakończy się niepowodzeniem bez parametryzacji, zakładając, że kolumna SSN jest zaszyfrowana.

DECLARE @SSN NCHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Włączanie i wyłączanie parametryzacji dla funkcji Always Encrypted

Parametryzacja funkcji Always Encrypted jest domyślnie wyłączona.

Aby włączyć/wyłączyć parametryzacja funkcji Always Encrypted dla bieżącego okna Edytora zapytań:

1. Wybierz pozycję Zapytanie z menu głównego.
2. Wybierz Opcje zapytania....
3. Przejdź do Execution>Advanced.
4. Wybierz lub usuń zaznaczenie Włącz parametryzacja dla funkcji Always Encrypted.
5. Wybierz pozycję OK.

Aby włączyć/wyłączyć parametryzacja funkcji Always Encrypted dla przyszłych okien edytora zapytań:

1. Wybierz pozycję Narzędzia z menu głównego.
2. Wybierz Opcje ....
3. Przejdź do Wykonywanie zapytań>programu SQL Server>Advanced.
4. Wybierz lub usuń zaznaczenie Włącz parametryzację dla Always Encrypted.
5. Wybierz pozycję OK.

Jeśli wykonasz zapytanie w oknie Edytora zapytań, które używa połączenia bazy danych z włączonym funkcją Always Encrypted, ale parametryzacja nie jest włączona dla okna Edytor zapytań, zostanie wyświetlony monit o jego włączenie.

Notatka

Parametryzacja funkcji Always Encrypted działa tylko w oknach Edytora zapytań, które używają połączeń bazy danych z włączoną funkcją Always Encrypted (zobacz Włączanie i wyłączanie parametryzacji dla funkcji Always Encrypted). Jeśli okno Edytor zapytań używa połączenia z bazą danych bez włączenia funkcji Always Encrypted, nie zostaną sparametryzowane żadne zmienne Transact-SQL.

Jak działa parametryzacja funkcji Always Encrypted

Jeśli dla okna Edytora zapytań włączono zarówno parametryzację Always Encrypted, jak i ustawienie Always Encrypted w połączeniu z bazą danych, program SQL Server Management Studio podejmie próbę sparametryzowania zmiennych Transact-SQL spełniających następujące warunki wstępne:

• Są deklarowane i inicjowane w tej samej instrukcji (inicjalizacja liniowa). Zmienne zadeklarowane przy użyciu oddzielnych instrukcji SET nie zostaną sparametryzowane.
• Inicjuje się przy użyciu jednego literału. Zmienne inicjowane przy użyciu wyrażeń, w tym operatorów lub funkcji, nie będą sparametryzowane.

Poniżej przedstawiono przykłady zmiennych, program SQL Server Management Studio sparametryzuje.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Oto kilka przykładów zmiennych, których program SQL Server Management Studio nie podejmie próby sparametryzowania:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

Aby próba parametryzacji powiodła się:

• Typ literału używanego do inicjowania zmiennej do parametryzacji musi być zgodny z typem w deklaracji zmiennej.
• Jeśli zadeklarowany typ zmiennej jest typem daty lub typem godziny, zmienna musi zostać zainicjowana przy użyciu ciągu przy użyciu jednego z następujących formatów zgodnych z normą ISO 8601 .

Oto przykłady deklaracji zmiennych Transact-SQL, które spowodują błędy parametryzacji:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable   

Program SQL Server Management Studio używa funkcji IntelliSense, aby poinformować, które zmienne można pomyślnie sparametryzować i które próby parametryzacji kończą się niepowodzeniem (i dlaczego).

Deklaracja zmiennej, którą można pomyślnie sparametryzować, jest oznaczona znakiem podkreślenia ostrzeżenia w Edytorze zapytań. Jeśli zatrzymasz wskaźnik myszy na deklaracji oznaczonej podkreśleniem ostrzegawczym, zobaczysz wyniki procesu parametryzacji, w tym wartości kluczowych właściwości wynikowego obiektu SqlParameter (do którego zmienna jest mapowana): SqlDbType, Size, Precision, Scale, SqlValue. Możesz również wyświetlić pełną listę wszystkich zmiennych, które zostały pomyślnie sparametryzowane w zakładce Ostrzeżenie w widoku listy błędów . Aby otworzyć widok listy błędów , wybierz pozycję Widok z menu głównego, a następnie wybierz pozycję lista błędów .

Jeśli program SQL Server Management Studio próbował sparametryzować zmienną, ale parametryzacja nie powiodła się, deklaracja zmiennej zostanie oznaczona podkreślonym błędem. Jeśli zatrzymasz wskaźnik myszy na deklaracji, która została oznaczona podkreśleniem błędu, otrzymasz wyniki dotyczące błędu. Można również zobaczyć pełną listę błędów parametryzacji dla wszystkich zmiennych w zakładce Błąd w widoku listy błędów . Aby otworzyć widok Lista błędów , wybierz pozycję Widok z menu głównego, a następnie wybierz pozycję Lista błędów .

Poniższy zrzut ekranu przedstawia przykład sześciu deklaracji zmiennych. Program SQL Server Management Studio pomyślnie sparametryzował pierwsze trzy zmienne. Ostatnie trzy zmienne nie spełniały warunków wymagań wstępnych dla parametryzacji, dlatego program SQL Server Management Studio nie próbował ich sparametryzować (ich deklaracje nie są w żaden sposób oznaczone).

W innym przykładzie poniżej przedstawiono dwie zmienne spełniające warunki wstępne parametryzacji, ale próba parametryzacji nie powiodła się, ponieważ zmienne są niepoprawnie inicjowane.

Notatka

Ponieważ funkcja Always Encrypted obsługuje ograniczony podzestaw konwersji typów, w wielu przypadkach wymagane jest, aby typ danych zmiennej Transact-SQL był taki sam jak typ docelowej kolumny bazy danych. Na przykład przy założeniu, że typ kolumny SSN w tabeli Patients jest char(11), poniższe zapytanie zakończy się niepowodzeniem, ponieważ typ zmiennej @SSN, która jest nchar(11), nie jest zgodna z typem kolumny.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Notatka

Bez parametryzacji całe zapytanie, w tym konwersje typów, jest przetwarzane wewnątrz programu SQL Server/usługi Azure SQL Database. Po włączeniu parametryzacji niektóre konwersje typów są wykonywane przez program .NET Framework w programie SQL Server Management Studio. Ze względu na różnice między systemem typów .NET Framework i systemem typów programu SQL Server (np. inną precyzją niektórych typów, takich jak zmiennoprzecinkowe), zapytanie wykonywane z włączoną parametryzacją może wygenerować inne wyniki niż zapytanie wykonane bez włączenia parametryzacji.

Następne kroki

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

Zobacz też

• Zawsze Szyfrowane