Использование функции Always Encrypted с драйвером ODBC для SQL Server

Скачать драйвер ODBC

Применимо к

• Драйвер ODBC 13.1+ для SQL Server

Введение

В этой статье содержатся сведения о разработке приложений ODBC с помощью Always Encrypted (ядро СУБД) или Always Encrypted с безопасными анклавами и ODBC Driver for SQL Server.

Функция Always Encrypted позволяет шифровать конфиденциальные данные в клиентских приложениях, не раскрывая данные или ключи шифрования для SQL Server или Базы данных SQL Azure. Драйвер с поддержкой Always Encrypted, такой как драйвер ODBC для SQL Server, реализует эту функцию безопасности за счет прозрачного шифрования и расшифровки конфиденциальных данных в клиентском приложении. Драйвер автоматически определяет, какие параметры запроса соответствуют важным столбцам базы данных (защищенным с помощью Always Encrypted), и шифрует значения этих параметров перед передачей данных в SQL Server или Базу данных SQL Azure. Аналогичным образом драйвер прозрачно расшифровывает данные, полученные из зашифрованных столбцов базы в результатах запроса. Always Encrypted с безопасными анклавами обеспечивает расширенные функции защиты конфиденциальных данных.

Дополнительные сведения см. в статьях об использовании Always Encrypted (ядро СУБД) и Always Encrypted с безопасными анклавами.

Предварительные требования

Настройте функцию постоянного шифрования в базе данных. В процесс настройки входят действия по подготовке ключей Always Encrypted и настройке шифрования для выбранных столбцов базы данных. Если у вас еще нет базы данных с настроенным Always Encrypted, следуйте инструкциям в руководстве по началу работы с Always Encrypted. В частности база данных должна содержать определения метаданных для главного ключа столбца (CMK), ключа шифрования столбца (CEK) и таблицы с одним или несколькими столбцами, зашифрованными с помощью этого ключа CEK.

Если вы используете Always Encrypted с безопасными анклавами, см. статью Разработка приложений с помощью Always Encrypted с безопасными анклавами со сведениями о дополнительных предварительных требованиях.

Включение Always Encrypted в приложении ODBC

Самый простой способ одновременно включить шифрование параметров и расшифровку зашифрованных столбцов в наборе результатов — установить для ключевого слова строки подключения ColumnEncryption значение Enabled. Ниже приведен пример кода со строкой подключения, которая позволяет включить Always Encrypted.

SQLWCHAR *connString = L"Driver={ODBC Driver 18 for SQL Server};Server={myServer};Encrypt=yes;Trusted_Connection=yes;ColumnEncryption=Enabled;";

Always Encrypted также можно включить в конфигурации DSN, указав те же ключ и значение (которые будут переопределены параметром строки подключения, если он указан), или включить программно с помощью атрибута предварительного подключения SQL_COPT_SS_COLUMN_ENCRYPTION. Если задать этот параметр таким образом, это переопределит значение, заданное в строке подключения или DSN:

 SQLSetConnectAttr(hdbc, SQL_COPT_SS_COLUMN_ENCRYPTION, (SQLPOINTER)SQL_COLUMN_ENCRYPTION_ENABLE, 0);

Включив Always Encrypted для подключения, вы можете изменять его поведение для отдельных запросов. Дополнительные сведения см. в разделе Управление влиянием Always Encrypted на производительность ниже.

Включенной функции Always Encrypted недостаточно для успешного шифрования или расшифровки. Вам нужно убедиться в следующем:

• У приложения есть разрешения базы данных VIEW ANY COLUMN MASTER KEY DEFINITION и VIEW ANY COLUMN ENCRYPTION KEY DEFINITION, которые необходимы для доступа к метаданным о ключах Always Encrypted в базе данных. Дополнительные сведения см. в описании разрешений базы данных.

• Приложение может получить доступ к CMK, который защищает ключи CEK для запрашиваемых зашифрованных столбцов. Это поведение зависит от поставщика хранилища ключей, который хранит CMK. См. подробнее о работе с хранилищами главных ключей столбцов.

Включение Always Encrypted с защищёнными анклавами

Начиная с версии 17.4 драйвер поддерживает Always Encrypted с безопасными анклавами. Чтобы включить использование анклава при подключении к базе данных, задайте ключ DSN ColumnEncryption, ключевое слово строки подключения или атрибут подключения, указав значение <attestation protocol>,<attestation URL>, где:

• <attestation protocol> — определяет протокол, используемый для аттестации анклава.

  • Если вы используете SQL Server и службу защиты узлов (HGS), <attestation protocol> должно быть VBS-HGS.
  • Если вы используете Базу данных SQL Azure и Microsoft Аттестация Azure, <attestation protocol> должно быть SGX-AAS.
  • Если аттестация не требуется, параметр <attestation-protocol> должен иметь значение VBS-NONE. (Версия 18.1 и выше)

• <attestation URL> — определяет URL-адрес аттестации (конечная точка службы аттестации). Необходимо получить URL-адрес аттестации для вашей среды у администратора службы аттестации.

  • Если вы используете SQL Server и Host Guardian Service (HGS), см. Определение URL-адреса аттестации HGS и предоставление общего доступа к нему.
  • Если вы используете База данных SQL Azure и Microsoft Аттестация Azure, см. Определение URL-адреса аттестации для политики аттестации.
  • Если аттестация не требуется, не указывайте ни URL-адрес аттестации, ни запятую перед ним. (Версия 18.1 и выше)

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

• SQL Server.

  "Driver=ODBC Driver 18 for SQL Server;Server=myServer.myDomain;Encrypt=yes;Database=myDataBase;Trusted_Connection=Yes;ColumnEncryption=VBS-HGS,http://myHGSServer.myDomain/Attestation"
  

• База данных SQL Azure:

  "Driver=ODBC Driver 18 for SQL Server;Server=myServer.database.windows.net;Database=myDataBase;Uid=myUsername;Pwd=<password>;Encrypt=yes;ColumnEncryption=SGX-AAS,https://myAttestationProvider.uks.attest.azure.net/"
  

• Без аттестации (версия 18.1 и выше):

  "Driver=ODBC Driver 18 for SQL Server;Server=myServer.database.windows.net;Database=myDataBase;Uid=myUsername;Pwd=<password>;Encrypt=yes;ColumnEncryption=VBS-NONE"
  

Если сервер и служба аттестации, а также ключи CMK и CEK с поддержкой анклава настроены правильно для зашифрованных столбцов, вы сможете выполнять запросы, использующие анклав, например шифрование на месте и расширенные вычисления, в дополнение к существующим функциональным возможностям, которые предоставляет Always Encrypted. Дополнительные сведения см. в статье Настройка Always Encrypted с безопасными анклавами.

Получение и изменение данных в зашифрованных столбцах

После включения Always Encrypted для подключения можно использовать стандартные API-интерфейсы ODBC. API-интерфейсы ODBC могут извлекать или изменять данные в зашифрованных столбцах базы данных. Вам могут быть полезны следующие ресурсы документации:

• Пример кода ODBC.
• Справочник по программированию ODBC

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

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

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

В следующих примерах показано получение и изменение данных в зашифрованных столбцах. В этом примере предполагается, что таблица имеет следующую схему. Столбцы SSN и BirthDate зашифрованы.

CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1),
 [SSN] [char](11) COLLATE Latin1_General_BIN2
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [BirthDate] [date]
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
 PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
 GO

Пример вставки данных

В этом примере в таблицу Patients вставляется строка. Заметьте следующие детали:

• В примере кода нет ничего, связанного с шифрованием. Драйвер автоматически определяет и шифрует значения параметров SSN и даты, которые соответствуют зашифрованным столбцам. Благодаря этому шифрование является прозрачным для приложения.

• Данные, вставленные в столбцы базы данных (в том числе в зашифрованные) передаются в качестве привязанных параметров (см. Функция SQLBindParameter). Несмотря на то, что при отправке значений в незашифрованные столбцы использовать параметры необязательно (но настоятельно рекомендуется, так как это помогает предотвратить внедрение кода SQL), они требуются для значений, предназначенных для зашифрованных столбцов. Если значения, вставленные в столбцы SSN или BirthDate, были переданы в качестве внедренных в инструкцию запроса литералов, выполнение запроса завершится ошибкой, так как драйвер не пытается шифровать или иным образом обрабатывать литералы в запросах. В результате сервер отклонит их как несовместимые с зашифрованными столбцами.

• Для параметров, вставляемых в столбец SSN, устанавливается тип SQL SQL_CHAR, который сопоставляется с типом данных SQL Server char (rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);). Если для параметра был задан тип SQL_WCHAR, который сопоставляется с типом данных nchar, выполнение запроса завершится ошибкой, так как Always Encrypted не поддерживает преобразования на стороне сервера из зашифрованных значений nchar в зашифрованные значения char. Сведения о сопоставлении типов данных см. в Справочнике программиста ODBC — приложение D: Типы данных.

    SQL_DATE_STRUCT date;
    SQLLEN cbdate;   // size of date structure  

    SQLCHAR SSN[12];
    strcpy_s((char*)SSN, _countof(SSN), "795-73-9838");

    SQLWCHAR* firstName = L"Catherine";
    SQLWCHAR* lastName = L"Abel";
    SQLINTEGER cbSSN = SQL_NTS, cbFirstName = SQL_NTS, cbLastName = SQL_NTS;

    // Initialize the date structure  
    date.day = 10;
    date.month = 9;
    date.year = 1996;

    // Size of structures   
    cbdate = sizeof(SQL_DATE_STRUCT);

    SQLRETURN rc = 0;

    string queryText = "INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate]) VALUES (?, ?, ?, ?) ";

    rc = SQLPrepare(hstmt, (SQLCHAR *)queryText.c_str(), SQL_NTS);

    //SSN
    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);
    //FirstName
    rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 50, 0, (SQLPOINTER)firstName, 0, &cbFirstName);
    //LastName
    rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 50, 0, (SQLPOINTER)lastName, 0, &cbLastName);
    //BirthDate
    rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TYPE_DATE, 10, 0, (SQLPOINTER)&date, 0, &cbdate);

    rc = SQLExecute(hstmt);

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

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

• Значение, используемое в предложении WHERE для фильтрации по столбцу SSN, необходимо передавать, используя SQLBindParameter, чтобы перед отправкой на сервер драйвер мог его прозрачно зашифровать.

• Все значения, выводимые программой, будут представлены в виде обычного текста, так как драйвер прозрачно расшифрует данные, полученные из столбцов SSN и BirthDate.

SQLCHAR SSN[12];
strcpy_s((char*)SSN, _countof(SSN), "795-73-9838");

SQLWCHAR* firstName = L"Catherine";
SQLWCHAR* lastName = L"Abel";
SQLINTEGER cbSSN = SQL_NTS, cbFirstName = SQL_NTS, cbLastName = SQL_NTS;

SQLRETURN rc = 0;
string empty = "";
string queryText = "SELECT [SSN], [FirstName], [LastName], [BirthDate] " + empty +
    "FROM  [dbo].[Patients]" +
    "WHERE " +
    "[SSN] = ? ";

rc = SQLPrepare(hstmt, (SQLCHAR *)queryText.c_str(), SQL_NTS);

//SSN
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);

rc = SQLExecute(hstmt);
HandleDiagnosticRecord(hstmt, SQL_HANDLE_STMT, rc);

SQL_DATE_STRUCT dateVal;
SQLWCHAR firstNameVal[50];
SQLWCHAR lastNameVal[50];
SQLCHAR SSNVal[12];
SQLLEN cbdate;   // size of date structure  

int rowcount = 0;
while (SQL_SUCCEEDED(SQLFetch(hstmt)))
{
    rowcount++;
    SQLGetData(hstmt, 1, SQL_C_CHAR, &SSNVal, 11, &cbSSN);
    SQLGetData(hstmt, 2, SQL_C_WCHAR, &firstNameVal, 50, &cbFirstName);
    SQLGetData(hstmt, 3, SQL_C_WCHAR, &lastNameVal, 50, &cbLastName);
    SQLGetData(hstmt, 4, SQL_C_TYPE_DATE, &dateVal, 10, &cbdate);        
}

Пример получения данных в виде зашифрованного текста

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

В приведенном ниже примере показано извлечение двоичных зашифрованных данных из зашифрованных столбцов. Заметьте следующие детали:

• Так как Always Encrypted не включается в строке подключения, запрос вернет зашифрованные значения SSN и BirthDate в виде байтовых массивов (программа преобразует эти значения в строки).
• Запрос, получающий данные из зашифрованных столбцов с отключенным постоянным шифрованием, может иметь параметры при условии, что ни один из параметров не предназначен для зашифрованного столбца. Приведенный выше запрос выполняет фильтрацию по LastName, который не зашифрован в базе данных. Запрос, отфильтрованный по SSN или BirthDate, завершится ошибкой.

SQLCHAR SSN[12];
strcpy_s((char*)SSN, _countof(SSN), "795-73-9838");

SQLWCHAR* firstName = L"Catherine";
SQLWCHAR* lastName = L"Abel";
SQLINTEGER cbSSN = SQL_NTS, cbFirstName = SQL_NTS, cbLastName = SQL_NTS;

SQLRETURN rc = 0;
string empty = "";
string queryText = "SELECT [SSN], [FirstName], [LastName], [BirthDate] " + empty +
    "FROM  [dbo].[Patients]" +
    "WHERE " +
    "[LastName] = ?";

rc = SQLPrepare(hstmt, (SQLCHAR *)queryText.c_str(), SQL_NTS);

//LastName
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 50, 0, (SQLPOINTER)lastName, 0, &cbLastName);

rc = SQLExecute(hstmt);
HandleDiagnosticRecord(hstmt, SQL_HANDLE_STMT, rc);

SQL_DATE_STRUCT dateVal;
SQLWCHAR firstNameVal[50];
SQLWCHAR lastNameVal[50];
SQLCHAR SSNVal[12];
SQLLEN cbdate;   // size of date structure  

int rowcount = 0;
while (SQL_SUCCEEDED(SQLFetch(hstmt)))
{
    rowcount++;
    SQLGetData(hstmt, 1, SQL_C_CHAR, &SSNVal, 11, &cbSSN);
    SQLGetData(hstmt, 2, SQL_C_WCHAR, &firstNameVal, 50, &cbFirstName);
    SQLGetData(hstmt, 3, SQL_C_WCHAR, &lastNameVal, 50, &cbLastName);
    SQLGetData(hstmt, 4, SQL_C_TYPE_DATE, &dateVal, 10, &cbdate);        
}

Шифрование Money/SmallMoney

Начиная с версии драйвера 17.7, функцию Always Encrypted можно использовать с типами MONEY и SMALLMONEY. Однако необходимо выполнить ряд дополнительных действий. При вставке данных в зашифрованные столбцы MONEY или SMALLMONEY используйте один из следующих типов C:

SQL_C_CHAR
SQL_C_WCHAR
SQL_C_SHORT
SQL_C_LONG
SQL_C_FLOAT
SQL_C_DOUBLE
SQL_C_BIT
SQL_C_TINYINT
SQL_C_SBIGINT
SQL_C_NUMERIC

и тип SQL SQL_NUMERIC или SQL_DOUBLE (при использовании этого типа точность может снижаться).

Привязка переменной

При привязке переменной типа MONEY или SMALLMONEY в зашифрованном столбце необходимо задать следующие поля дескриптора:

// n is the descriptor record of the MONEY/SMALLMONEY parameter
// the type is assumed to be SMALLMONEY if isSmallMoney is true and MONEY otherwise

SQLHANDLE ipd = 0;
SQLGetStmtAttr(hStmt, SQL_ATTR_IMP_PARAM_DESC, (SQLPOINTER)&ipd, SQL_IS_POINTER, NULL);
SQLSetDescField(ipd, n, SQL_CA_SS_SERVER_TYPE, isSmallMoney ? (SQLPOINTER)SQL_SS_TYPE_SMALLMONEY :
                                                              (SQLPOINTER)SQL_SS_TYPE_MONEY, SQL_IS_INTEGER);

// If the variable is bound as SQL_NUMERIC, additional descriptor fields have to be set
// var is SQL_NUMERIC_STRUCT containing the value to be inserted

SQLHDESC   hdesc = NULL;
SQLGetStmtAttr(hStmt, SQL_ATTR_APP_PARAM_DESC, &hdesc, 0, NULL);
SQLSetDescField(hdesc, n, SQL_DESC_PRECISION, (SQLPOINTER)(var.precision), 0);
SQLSetDescField(hdesc, n, SQL_DESC_SCALE, (SQLPOINTER)(var.scale), 0);
SQLSetDescField(hdesc, n, SQL_DESC_DATA_PTR, &var, 0);

Как избежать распространенных проблем при запросе зашифрованных столбцов

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

Ошибки преобразования неподдерживаемых типов данных

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

• Тип SQL для параметра всегда точно совпадает с типом целевого столбца или может быть преобразован в тип этого столбца.

• Точность и масштаб параметров, предназначенных для столбцов типов данных SQL Server decimal и numeric, соответствуют точности и масштабу, настроенным для конечного столбца.

• В запросах, изменяющих целевой столбец, точность параметров, ориентированных на столбцы типов данных SQL Server datetime2, datetimeoffset или time, не превышает точность целевого столбца.

Ошибки, возникающие из-за передачи значений в виде открытого текста, а не в зашифрованном виде

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

• Функция Always Encrypted включена (через имя DSN, в строке подключения, перед подключением через атрибут подключения SQL_COPT_SS_COLUMN_ENCRYPTION для конкретного подключения или через атрибут инструкции SQL_SOPT_SS_COLUMN_ENCRYPTION для конкретной инструкции).

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

string queryText = "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE SSN='795-73-9838'";

Меры предосторожности при использовании SQLSetPos и SQLMoreResults

функция SQLSetPos;

API SQLSetPos позволяет приложению обновлять строки в наборе результатов, используя буферы, которые были привязаны с помощью SQLBindCol и в которые ранее были извлечены данные строк. Из-за асимметричного поведения заполнения для зашифрованных типов фиксированной длины существует риск неожиданно изменить данные в этих столбцах при обновлении других столбцов в той же строке. При использовании AE символьные значения фиксированной длины будут дополняться, если значение короче размера буфера.

Чтобы устранить это поведение, установите флаг SQL_COLUMN_IGNORE для пропуска тех столбцов, которые не должны обновляться при операции SQLBulkOperations или использовании SQLSetPos для обновлений на основе курсора. Все столбцы, которые не изменяются приложением напрямую, следует игнорировать по соображениям производительности и для того, чтобы избежать усечения столбцов, привязанных к буферу меньше их фактического размера (в базе данных). Дополнительные сведения см. в справочнике по функции SQLSetPos.

SQLMoreResults и SQLDescribeCol

Приложения могут вызывать SQLDescribeCol, чтобы возвращать метаданные о столбцах в подготовленных инструкциях. Если включена функция Always Encrypted, вызов SQLMoreResultsперед вызовом SQLDescribeCol приводит к вызову sp_describe_first_result_set, в результате чего неверно возвращаются метаданные в формате открытого текста для зашифрованных столбцов. Чтобы избежать этой проблемы, вызовите SQLDescribeCol для подготовленных выражений перед тем, как вызвать SQLMoreResults.

Управление влиянием Always Encrypted на производительность

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

• Дополнительные обращения к базе данных для получения метаданных для параметров запроса.

• Вызовы хранилища главных ключей столбцов для доступа к главному ключу столбца.

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

Управление круговыми путями для получения метаданных параметров запроса

Если для соединения включена функция Always Encrypted, по умолчанию драйвер будет вызывать sys.sp_describe_parameter_encryption для каждого параметризованного запроса, передавая инструкцию запроса (без значений параметров) в SQL Server. Эта хранимая процедура анализирует инструкцию запроса и выясняет, нужно ли шифровать параметры. Если да, то она возвращает по каждому параметру связанные с шифрованием сведения, позволяющие драйверу шифровать их. Описанное выше поведение гарантирует высокий уровень прозрачности клиентского приложения. Приложению (и разработчику) не нужно знать, какие запросы обращаются к зашифрованным столбцам, если все значения для этих зашифрованных столбцов передаются драйверу в параметрах.

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

Поведение Always Encrypted на уровне отдельных инструкций

Чтобы управлять тем, как получение метаданных шифрования для параметризованных запросов влияет на производительность, вы можете изменять поведение Always Encrypted для отдельных запросов, если эта функция уже включена для подключения. Это позволяет гарантировать, что sys.sp_describe_parameter_encryption вызывается только для тех запросов, в которых точно есть параметры для зашифрованных столбцов. Обратите внимание, что в этом случае снижается прозрачность шифрования: при шифровании дополнительных столбцов в базе данных может потребоваться изменить код приложения в соответствии с изменениями схемы.

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

Новые дескрипторы инструкций, которые создаются для подключения с включенной функцией Always Encrypted, по умолчанию получают значение SQL_CE_ENABLED. Дескрипторы, создаваемые для подключений, где эта функция отключена, по умолчанию получают значение SQL_CE_DISABLED (и вы не сможете включить для них Always Encrypted).

Если большинство запросов клиентского приложения обращаются к зашифрованным столбцам, мы рекомендуем выполнить следующее.

• Присвойте ключевому слову ColumnEncryption строки подключения значение Enabled.

• Задайте атрибуту SQL_SOPT_SS_COLUMN_ENCRYPTION значение SQL_CE_DISABLED для инструкций, которые не обращаются к зашифрованным столбцам. Этот параметр отключает возможность вызова sys.sp_describe_parameter_encryption и расшифровки значений в результирующем наборе.

• Задайте атрибуту SQL_SOPT_SS_COLUMN_ENCRYPTION значение SQL_CE_RESULTSETONLY для инструкций, которые не имеют требующих шифрования параметров, но получают данные из зашифрованных столбцов. Этот параметр отключает вызов sys.sp_describe_parameter_encryption и шифрование параметров. Результаты, содержащие зашифрованные столбцы, будут по-прежнему расшифровываться.

• Используйте подготовленные инструкции для запросов, которые будут выполняться многократно; подготовьте запрос с помощью SQLPrepare и сохраните дескриптор инструкции, указывая его в SQLExecute при каждом очередном выполнении. Этот метод является рекомендуемым для поддержания производительности даже при отсутствии зашифрованных столбцов и позволяет драйверу использовать преимущества кэширования метаданных.

Параметры безопасности Always Encrypted

Принудительное шифрование столбцов

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

SQLHDESC ipd;
SQLGetStmtAttr(hStmt, SQL_ATTR_IMP_PARAM_DESC, &ipd, 0, 0);
SQLSetDescField(ipd, paramNum, SQL_CA_SS_FORCE_ENCRYPT, (SQLPOINTER)TRUE, SQL_IS_SMALLINT);   

Если SQL Server сообщает драйверу, что параметр не должен быть зашифрован, использующие этот параметр запросы будут завершаться ошибкой. Это поведение обеспечивает дополнительную защиту от атак на систему безопасности, включающих предоставление клиенту скомпрометированным SQL Server неверных метаданных шифрования, что может привести к раскрытию данных.

Кэширование ключа шифрования столбца

Чтобы уменьшить количество вызовов к хранилищу главных ключей столбцов для расшифровки ключей шифрования столбцов (CEK), драйвер кэширует ключи CEK в памяти в открытом тексте. Кэш CEK является глобальным для драйвера, а не связывается с отдельным подключением. Получив ECEK из метаданных базы данных, драйвер сначала пытается найти в кэше открытый CEK, соответствующий зашифрованному значению ключа. Драйвер обращается к хранилищу ключей, содержащему CMK, только если не может найти в кэше соответствующий CEK в открытом виде.

Начиная с драйвера ODBC Driver for SQL Server версии 17.1, время ожидания кэша для CEK можно изменить с помощью атрибута подключения SQL_COPT_SS_CEKCACHETTL, который указывает количество секунд хранения CEK в кэше. В силу глобальной природы кэша этот атрибут можно изменять из любого дескриптора подключения, который допустим для драйвера. Когда TTL кэша уменьшается, существующие CEK, которые превышали бы новое значение TTL, также удаляются. Если задано значение 0, ключи CEK не кэшируются.

Доверенные пути к ключам

Начиная с ODBC Driver 17.1 for SQL Server, атрибут подключения SQL_COPT_SS_TRUSTEDCMKPATHS позволяет приложению требовать, чтобы операции Always Encrypted использовали только указанный список CMK, определяемых по путям к ключам. По умолчанию этот атрибут имеет значение NULL, и при этом драйвер принимает любой путь ключа. Чтобы использовать эту функцию, задайте SQL_COPT_SS_TRUSTEDCMKPATHS так, чтобы он указывал на нуль-разделённую, нуль-терминированную строку расширенных символов, в которой перечислены разрешённые пути к ключам. Память, на которую указывает этот атрибут, должна оставаться действительной во время операций шифрования или расшифровки с использованием дескриптора соединения, для которого он задан, после чего драйвер проверит, входит ли путь CMK, указанный в метаданных сервера, в этот список без учета регистра. Если путь CMK отсутствует в этом списке, операция завершается ошибкой. Чтобы изменить список доверенных ключей CMK, приложение может изменять содержимое памяти, на которую указывает этот атрибут, не изменяя значение самого атрибута.

Работа с хранилищами главных ключей столбцов

Для шифрования или расшифровки данных драйвер должен получить ключ CEK, который настроен для целевого столбца. Ключи CEK хранятся в зашифрованном виде (ECEK) в метаданных базы данных. Каждому ключу CEK соответствует определенный ключ CMK, который использовался для его шифрования. Метаданные базы данных не хранят сам CMK; они содержат только имя хранилища ключей и информацию, которую хранилище ключей может использовать для поиска CMK.

Чтобы получить значение ECEK в виде открытого текста, драйвер сначала получает метаданные как о CEK, так и о соответствующем ему CMK, а затем использует эти сведения, чтобы обратиться к хранилищу ключей, в котором хранится CMK, и запросить расшифровку ECEK. Драйвер взаимодействует с хранилищем ключей с помощью поставщика хранилища ключей.

Встроенные поставщики хранилища ключей

Драйвер ODBC для SQL Server поставляется со следующими встроенными поставщиками хранилища ключей:

• Вам (или вашему администратору баз данных) необходимо убедиться, что имя поставщика, указанное в метаданных главного ключа столбца, задано правильно, а путь главного ключа столбца соответствует формату пути к ключу для данного поставщика. Рекомендуется настроить ключи с помощью таких средств, как SQL Server Management Studio, которые автоматически создают допустимые имена поставщиков и пути ключей при выдаче CREATE COLUMN MASTER KEY инструкции (Transact-SQL) .

• Убедитесь, что приложение может получить доступ к ключу в хранилище ключей. Для этого процесса может потребоваться предоставить приложению доступ к ключу и (или) ко всему хранилищу ключей (в зависимости от хранилища ключей), либо выполнить другие действия по настройке для определенного хранилища ключей. Например, для доступа к Azure Key Vault необходимо предоставить правильные учетные данные для хранилища ключей.

Использование поставщика Azure Key Vault

Azure Key Vault (AKV) — удобное решение для хранения и управления главными ключами столбцов для Always Encrypted (особенно если ваши приложения размещены в Azure). Драйвер ODBC Driver for SQL Server в Linux, macOS и Windows содержит встроенный поставщик хранилища ключей Azure Key Vault для хранения главного ключа. Дополнительные сведения о настройке Azure Key Vault для Always Encrypted см. в Azure Key Vault — пошаговое руководство, Начало работы с Key Vault и Создание главных ключей столбцов в Azure Key Vault.

Драйвер поддерживает проверку подлинности в Azure Key Vault со следующими типами учетных данных.

• Имя пользователя и пароль — с помощью этого метода учетные данные — это имя пользователя Microsoft Entra и его пароль.

• Идентификатор и секрет клиента — этот метод использует в качестве учетных данных идентификатор клиента приложения и секрет приложения.

• Управляемое удостоверение (17.5.2 и выше) — системное или назначенное пользователем; дополнительные сведения см. в статье Управляемые удостоверения для ресурсов Azure.

• Интерактивный Azure Key Vault (драйверы Windows 17.7+) — при использовании этого метода проверка подлинности учетных данных выполняется через Microsoft Entra ID с использованием Login ID.

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

Начиная с версии 17.8 KeystoreAuthentication и KeystorePrincipalId можно редактировать с помощью пользовательского интерфейса конфигурации DSN в администраторе источника данных ODBC.

Примеры строк подключения

Следующие строки подключения демонстрируют проверку подлинности в Azure Key Vault с двумя разными типами учетных данных.

Идентификатор и секрет клиента

"DRIVER=ODBC Driver 18 for SQL Server;SERVER=myServer;Encrypt=yes;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=<clientId>;KeyStoreSecret=<secret>"

Имя пользователя или пароль

"DRIVER=ODBC Driver 18 for SQL Server;SERVER=myServer;Encrypt=yes;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultPassword;KeyStorePrincipalId=<username>;KeyStoreSecret=<password>"

Управляемое удостоверение личности (назначаемое системой)

"DRIVER=ODBC Driver 18 for SQL Server;SERVER=myServer;Encrypt=yes;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity"

Управляемое удостоверение (назначенное пользователем)

"DRIVER=ODBC Driver 18 for SQL Server;SERVER=myServer;Encrypt=yes;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity;KeyStorePrincipalId=<objectID>"

AKV Интерактив

"DRIVER=ODBC Driver 18 for SQL Server;SERVER=myServer;Encrypt=yes;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultInteractive;UID=<userID>;PWD=<password>"

Чтобы использовать Azure Key Vault в качестве хранилища для CMK не нужно вносить других изменений в приложение ODBC.

Использование поставщика хранилища сертификатов Windows

Драйвер ODBC для SQL Server в Windows включает встроенный поставщик хранилища главных ключей столбцов для хранилища сертификатов Windows с именем MSSQL_CERTIFICATE_STORE. (Этот поставщик недоступен для macOS и Linux.) При использовании этого поставщика ключи CMK хранятся локально на клиентском компьютере, и для его использования с драйвером не нужно вносить никаких дополнительных изменений в конфигурацию приложения. Но такому приложению необходимы права доступа к сертификату и закрытому ключу, размещенным в хранилище. Дополнительные сведения см. в разделе Create and Store Column Master Keys (Always Encrypted)(Создание и хранение главных ключей столбцов (постоянное шифрование)).

Использование пользовательских поставщиков хранилища ключей

Драйвер ODBC для SQL Server также поддерживает настраиваемые сторонние поставщики хранилища ключей с использованием интерфейса CEKeystoreProvider. Это позволяет приложению загружать, запрашивать и настраивать поставщики хранилища ключей, чтобы драйвер мог использовать их для доступа к зашифрованным столбцам. Приложения могут напрямую взаимодействовать с поставщиком хранилища ключей, чтобы шифровать ключи CEK для хранения в SQL Server и выполнять другие задачи ODBC помимо доступа к зашифрованным столбцам. Дополнительные сведения см. в статье Пользовательские поставщики хранилища ключей.

Для взаимодействия с пользовательскими поставщиками хранилища ключей используются два атрибута подключения. В их число входят:

• SQL_COPT_SS_CEKEYSTOREPROVIDER

• SQL_COPT_SS_CEKEYSTOREDATA

Первый из них позволяет загружать и перечислять загруженные поставщики хранилища ключей, а второй нужен для взаимодействия между поставщиком и приложением. Эти атрибуты подключения можно применять в любое время, как до, так и после установки подключения, так как взаимодействие между поставщиком и приложением не требует обмена данными с SQL Server. Но перед подключением, когда драйвер еще не загружен, установка и получение этих атрибутов будет обрабатываться диспетчером драйверов и может приводить к неожиданным результатам.

Загрузка поставщика хранилища ключей

Настройка атрибута подключения SQL_COPT_SS_CEKEYSTOREPROVIDER позволяет клиентскому приложению загрузить библиотеку поставщика, что делает доступными для использования все содержащиеся в этой библиотеке поставщики хранилища ключей.

SQLRETURN SQLSetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

Драйвер пытается загрузить библиотеку, указанную в параметре ValuePtr, через определяемый платформой механизм загрузки динамических библиотек (dlopen() в Linux и macOS, LoadLibrary() в Windows) и добавляет все определенные в ней поставщики в список известных драйверу поставщиков. Могут возникать следующие ошибки.

SQLSetConnectAttr возвращает обычные значения ошибки или успешного завершения, а дополнительные сведения по любым возникающим ошибкам можно получить через стандартный механизм диагностики ODBC.

Получение списка загруженных поставщиков

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

SQLRETURN SQLGetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);

Каждая операция Get возвращает имя текущего поставщика и увеличивает значение внутреннего счетчика до следующего поставщика, чтобы обеспечить возможность получить весь список. Когда этот счетчик достигнет конца списка, возвращается пустая строка ("") и счетчик сбрасывается. Следующая операция Get снова начинает процесс с начала списка.

Взаимодействие с поставщиками хранилища ключей

Атрибут подключения SQL_COPT_SS_CEKEYSTOREDATA позволяет клиентскому приложению взаимодействовать с загруженными поставщиками хранилища ключей для настройки дополнительных параметров, ключевого материала и т. д. Взаимодействие между клиентским приложением и поставщиком выполняется по простому протоколу формата "запрос и ответ" через запросы Get и Set с помощью этого атрибута соединения. Взаимодействие всегда инициируется клиентским приложением.

Приложение взаимодействует с поставщиками хранилища ключей через драйвер с использованием структуры CEKeystoreData:

typedef struct CEKeystoreData {
wchar_t *name;
unsigned int dataSize;
char data[];
} CEKEYSTOREDATA;

Запись данных поставщику

Вызов SQLSetConnectAttr с использованием атрибута SQL_COPT_SS_CEKEYSTOREDATA записывает «пакет» данных указанному поставщику хранилища ключей.

SQLRETURN SQLSetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

Подробные сведения об ошибке можно получить с помощью SQLGetDiacRec.

Чтение данных от поставщика данных

Вызов SQLGetConnectAttr с использованием атрибута SQL_COPT_SS_CEKEYSTOREDATA считывает «пакет» данных из провайдера, в который последним была выполнена запись. Если такого не было, возникает ошибка последовательности вызова функций. Разработчикам поставщиков хранилищ ключей рекомендуется поддерживать «фиктивные записи» размером 0 байт как способ выбирать поставщика для операций чтения, не вызывая других побочных эффектов, если это имеет смысл.

SQLRETURN SQLGetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);

Вызывающая сторона должна обеспечить выделение буфера достаточного размера, следующего за структурой CEKEYSTOREDATA, в который поставщик сможет записывать данные. При возврате поле dataSize обновляется фактической длиной данных, считанных от поставщика данных. Подробные сведения об ошибке можно получить с помощью SQLGetDiacRec.

Этот интерфейс не предъявляет дополнительных требований к формату передачи данных между приложением и поставщиком хранилища ключей. Каждый поставщик может определить свой формат данных и протоколов в зависимости от конкретных потребностей.

Пример реализации собственного поставщика хранилища ключей см. в статье Custom Keystore Providers

Ограничения драйвера ODBC при использовании Always Encrypted

Асинхронные операции

Драйвер ODBC разрешает использование асинхронных операций совместно с Always Encrypted, но такой режим использования негативно влияет на производительность операций. Вызов sys.sp_describe_parameter_encryption для определения метаданных шифрования для инструкции является блокирующим, то есть драйвер будет ждать получения метаданных от сервера, прежде чем возвращать SQL_STILL_EXECUTING.

Получение данных частями с помощью SQLGetData

До ODBC Driver 17 for SQL Server зашифрованные символьные и двоичные столбцы невозможно получать по частям с помощью SQLGetData. Допускался только один вызов SQLGetData, для которого требовался буфер достаточной длины для размещения полных данных из столбца.

Отправка данных по частям с помощью SQLPutData

До версии ODBC Driver 17.3 for SQL Server нельзя было отправлять данные для вставки или сравнения по частям с помощью SQLPutData. Допускался только один вызов SQLPutData с буфером, который содержит полные данные. Для вставки в зашифрованные столбцы длинных данных используйте интерфейс API массового копирования, который описан в следующем разделе, передавая ему файл входных данных.

Зашифрованные данные money и smallmoney

Зашифрованные столбцы типа money или smallmoney нельзя использовать в качестве цели для параметров, поскольку не существует определённого типа данных ODBC, сопоставляемого с этими типами, в результате чего возникают ошибки Operand Type Clash.

Массовое копирование зашифрованных столбцов

Начиная с ODBC Driver 17 for SQL Server, поддерживается использование функций SQL Bulk Copy и служебной программы bcp с Always Encrypted. Как открытый текст (зашифрованный при вставке и расшифрованный при извлечении), так и зашифрованный (переданный буквально) можно вставлять и извлекать с помощью API массового копирования (bcp_*) и утилиты bcp.

• Чтобы получить зашифрованный текст в формате varbinary(max) (например, для массовой загрузки в другую базу данных), создайте подключение без параметра ColumnEncryption (или присвойте ему значение Disabled) и выполните операцию BCP OUT.

• Чтобы вставлять и извлекать открытый текст, а драйвер при необходимости будет прозрачно выполнять шифрование и расшифровку, достаточно установить для ColumnEncryption значение Enabled. Все остальные функции API BCP сохраняются неизменными.

• Чтобы вставить зашифрованные данные в форме varbinary(max) (например, как полученные выше), установите для параметра BCPMODIFYENCRYPTED значение TRUE и выполните операцию BCP IN. Чтобы результирующие данные можно было расшифровать, ключ CEK для целевого столбца должен совпадать с тем ключом CEK, который изначально применялся для получения зашифрованного текста.

Если вы используете служебную программу bcp, для указания параметра ColumnEncryption следует указать аргумент -D и имя источника данных, содержащего нужное значение. Чтобы вставить зашифрованные данные, включите для пользователя параметр ALLOW_ENCRYPTED_VALUE_MODIFICATIONS.

Следующая таблица содержит краткое описание действий, доступных при работе с зашифрованным столбцом.

Параметр BCPMODIFYENCRYPTED

Чтобы предотвратить повреждение данных, сервер обычно не допускает прямую вставку зашифрованных данных в зашифрованный столбец, то есть такая попытка завершится ошибкой. Но для массовой загрузки зашифрованных данных с помощью API BCP вы можете установить для параметра BCPMODIFYENCRYPTEDbcp_control значение TRUE, что разрешает прямую вставку зашифрованных данных и снижает риск повреждения зашифрованных данных по сравнению с применением параметра ALLOW_ENCRYPTED_VALUE_MODIFICATIONS для учетной записи пользователя. Тем не менее ключи должны соответствовать данным, и после массовой вставки целесообразно выполнить несколько проверок вставленных данных в режиме только чтения до их дальнейшего использования.

Дополнительные сведения см. в разделе Перенос конфиденциальных данных с помощью функции Always Encrypted.

Сводные данные по API Always Encrypted

Ключевые слова в строке подключения

Атрибуты подключения

Атрибуты инструкции

Поля дескриптора

Параметры bcp_control

Устранение неполадок

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

• Наличие и доступность на сервере ключа CEK, который шифрует нужный столбец.

• CMK, который шифрует CEK, имеет метаданные, доступные на сервере, и также доступен на стороне клиента.

• ColumnEncryption включается в DSN, строке подключения или атрибуте подключения, а при использовании безопасного анклава имеет правильный формат.

Кроме того, при использовании безопасного анклава ошибки аттестации указывают на шаг в процессе аттестации, на котором произошел сбой (см. следующую таблицу).

См. также

• Always Encrypted (ядро СУБД)
• Always Encrypted с безопасными анклавами