Выполнение запросов к столбцам с помощью Always Encrypted с использованием SQL Server Management Studio

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описывается выполнение запросов к столбцам, зашифрованным с помощью Always Encrypted, с использованием SQL Server Management Studio (SSMS). С помощью SSMS вы можете выполнять следующие задачи:

• извлечение значений зашифрованных данных, хранящихся в зашифрованных столбцах;
• извлечение значений открытого текста, хранящихся в зашифрованных столбцах;
• отправка значений открытого текста, предназначенных для зашифрованных столбцов (например, в инструкциях INSERT или UPDATE и в инструкциях SELECT в виде параметра поиска в предложениях WHERE).

Извлечение зашифрованных данных, хранящихся в зашифрованных столбцах

Выполнение запросов SELECT, которые извлекают зашифрованный текст данных, хранящихся в зашифрованных столбцах (без расшифровки данных), не требуют доступа к главным ключам столбцов, защищающим данные. Чтобы извлечь значения в виде зашифрованного текста в среде SSMS сделайте следующее.

1. Убедитесь, что вы можете получить доступ к метаданным о ключах, защищающих столбцы, для которых выполняется запрос. Хотя вам не нужно иметь доступ к фактическим главным ключам столбцов, вам требуются разрешения на уровне базы данных для просмотра объектов метаданных ключа шифрования столбцов и ключей шифрования столбцов в базе данных. Дополнительные сведения см. в разделе Разрешения для выполнения запросов к зашифрованным столбцам ниже.
2. Убедитесь, что вы отключили Always Encrypted для подключения к базе данных для окна Редактор запросов, из которого вы запустите SELECT запрос, извлекающий значения шифра. См. раздел Включение и отключение функции Always Encrypted, применяемой для подключения к базе данных ниже.
3. Выполните запрос SELECT. Любые данные, полученные из зашифрованных столбцов, возвращаются в виде двоичных (зашифрованных) значений.

Пример получения шифров

Предположим, что SSN — это зашифрованный столбец в таблице Patients . В этом случае если функция Always Encrypted, применяемая для подключения к базе данных, отключена, то выполнив приведенный ниже запрос, вы получите двоичные значения зашифрованных данных.

Извлечение значений открытого текста, хранящихся в зашифрованных столбцах

Чтобы извлечь значения открытого текста из зашифрованного столбца (а затем расшифровать их), сделайте следующее:

1. Убедитесь, что вы можете получить доступ к главным ключам столбца и метаданным о ключах, защищающих столбцы, для которых выполняется запрос. Дополнительные сведения см. в разделе Разрешения для выполнения запросов к зашифрованным столбцам ниже.
2. Убедитесь, что вы включили Always Encrypted для подключения к базе данных для окна Редактор запросов, из которого будет выполняться SELECT запрос, извлекаемый и расшифровывающий данные. Он указывает поставщику данных .NET Framework для SQL Server (используемому средой SSMS) расшифровать зашифрованные столбцы в результирующем наборе запроса. См. раздел Включение и отключение функции Always Encrypted, применяемой для подключения к базе данных ниже.
3. Выполните запрос SELECT. Все данные, полученные из зашифрованных столбцов, возвращаются в виде значений открытого текста исходных типов данных.

Пример получения открытого текста

Предполагая, что SSN является зашифрованным char(11) столбцом в Patients таблице, запрос, показанный ниже, вернет значения открытого текста, если always Encrypted включен для подключения к базе данных и если у вас есть доступ к главному ключу столбца, настроенного для столбца SSN .

Отправка значений открытого текста, предназначенных для зашифрованных столбцов

Чтобы выполнить запрос на отправку значения в зашифрованный столбец, например запрос на вставку, обновление или фильтрацию значений, хранящихся в зашифрованном столбце, сделайте следующее:

1. Убедитесь, что у вас есть доступ к главным ключам столбцов и метаданным для ключей, защищающих столбцы, к которым выполняется запрос. Дополнительные сведения см. в разделе Разрешения для выполнения запросов к зашифрованным столбцам ниже.

2. Убедитесь, что вы включили Always Encrypted для подключения к базе данных для окна Редактор запросов, из которого будет выполняться SELECT запрос, извлекаемый и расшифровывающий данные. Он указывает поставщику данных .NET Framework для SQL Server (используемому средой SSMS) расшифровать зашифрованные столбцы в результирующем наборе запроса. См. раздел Включение и отключение функции Always Encrypted, применяемой для подключения к базе данных ниже.

3. Включите параметризацию для Always Encrypted в окне редактора запросов. (Требуется по крайней мере SSMS версии 17.0.) Объявите переменную Transact-SQL и инициализируете ее со значением, вы хотите отправить (вставка, обновление или фильтрация) в базу данных. Дополнительные сведения см. в разделе Параметризация для Always Encrypted ниже.

4. Выполните запрос, чтобы отправить значение переменной Transact-SQL в базу данных. SQL Server Management Studio преобразует эту переменную в параметр запроса, значение которого шифруется перед отправкой в базу данных.

Пример

Предположим, что SSN — это зашифрованный столбец char(11) в таблице Patients . В этом случае если функция Always Encrypted, применяемая для подключения к базе данных, включена, в окне редактора запросов включена параметризация для Always Encrypted и при этом у вас есть доступ к главному ключу, настроенному для столбца '795-73-9838' , то приведенный ниже скрипт произведет попытку найти строку, содержащую значение LastName в столбце SSN, и вернет значение столбца SSN .

Разрешения для выполнения запросов к зашифрованным столбцам

Чтобы выполнять любые запросы к зашифрованным столбцам, в том числе запросы на извлечение зашифрованных данных, в базе данных необходимо иметь разрешения VIEW ANY COLUMN MASTER KEY DEFINITION и VIEW ANY COLUMN ENCRYPTION KEY DEFINITION .

Помимо указанных выше разрешений, чтобы расшифровать любые результаты и зашифровать параметры запроса (созданные в процессе параметризации переменных Transact-SQL), также требуются разрешения для хранилища ключей на доступ к главному ключу для защиты целевых столбцов и на использование такого ключа. Дополнительные сведения о разрешениях для хранилища ключей см. в статье Создание и хранение главных ключей столбцов для Always Encrypted в разделе о нужном хранилище.

Включение и отключение функции Always Encrypted, применяемой для подключения к базе данных

При подключении к базе данных в среде SSMS можно включить или отключить функцию Always Encrypted, применяемую для подключения к базе данных. По умолчанию функция Always Encrypted отключена.

После включения функции Always Encrypted, применяемой для подключения к базе данных, поставщик данных .NET Framework для SQL Server, используемый в SQL Server Management Studio, получает установку на прозрачное выполнение следующих действий:

• расшифровка любых значений, полученных из зашифрованных столбцов и возращенных в результатах запроса;
• шифрование значений параметризованных переменных Transact-SQL, предназначенных для зашифрованных столбцов базы данных.

Если функция Always Encrypted, применяемая для подключения к базе данных, не включена, поставщик данных .NET Framework для SQL Server, используемый средой SSMS, не будет пытаться зашифровать параметры запроса или расшифровать результаты.

Always Encrypted можно включить или отключить при создании подключения или изменении существующего подключения в диалоговом окне Соединение с сервером.

Чтобы включить или отключить Always Encrypted, выполните следующие действия.

1. Откройте диалоговое окно Соединение с сервером (см. статью Подключение к экземпляру SQL Server).
2. Выберите Параметры.
3. Выберите вкладку Always Encrypted. Чтобы включить Always Encrypted, выберите включить Always Encrypted (шифрование столбцов). Чтобы отключить Always Encrypted, снимите флажок Включить Always Encrypted (шифрование столбца).
4. Нажмите Подключиться.

Параметризация для Always Encrypted

Параметризация для Always Encrypted — это функция в SQL Server Management Studio, которая автоматически преобразует переменные Transact-SQL в параметры запросов (экземпляры класса SqlParameter). (Требуются службы SSMS версии не ниже 17.0.) Это позволяет основному поставщику данных .NET Framework для SQL Server определять данные, предназначенные для зашифрованных столбцов, и шифровать эти данные перед отправкой в базу данных.

Без параметризации поставщик данных .NET Framework передает все инструкции, созданные в редакторе запросов, в виде непараметризованных запросов. Если запрос содержит литералы или переменные Transact-SQL, предназначенные для зашифрованных столбцов, поставщик данных .NET Framework для SQL Server не сможет определить и зашифровать их перед отправкой запроса в базу данных. В результате этого из-за несоответствия типов (между переменной Transact-SQ литерала открытого текста и зашифрованным столбцом) запрос завершится сбоем. Например, если столбец SSN зашифрован, без параметризации следующий запрос завершится сбоем.

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

Включение и отключение параметризации для Always Encrypted

По умолчанию параметризация для Always Encrypted отключена.

Чтобы включить или отключить параметризацию для Always Encrypted в текущем окне редактора запросов, выполните следующие действия:

1. В главном меню выберите Запрос .
2. Щелкните Параметры запроса....
3. Выберите Выполнение>Дополнительно.
4. Установите или снимите флажок Включить определение параметров для Always Encrypted.
5. Нажмите ОК.

Чтобы включить или отключить параметризацию для Always Encrypted в будущих окнах редактора запросов, выполните следующие действия:

1. В главном меню выберите Средства .
2. Выберите Параметры.
3. Выберите Выполнение запроса>SQL Server>Дополнительно.
4. Установите или снимите флажок Включить определение параметров для Always Encrypted.
5. Нажмите ОК.

При выполнении запроса в окне редактора запросов с включенной функцией Always Encrypted, применяемой для подключения к базе данных, но с отключенной параметризацией появится запрос на включение этой возможности.

Принципы работы параметризации для Always Encrypted

Если в окне редактора запросов включена и функция Always Encrypted, применяемая для подключения к базе данных, и параметризация для Always Encrypted, SQL Server Management Studio произведет попытку параметризовать переменные Transact-SQL, соответствующие следующим требованиям.

• Эти переменные объявлены и инициализированы в одной инструкции (встроенная инициализация). Параметризация переменных, объявленных с использованием разных инструкций SET, не выполняется.
• Эти переменные инициализированы с использованием одного литерала. Параметризация переменных, инициализированных с использованием выражений, в том числе любых операторов и функций, не выполняется.

Ниже приведены примеры переменных, которые параметризуются в SQL Server Management Studio.

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

Вот некоторые примеры переменных, параметризация которых не выполняется в SQL Server Management Studio.

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

Параметризация происходит успешно в следующих случаях:

• тип литерала, используемый для инициализации переменной, параметризация которой выполняется, должен совпадать с типом в объявлении переменной;
• если в качестве объявленного типа переменной используется тип даты или времени, переменную нужно инициализировать, используя строку в одном из следующих совместимых с ISO 8601 форматов.

Ниже приведены примеры объявлений переменных Transact-SQL, которые приводят к ошибкам параметризации переменных.

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   

SQL Server Management Studio использует технологию Intellisense, чтобы предоставлять сведения о том, для каких переменных можно определить параметры, а для каких эта операция завершится сбоем (с указанием причины).

Объявление переменной, для которой можно определить параметры, подчеркивается линией в редакторе запросов. Если навести указатель мыши на инструкцию объявления, помеченную подчеркиванием предупреждения, вы увидите результаты процесса параметризации, включая значения ключевых свойств результирующего объекта SqlParameter (переменная сопоставляется с): SqlDbType, Size, Precision, Scale, SqlValue. Кроме того, в представлении Список ошибок на вкладке Предупреждение можно просмотреть полный список всех параметризованных переменных. Чтобы открыть представление Список ошибок , выберите в главном меню Представление , а затем щелкните Список ошибок.

Если попытка определить параметры переменной в SQL Server Management Studio завершилась сбоем, объявление переменной будет помечено знаком ошибки. Если навести указатель мыши на помеченную инструкцию выражения, отобразятся результаты ошибки. Полный список ошибок параметризации для всех переменных можно просмотреть в представлении Список ошибок на вкладке Ошибка . Чтобы открыть представление Список ошибок , выберите в главном меню Представление , а затем щелкните Список ошибок.

На снимке экрана ниже приведены примеры шести объявлений переменных. Параметризация первых трех переменных в SQL Server Management Studio выполнена успешно. Три последние переменные не соответствуют необходимым требованиям. Поэтому они не были параметризованы в SQL Server Management Studio (их объявления не отмечены).

В приведенном ниже примере две переменные соответствуют требованиям, необходимым для параметризации, но этот процесс завершился сбоем, так как их инициализация выполнена неправильно.

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.

Следующие шаги

• Разработка приложений с помощью Always Encrypted

См. также

• Always Encrypted