Wykonywanie zapytań dotyczących kolumn przy użyciu funkcji Always Encrypted w narzędziu Azure Data Studio

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

W tym artykule opisano sposób wykonywania zapytań dotyczących kolumn zaszyfrowanych za pomocą Always Encrypted przy użyciu Azure Data Studio. Za pomocą narzędzia Azure Data Studio 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).

Pobieranie wartości szyfrowania przechowywanych w zaszyfrowanych kolumnach

W tej sekcji opisano sposób pobierania danych przechowywanych w zaszyfrowanych kolumnach jako tekst zaszyfrowany.

Kroki

1. Upewnij się, że funkcja Always Encrypted jest wyłączona dla połączenia bazy danych w oknie zapytania, z którego uruchomisz zapytanie SELECT pobierające zaszyfrowane wartości. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.
2. Uruchom zapytanie SELECT. Wszystkie dane pobrane z zaszyfrowanych kolumn będą zwracane jako wartości binarne (zaszyfrowane).

Przykład

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

W tej sekcji opisano sposób pobierania danych przechowywanych w zaszyfrowanych kolumnach jako tekst zaszyfrowany.

Warunki wstępne

• Azure Data Studio w wersji 17.1 lub nowszej.
• Musisz mieć 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.
• Klucze główne kolumny muszą być przechowywane w magazynie kluczy w usłudze Azure Key Vault lub Magazynie certyfikatów systemu Windows. Usługa Azure Data Studio nie obsługuje innych magazynów kluczy i nie obsługuje kluczy głównych kolumn przechowywanych w zarządzanych modułach HSM w usłudze Azure Key Vault.

Kroki

1. Włącz Always Encrypted dla połączenia z bazą danych dla okna zapytań, z którego uruchomisz zapytanie SELECT w celu pobrania i odszyfrowania danych. To polecenie spowoduje, że Microsoft .NET Data Provider for SQL Server (używany przez program Azure Data Studio) odszyfruje zaszyfrowane kolumny w zestawie wyników zapytania. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.
2. Uruchom zapytanie SELECT. Wszystkie dane pobrane z zaszyfrowanych kolumn będą zwracane jako wartości w postaci zwykłego tekstu oryginalnych typów danych.

Przykład

Przy założeniu, że SSN jest zaszyfrowaną kolumną 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, a 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

W tej sekcji opisano sposób uruchamiania zapytania, które wysyła wartości docelowe zaszyfrowanej kolumny. Na przykład zapytanie, które wstawia, aktualizuje lub filtruje według wartości przechowywanej w zaszyfrowanej kolumnie:

Warunki wstępne

• Azure Data Studio w wersji 18.1 lub nowszej.
• Musisz mieć 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.
• Klucze główne dla kolumn muszą być przechowywane w usłudze Azure Key Vault lub w Sklepie Certyfikatów Windows. Usługa Azure Data Studio nie obsługuje innych magazynów kluczy i nie obsługuje kluczy głównych kolumn przechowywanych w zarządzanych modułach HSM w usłudze Azure Key Vault.

Kroki

1. Włącz funkcję Always Encrypted dla połączenia bazy danych w oknie zapytania, w którym uruchomisz zapytanie SELECT służące do pobierania i odszyfrowywania danych. Spowoduje to, że Microsoft .NET Data Provider dla SQL Server (używany przez Azure Data Studio) zaszyfruje parametry zapytania dotyczące zaszyfrowanych kolumn i odszyfruje wyniki pobrane z tych zaszyfrowanych kolumn. Zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych poniżej.
2. Włącz parametryzację dla funkcji "Always Encrypted" w oknie zapytania. Aby uzyskać szczegółowe informacje, zobacz Parameterization for Always Encrypted poniżej.
3. Zadeklaruj zmienną Transact-SQL i zainicjuj ją przy użyciu wartości, którą chcesz wysłać (wstawić, zaktualizować lub filtrować) do bazy danych.
4. Uruchom zapytanie wysyłające wartość zmiennej Transact-SQL do bazy danych. Program Azure Data Studio 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. Wyniki są zwracane, jeśli funkcja Always Encrypted jest włączona dla połączenia z bazą danych, parametryzacja funkcji Always Encrypted jest włączona dla okna zapytania i masz dostęp do klucza głównego kolumny skonfigurowanego dla kolumny SSN.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo].[Pacjenci] WHERE [SSN] = @SSN oraz wyników tego zapytania. />

Uprawnienia do wykonywania zapytań dotyczących zaszyfrowanych kolumn

Aby uruchomić wszystkie zapytania dotyczące zaszyfrowanych kolumn, w tym zapytań pobierających dane w formacie tekstu zaszyfrowanego, potrzebujesz WYŚWIETL DOWOLNĄ DEFINICJĘ KLUCZA GŁÓWNEGO KOLUMNY i WYŚWIETL DOWOLNĄ DEFINICJĘ KLUCZA SZYFROWANIA KOLUMNY uprawnień 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 Twojego 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 narzędziu Azure Data Studio możesz włączyć lub wyłączyć funkcję Always Encrypted dla połączenia z bazą danych. Domyślnie funkcja Always Encrypted jest wyłączona.

Włączenie funkcji Always Encrypted dla połączenia z bazą danych nakazuje Microsoft .NET Data Provider for SQL Server, używanemu przez program Azure Data Studio, do próby podjęcia działań w sposób przejrzysty:

• Odszyfruj wszystkie wartości pobrane z zaszyfrowanych kolumn i zwrócone 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 microsoft .NET dla programu SQL Server nie spróbuje zaszyfrować parametrów zapytania ani odszyfrować wyników.

Funkcję Always Encrypted można włączyć lub wyłączyć podczas nawiązywania połączenia z bazą danych. Aby uzyskać ogólne informacje na temat nawiązywania połączenia z bazą danych, zobacz:

• Szybki start: nawiązywanie połączenia z programem SQL Server i wykonywanie zapytań za pomocą programu Azure Data Studio
• Szybki start: nawiązywanie połączeń z bazą danych Azure SQL Database i wykonywanie zapytań względem nich za pomocą narzędzia Azure Data Studio

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

1. W oknie dialogowym Połączenie kliknij pozycję Zaawansowane....
2. Aby włączyć funkcję Zawsze Szyfrowane dla połączenia, ustaw pole Zawsze Szyfrowane na Włączone. Aby wyłączyć funkcję Always Encrypted, pozostaw wartość pola Always Encrypted puste lub ustaw je na wartość Disabled.
3. Kliknij przycisk OK, aby zamknąć Właściwości zaawansowane.

Aby uruchomić instrukcje korzystające z bezpiecznej enklawy po stronie serwera podczas korzystania z Always Encrypted z bezpiecznymi enklawami, należy określić protokół zaświadczania enklawy i adres URL zaświadczania enklawy, oprócz włączenia funkcji Always Encrypted dla połączenia. Aby uzyskać szczegółowe informacje, zobacz Wymagania wstępne dotyczące uruchamiania zapytań T-SQL przy użyciu enklaw w Azure Data Studio.

Napiwek

Aby przełączać się między włączonym i wyłączonym funkcją Always Encrypted dla istniejącego okna zapytania, kliknij przycisk Rozłącz, a następnie kliknij przycisk Połącz i wykonaj powyższe kroki, aby ponownie połączyć się z bazą danych przy użyciu żądanych wartości pola Always Encrypted.

Notatka

Przycisk Zmień połączenie w oknie zapytania nie obsługuje obecnie przełączania między włączoną i wyłączoną funkcją Always Encrypted.

Parametryzacja funkcji Always Encrypted

Parametryzacja funkcji Always Encrypted to funkcja w programie Azure Data Studio 18.1 lub nowszym, która automatycznie konwertuje zmienne Transact-SQL na parametry zapytania (wystąpienia klasy SqlParameter). Umożliwia to dostawcy danych Microsoft .NET dla SQL Server wykrywanie danych przeznaczonych dla zaszyfrowanych kolumn i szyfrowanie takich danych przed wysłaniem ich do bazy danych.

Bez parametryzacji dostawca danych microsoft .NET dla programu SQL Server przekazuje każdą instrukcję utworzoną w oknie zapytania 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ł wykryć i zaszyfrować ich przed wysłaniem zapytania do bazy danych. W rezultacie zapytanie zakończy się niepowodzeniem z powodu niezgodności typu (między literałem tekstu jawnego Transact-SQL a zmienną w zaszyfrowanej kolumnie). Na przykład następujące zapytanie zakończy się niepowodzeniem bez parametryzacji, zakładając, że kolumna SSN jest zaszyfrowana.

DECLARE @SSN CHAR(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:

1. Wybierz plików>Preferencje>Ustawienia (Preferencje kodu>Ustawienia>na Mac).
2. Przejdź do Data>Microsoft SQL Server.
3. Wybierz lub usuń zaznaczenie Włącz parametryzację funkcji Always Encrypted.
4. Zamknij okno Ustawienia.

Notatka

Parametryzacja funkcji Always Encrypted działa tylko w zapytaniu używającym połączeń bazy danych z włączoną funkcją Always Encrypted (zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych). Żadne zmienne Transact-SQL nie zostaną sparametryzowane, jeśli w oknie zapytania używane jest połączenie z bazą danych bez włączenia funkcji Always Encrypted.

Jak działa parametryzacja funkcji Always Encrypted

Jeśli dla okna zapytania włączono Parametryzację dla Always Encrypted oraz Always Encrypted, narzędzie Azure Data 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 w linii). Zmienne zadeklarowane przy użyciu oddzielnych instrukcji SET nie zostaną sparametryzowane.
• Inicjuje się je przy pomocy pojedynczego 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, które program Azure Data 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 Azure Data 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   

Usługa Azure Data Studio używa funkcji IntelliSense, aby poinformować, które zmienne mogą być pomyślnie sparametryzowane i które próby parametryzacji kończą się niepowodzeniem (i dlaczego).

Deklaracja zmiennej, którą można pomyślnie sparametryzować, jest oznaczona podkreśloną komunikatem informacyjnym w oknie zapytania. Jeśli zatrzymasz wskaźnik myszy na instrukcji deklaracyjnej, która została oznaczona podkreśleniem komunikatu, zobaczysz komunikat zawierający wyniki procesu parametryzacji, w tym wartości kluczowych właściwości wynikowego obiektu klasy SqlParameter (zmienna jest mapowana na: SqlDbType, Rozmiar, Precision, Scale, SqlValue). Możesz również wyświetlić pełną listę wszystkich zmiennych, które zostały pomyślnie sparametryzowane w widoku Problemy. Aby otworzyć widok Problemy, wybierz pozycję Wyświetl problemy>.

Jeśli usługa Azure Data Studio próbowała 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, uzyskasz informacje o błędzie. Pełną listę błędów parametryzacji można również wyświetlić dla wszystkich zmiennych w widoku Problemy.

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 w programie SQL Server/usłudze Azure SQL Database. Po włączeniu parametryzacji niektóre konwersje typów są wykonywane przez dostawcę danych microsoft .NET dla programu SQL Server w narzędziu Azure Data Studio. Ze względu na różnice między systemem typów Microsoft .NET i systemem typów SQL Server (na przykład różną precyzję niektórych typów, takich jak float), zapytanie wykonywane z włączoną parametryzacją może generować inne wyniki niż zapytanie wykonywane bez włączonej parametryzacji.

Następne kroki

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

Zobacz też

• Zawsze Szyfrowane