搭配使用 Always Encrypted 與 ODBC Driver for SQL ServerUsing Always Encrypted with the ODBC Driver for SQL Server

下載下載 ODBC 驅動程式DownloadDownload ODBC Driver

適用於Applicable to

  • ODBC Driver 13.1 for SQL ServerODBC Driver 13.1 for SQL Server
  • ODBC Driver 17 for SQL ServerODBC Driver 17 for SQL Server

簡介Introduction

本文提供有關如何使用Always encrypted (Database Engine來開發 ODBC 應用程式的資訊) 或使用 secure 記憶體保護區和ODBC Driver for SQL Serveralways encrypted來開發 odbc 應用程式。This article provides information on how to develop ODBC applications using Always Encrypted (Database Engine) or Always Encrypted with secure enclaves and the ODBC Driver for SQL Server.

[永遠加密] 可讓用戶端應用程式加密敏感性資料,且永遠不會顯示資料或 SQL Server 或 Azure SQL Database 的加密金鑰。Always Encrypted allows client applications to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server or Azure SQL Database. 啟用 Always Encrypted 的驅動程式(例如 ODBC Driver for SQL Server)可透過明確地加密和解密用戶端應用程式中的敏感性資料,達到此安全性。An Always Encrypted enabled driver, such as the ODBC Driver for SQL Server, achieves this security by transparently encrypting and decrypting sensitive data in the client application. 驅動程式會自動判斷哪一個查詢參數對應至敏感性資料庫資料行 (使用 [永遠加密] 保護),然後加密這些參數值後再將資料傳遞至 SQL Server 或 Azure SQL Database。The driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the data to SQL Server or Azure SQL Database. 同樣地,驅動程式會以清晰簡明的方式,將擷取自查詢結果的加密資料庫資料行資料進行解密。Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results. 「具有安全記憶體保護區的 Always Encrypted」 會延伸此功能以啟用更豐富的敏感性資料功能,同時保持資料的機密性。Always Encrypted with secure enclaves extends this feature to enable richer functionality on sensitive data while keeping the data confidential.

如需詳細資訊,請參閱 Always encrypted (Database Engine) always encrypted (使用安全記憶體保護區)。For more information, see Always Encrypted (Database Engine) and Always Encrypted with secure enclaves.

PrerequisitesPrerequisites

在資料庫中設定永遠加密。Configure Always Encrypted in your database. 此程式牽涉到布建 Always Encrypted 金鑰,以及設定所選資料庫資料行的加密。This process involves provisioning Always Encrypted keys and setting up encryption for selected database columns. 如果您的資料庫尚未設定 [永遠加密],請遵循 Getting Started with Always Encrypted(永遠加密快速入門) 中的指示操作。If you do not already have a database with Always Encrypted configured, follow the directions in Getting Started with Always Encrypted. 特別是,您的資料庫應該包含下列項目的中繼資料定義:「資料行主要金鑰」(CMK)、「資料行加密金鑰」(CEK),以及包含一或多個使用該 CEK 來加密之資料行的資料表。In particular, your database should contain the metadata definitions for a Column Master Key (CMK), a Column Encryption Key (CEK), and a table containing one or more columns encrypted using that CEK.

如果您搭配安全記憶體保護區使用 Always Encrypted,請參閱使用 Always encrypted 搭配安全記憶體保護區開發應用程式 ,以取得更多必要條件。If you are using Always Encrypted with secure enclaves, see Develop applications using Always Encrypted with secure enclaves for more prerequisites.

在 ODBC 應用程式中啟用 Always EncryptedEnabling Always Encrypted in an ODBC Application

若要同時啟用參數加密和結果集加密資料行解密,最簡單的方式是將 ColumnEncryption 連接字串關鍵字的值設定為 EnabledThe easiest way to enable both parameter encryption and resultset encrypted column decryption is by setting the value of the ColumnEncryption connection string keyword to Enabled. 可啟用永遠加密的連接字串範例如下:The following is an example of a connection string that enables Always Encrypted:

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

您也可以使用相同的金鑰和值 (如果有連接字串設定,則會由此設定覆寫),或透過程式設計方式使用 SQL_COPT_SS_COLUMN_ENCRYPTION 連線前屬性,在 DSN 設定中啟用 Always Encrypted。Always Encrypted may also be enabled in the DSN configuration, using the same key and value (which will be overridden by the connection string setting, if present), or programmatically with the SQL_COPT_SS_COLUMN_ENCRYPTION pre-connection attribute. 以這種方式設定它會覆寫連接字串或 DSN 中設定的值:Setting it this way overrides the value set in the connection string or DSN:

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

針對連線啟用 Always Encrypted 之後,便可以針對個別查詢調整其行為。Once enabled for the connection, the behavior of Always Encrypted may be adjusted for individual queries. 如需詳細資訊,請參閱下列 控制 Always Encrypted 的效能影響For more information, see Controlling the Performance Impact of Always Encrypted below.

啟用 Always Encrypted 並不足以確保加密或解密成功;您還必須確定:Enabling Always Encrypted is not sufficient for encryption or decryption to succeed; you also need to make sure that:

  • 應用程式要有 [檢視任何資料行的主要金鑰定義] 和 [檢視任何資料行的加密金鑰定義] 資料庫權限,才能存取資料庫中永遠加密金鑰的相關中繼資料。The application has the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions, required to access the metadata about Always Encrypted keys in the database. 如需詳細資料,請參閱資料庫權限For details, see Database Permissions.

  • 應用程式可以存取保護所查詢加密資料行之 CEK 的 CMK。The application can access the CMK that protects the CEKs for the queried encrypted columns. 此行為取決於儲存 CMK 的金鑰存放區提供者。This behavior is dependent on the keystore provider that stores the CMK. 如需詳細資訊,請參閱使用資料行主要金鑰存放區For more information, see Working with Column Master Key Stores.

啟用具有安全記憶體保護區的 Always EncryptedEnabling Always Encrypted with secure enclaves

注意

在 Linux 和 macOS 上,必須有 OpenSSL 版本1.0.1 或更新版本,才能搭配使用 Always Encrypted 與安全記憶體保護區。On Linux and macOS, OpenSSL version 1.0.1 or later is required to use Always Encrypted with secure enclaves.

從17.4 版開始,驅動程式支援一律以安全記憶體保護區加密。Beginning with version 17.4, the driver supports Always Encrypted with secure enclaves. 若要在連接至資料庫時啟用記憶體保護區,請將 ColumnEncryption DSN 索引鍵、連接字串關鍵字或連接屬性設定為下列值: <attestation protocol>\<attestation URL> ,其中:To enable the use of the enclave when connecting to a database, set the ColumnEncryption DSN key, connection string keyword, or connection attribute to the following value: <attestation protocol>\<attestation URL>, where:

  • <attestation protocol> -指定用於記憶體保護區證明的通訊協定。<attestation protocol> - specifies a protocol used for enclave attestation.

    • 如果您是使用 SQL ServerSQL Server 和主機守護者服務 (HGS) ,則 <attestation protocol> 應該是 VBS-HGSIf you're using SQL ServerSQL Server and Host Guardian Service (HGS), <attestation protocol> should be VBS-HGS.
    • 如果您使用 Azure SQL DatabaseAzure SQL Database 的是和 Microsoft Azure 證明,則 <attestation protocol> 應該是 SGX-AASIf you're using Azure SQL DatabaseAzure SQL Database and Microsoft Azure Attestation, <attestation protocol> should be SGX-AAS.
  • <attestation URL> - (證明服務端點) 指定證明 URL。<attestation URL> - specifies an attestation URL (an attestation service endpoint). 您必須從證明服務系統管理員取得環境的證明 URL。You need to obtain an attestation URL for your environment from your attestation service administrator.

針對資料庫連接啟用記憶體保護區計算的連接字串範例:Examples of connection strings enabling enclave computations for a database connection:

  • SQL ServerSQL Server::

    Driver=ODBC Driver 17 for SQL Server;Server=myServer.myDomain;Database=myDataBase;Trusted_Connection=Yes;ColumnEncryption=VBS-HGS,http://myHGSServer.myDomain/Attestation
    
  • Azure SQL DatabaseAzure SQL Database::

    Driver=ODBC Driver 17 for SQL Server;Server=myServer.database.windows.net;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Encrypt=yes;ColumnEncryption=SGX-AAS,https://myAttestationProvider.uks.attest.azure.net/attest/SgxEnclave
    

如果伺服器和證明服務已正確設定,以及所需資料行的啟用記憶體保護區的 Cmk 和 Cek,您現在應該能夠執行使用記憶體保護區的查詢(例如就地加密和豐富計算),以及永遠加密所提供的現有功能。If the server and attestation service are configured correctly along with enclave-enabled CMKs and CEKs for the desired columns, you should now be able to execute queries that use the enclave such as in-place encryption and rich computations, in addition to the existing functionality provided by Always Encrypted. 如需詳細資訊,請參閱 使用 secure 記憶體保護區設定 Always EncryptedFor more information, see Configure Always Encrypted with secure enclaves.

擷取和修改加密資料行中的資料Retrieving and Modifying Data in Encrypted Columns

當您在連線上啟用 Always Encrypted 之後,就可以使用標準 ODBC API。Once you enable Always Encrypted on a connection, you can use standard ODBC APIs. ODBC API 可以擷取或修改加密資料庫資料行中的資料。The ODBC APIs can retrieve or modify data in encrypted database columns. 下列檔專案可能會有説明:The following documentation items might be helpful:

您的應用程式必須具有必要的資料庫權限,而且必須能夠存取資料行主要金鑰。Your application must have the required database permissions, and must be able to access the column master key. 然後,驅動程式會將以加密資料行為目標的任何查詢參數加密。Then, the driver encrypts any query parameters that target encrypted columns. 驅動程式也會將從加密資料行擷取的資料解密。The driver also decrypts data retrieved from encrypted columns. 驅動程式會執行所有此類加密及解密作業,不需要原始程式碼的協助。The driver performs all this encrypting and decrypting without any assistance from your source code. 對您的程式來說,就像資料行未加密一樣。To your program, it is as if the columns are not encrypted.

如未啟用 [永遠加密],使用目標加密資料行參數的查詢就會失敗。If Always Encrypted is not enabled, queries with parameters that target encrypted columns will fail. 只要查詢沒有以加密資料行為目標的參數,就仍然可以從加密資料行擷取資料。Data can still be retrieved from encrypted columns, as long as the query has no parameters targeting encrypted columns. 不過,驅動程式不會嘗試進行任何解密,而應用程式則會收到二進位加密資料 (以位元組陣列的形式)。However, the driver will not attempt any decryption and the application will receive the binary encrypted data (as byte arrays).

下表摘要說明查詢的行為,視 Always Encrypted 是否啟用而定:The table below summarizes the behavior of queries, depending on whether Always Encrypted is enabled or not:

查詢特性Query characteristic [永遠加密] 已啟用,且應用程式可以存取金鑰和金鑰中繼資料Always Encrypted is enabled and application can access the keys and key metadata [永遠加密] 已啟用,但應用程式不能存取金鑰或金鑰中繼資料Always Encrypted is enabled and application cannot access the keys or key metadata [永遠加密] 已停用Always Encrypted is disabled
以加密資料行為目標的參數。Parameters targeting encrypted columns. 以清晰簡明的方式加密參數值。Parameter values are transparently encrypted. 錯誤Error 錯誤Error
從加密的資料行擷取資料,沒有任何以加密資料行為目標的參數。Retrieving data from encrypted columns, without parameters targeting encrypted columns. 以清晰簡明的方式解密來自加密資料行的結果。Results from encrypted columns are transparently decrypted. 應用程式會收到純文字資料行值。The application receives plaintext column values. 錯誤Error 不解密來自加密資料行的結果。Results from encrypted columns are not decrypted. 應用程式收到位元組陣列形態的加密值。The application receives encrypted values as byte arrays.

以下範例將說明擷取和修改加密資料行中的資料。The following examples illustrate retrieving and modifying data in encrypted columns. 這些範例假設的是一個具有下列結構描述的資料表。The examples assume a table with the following schema. SSN 和 BirthDate 資料行均已加密。The SSN and BirthDate columns are encrypted.

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

資料插入範例Data Insertion Example

本例會將資料列插入病患資料表。This example inserts a row into the Patients table. 注意下列詳細資料:Note the following details:

  • 範例程式碼中沒有任何需要加密的特定項目。There is nothing specific to encryption in the sample code. 驅動程式會自動偵測並加密以加密資料行為目標之 SSN 與日期參數的值。The driver automatically detects and encrypts the values of the SSN and date parameters, which target encrypted columns. 此行為可讓加密對應用程式變得透明化。This behavior makes encryption transparent to the application.

  • 插入至資料庫資料行的值,包括加密的資料行,會傳遞為繫結參數 (請參閱 SQLBindParameter 函式)。The values inserted into database columns, including the encrypted columns, are passed as bound parameters (see SQLBindParameter Function). 雖然將值傳送到未加密的資料行時,使用參數是選擇性項目 (還是強烈建議使用,因有利於防止 SQL 插入式攻擊),但它對以加密資料行為目標的值卻是必要項目。While using parameters is optional when sending values to non-encrypted columns (although it is highly recommended because it helps prevent SQL injection), it is required for values targeting encrypted columns. 如果將插入 SSN 或 BirthDate 資料行中的值當作內嵌在查詢陳述式中的常值傳遞,則查詢會失敗,因為驅動程式不會嘗試加密或處理查詢中的常數。If the values inserted in the SSN or BirthDate columns were passed as literals embedded in the query statement, the query would fail because the driver does not attempt to encrypt or otherwise process literals in queries. 結果,伺服器會因與加密資料行不相容而拒絕它們。As a result, the server would reject them as incompatible with the encrypted columns.

  • 插入 SSN 資料行中之參數的 SQL 類型會設定為 SQL_CHAR,這會對應至 char SQL Server 資料類型 (rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, (SQLPOINTER)SSN, 0, &cbSSN);)。The SQL type of the parameter inserted into the SSN column is set to SQL_CHAR, which maps to the char SQL Server data type (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 值的轉換。If the type of the parameter was set to SQL_WCHAR, which maps to nchar, the query would fail, as Always Encrypted does not support server-side conversions from encrypted nchar values to encrypted char values. 請參閱 ODBC 程式設計師參考 -- 附錄 D:資料類型 (部分機器翻譯),以取得資料類型對應的相關資訊。See ODBC Programmer's Reference -- Appendix D: Data Types for information about the data type mappings.

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

純文字資料擷取範例Plaintext Data Retrieval Example

下例示範根據加密值篩選資料,以及從加密資料行擷取純文字資料。The following example demonstrates filtering data based on encrypted values, and retrieving plaintext data from encrypted columns. 注意下列詳細資料:Note the following details:

  • 在 WHERE 子句中用來篩選 SSN 資料行的值,需要使用 SQLBindParameter 傳遞,如此驅動程式可以清晰簡明方式來加密它,再將它傳送至伺服器。The value used in the WHERE clause to filter on the SSN column needs to be passed using SQLBindParameter, so that the driver can transparently encrypt it before sending it to the server.

  • 程式列印的所有值都是純文字格式,因為驅動程式會以清晰簡明方式來解密從 SSN 和 BirthDate 資料行擷取的資料。All values printed by the program will be in plaintext, since the driver will transparently decrypt the data retrieved from the SSN and BirthDate columns.

注意

只有當加密具確定性,或已啟用安全記憶體保護區時,查詢才能在加密資料行上執行相等比較。Queries can perform equality comparisons on encrypted columns only if the encryption is deterministic, or if the secure enclave is enabled. 如需詳細資訊,請參閱選取確定性或隨機化加密For more information, see Selecting Deterministic or Randomized encryption.

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

加密文字資料擷取範例Ciphertext Data Retrieval Example

如未啟用 [永遠加密],只要查詢沒有以加密資料行為目標的參數,查詢就仍然可以從加密資料行擷取資料。If Always Encrypted is not enabled, a query can still retrieve data from encrypted columns, as long as the query has no parameters targeting encrypted columns.

下列範例會示範從加密資料行擷取二進位的加密資料。The following example illustrates retrieving binary encrypted data from encrypted columns. 注意下列詳細資料:Note the following details:

  • 因為連接字串未啟用 [永遠加密],所以查詢會以位元組陣列 (程式會將值轉換為字串) 傳回加密的 SSN 和 BirthDate 值。As Always Encrypted is not enabled in the connection string, the query will return encrypted values of SSN and BirthDate as byte arrays (the program converts the values to strings).
  • 從加密資料行擷取資料但停用 [永遠加密] 的查詢可以有參數,只要沒有任何參數以加密資料行為目標。A query retrieving data from encrypted columns with Always Encrypted disabled can have parameters, as long as none of the parameters target an encrypted column. 上述依 LastName 篩選的查詢,在資料庫中未加密。The above query filters by LastName, which is not encrypted in the database. 如果依 SSN 或 BirthDate 篩選查詢,查詢會失敗。If the query filtered by SSN or BirthDate, the query would fail.
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 加密Money/SmallMoney encryption

從驅動程式版本17.7 開始,您可以搭配使用 Always Encrypted 與 MONEY 和 SMALLMONEY。Starting with driver version 17.7 it is possible to use Always Encrypted with MONEY and SMALLMONEY. 不過,還有一些需要採取的額外步驟。However there are some extra steps to take. 當插入加密的 MONEY 或 SMALLMONEY 資料行時,請使用下列其中一種 C 類型:When inserting into encrypted MONEY or SMALLMONEY columns, use one of the following C types:

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_NUMERIC SQL_DOUBLE 使用這個類型) 時,可能會遺失或 (精確度的 SQL 類型。and a SQL type of either SQL_NUMERIC or SQL_DOUBLE (precision may be lost when using this type).

系結變數Binding the variable

在加密資料行中系結 MONEY/SMALLMONEY 變數時,必須設定下列描述項欄位) (s:Whenever binding a MONEY/SMALLMONEY variable in an encrypted column the following descriptor field(s) must be set:

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

避免常見的加密資料行查詢問題Avoiding Common Problems when Querying Encrypted Columns

本節描述從 ODBC 應用程式查詢加密資料行時常見的錯誤類別,以及如何避免的一些指導方針。This section describes common categories of errors when querying encrypted columns from ODBC applications and a few guidelines on how to avoid them.

不支援的資料類型轉換錯誤Unsupported data type conversion errors

[永遠加密] 支援極少數的加密資料類型轉換。Always Encrypted supports few conversions for encrypted data types. 如需支援的類型轉換詳細清單,請參閱 Always Encrypted (資料庫引擎)See Always Encrypted (Database Engine) for the detailed list of supported type conversions. 為了避免資料類型轉換錯誤,請確定您在使用 SQLBindParameter 搭配以加密資料行為目標的參數時,注意到下列幾點:To avoid data type conversion errors, make sure that you observe the following points when using SQLBindParameter with parameters targeting encrypted columns:

  • 參數的 SQL 類型與目標資料行的類型完全相同,或是支援從 SQL 類型轉換成資料行的類型。The SQL type of the parameter is either exactly the same as the type of the targeted column, or the conversion from the SQL type to the type of the column is supported.

  • decimalnumeric SQL Server 資料類型資料行為目標之參數的有效位數和小數位數,和為目標資料行設定的有效位數和小數位數相同。The precision and scale of parameters targeting columns of the decimal and numeric SQL Server data types is the same as the precision and scale configured for the target column.

  • 在修改目標資料行的查詢中,以 datetime2datetimeoffsettime SQL Server 資料類型資料行為目標之參數的有效位數,不大於目標資料行的有效位數。The precision of parameters targeting columns of datetime2, datetimeoffset, or time SQL Server data types is not greater than the precision for the target column, in queries that modify the target column.

因為傳送純文字,而不是傳送加密值所造成的錯誤。Errors due to passing plaintext instead of encrypted values

任何以加密資料行為目標的值都必須在傳送至伺服器之前先加密。Any value that targets an encrypted column needs to be encrypted before being sent to the server. 嘗試對加密資料行插入、修改或以純文字值進行篩選時,會導致發生錯誤。An attempt to insert, modify, or filter by a plaintext value on an encrypted column will result in an error. 若要避免這類錯誤,請確定:To prevent such errors, make sure that:

  • Always Encrypted 已啟用 (藉由在連線前,於 DSN、連接字串中設定特定連線的 SQL_COPT_SS_COLUMN_ENCRYPTION 連線屬性,或特定陳述式的 SQL_SOPT_SS_COLUMN_ENCRYPTION 陳述式屬性)。Always Encrypted is enabled (in the DSN, the connection string, before connecting by setting the SQL_COPT_SS_COLUMN_ENCRYPTION connection attribute for a specific connection, or the SQL_SOPT_SS_COLUMN_ENCRYPTION statement attribute for a specific statement).

  • 您可以使用 SQLBindParameter 傳送以加密資料行為目標的資料。You use SQLBindParameter to send data targeting encrypted columns. 下列範例顯示對加密資料行 (SSN) 以常值/常數錯誤篩選,而不是以引數將常值傳遞至 SQLBindParameter 的查詢。The example below shows a query that incorrectly filters by a literal/constant on an encrypted column (SSN), instead of passing the literal as an argument to SQLBindParameter.

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

使用 SQLSetPos 和 SQLMoreResults 時的預防措施Precautions when using SQLSetPos and SQLMoreResults

SQLSetPosSQLSetPos

SQLSetPos API 可讓應用程式使用與 SQLBindCol 繫結的緩衝區來更新資料集內的資料列,並且更新到先前擷取資料列資料的位置中。The SQLSetPos API allows an application to update rows in a resultset using buffers that were bound with SQLBindCol and into which row data was previously fetched. 由於加密的固定長度類型有非對稱的填補行為,因此有可能在於資料列中的其他資料行上執行更新時,意外地改變資料。Due to the asymmetric padding behavior of encrypted fixed-length types, it is possible to unexpectedly alter the data of these columns while performing updates on other columns in the row. 使用 AE 時,如果值小於緩衝區大小,則會填補固定長度的字元值。With AE, fixed-length character values will be padded if the value is smaller than the buffer size.

若要減輕這種行為,請使用 SQL_COLUMN_IGNORE 旗標來忽略不會在中更新的資料行, SQLBulkOperations 以及 SQLSetPos 用於以資料指標為基礎之更新時的資料行。To mitigate this behavior, use the SQL_COLUMN_IGNORE flag to ignore columns that will not be updated as part of SQLBulkOperations and when using SQLSetPos for cursor-based updates. 為了效能考量,以及避免將繫結至比其實際 (DB) 大小「還要小」 之緩衝區的資料行截斷,應該忽略所有不會由應用程式直接修改的資料行。All columns that are not being directly modified by the application should be ignored, both for performance and to avoid truncation of columns that are bound to a buffer smaller than their actual (DB) size. 如需詳細資訊,請參閱 SQLSetPos 函式參考 (部分機器翻譯)。For more information, see SQLSetPos Function reference.

SQLMoreResults 和 SQLDescribeColSQLMoreResults & SQLDescribeCol

應用程式可以呼叫 SQLDescribeCol 來傳回準備陳述式中資料行的相關中繼資料。Application programs may call SQLDescribeCol to return metadata about columns in prepared statements. 已啟用 Always Encrypted 時,在呼叫 SQLDescribeCol「之前」 先呼叫 SQLMoreResults 會導致呼叫 sp_describe_first_result_set,這不會正確地傳回加密資料行的純文字中繼資料。When Always Encrypted is enabled, calling SQLMoreResults before calling SQLDescribeCol causes sp_describe_first_result_set to be called, which does not correctly return the plaintext metadata for encrypted columns. 若要避免此問題,請在呼叫 SQLMoreResults「之前」 先呼叫 SQLDescribeColTo avoid this issue, call SQLDescribeCol on prepared statements before calling SQLMoreResults.

控制 Always Encrypted 的效能影響Controlling the Performance Impact of Always Encrypted

因為 Always Encrypted 是用戶端加密技術,大部分的效能額外負荷是在用戶端觀察到,不是在資料庫觀察到。Because Always Encrypted is a client-side encryption technology, most of the performance overhead is observed on the client side, not in the database. 除了加密和解密作業成本之外,用戶端其他的效能額外負荷來源如下:Apart from the cost of encryption and decryption operations, the other sources of performance overhead on the client side are:

  • 額外的資料庫來回行程,以抓取查詢參數的中繼資料。Extra round trips to the database to retrieve metadata for query parameters.

  • 呼叫資料行主要金鑰存放區以存取資料行主要金鑰。Calls to a column master key store to access a column master key.

本節描述 JDBC Driver for SQL Server 的內建效能最佳化,以及如何控制上述兩個因素對效能的影響。This section describes the built-in performance optimizations in the ODBC Driver for SQL Server and how you can control the impact of the above two factors on performance.

控制反覆存取以擷取查詢參數的中繼資料Controlling Round-trips to Retrieve Metadata for Query Parameters

如果連線已啟用 Always Encrypted,此驅動程式預設會針對每個參數化查詢呼叫 sys.sp_describe_parameter_encryption,將查詢陳述式 (不含任何參數值) 傳遞至 SQL Server。If Always Encrypted is enabled for a connection, the driver will, by default, call sys.sp_describe_parameter_encryption for each parameterized query, passing the query statement (without any parameter values) to SQL Server. 這個預存程序會分析查詢陳述式,以查明是否有任何參數需要加密,如果有,便傳回每個參數的加密相關資訊,以便讓驅動程式加密參數。This stored procedure analyzes the query statement to find out if any parameters need to be encrypted, and if so, returns the encryption-related information for each parameter to allow the driver to encrypt them. 上述行為可確保用戶端應用程式的高度透明:應用程式 (和應用程式開發人員) 不需要知道哪些查詢會存取加密資料行,只要以加密資料行為目標的值會傳遞給參數中的驅動程式即可。The above behavior ensures a high level of transparency to the client application: The application (and the application developer) does not need to be aware of which queries access encrypted columns, as long as the values targeting encrypted columns are passed to the driver in parameters.

從17.6 版開始,驅動程式也會快取備妥之語句的加密中繼資料,藉由允許的後續呼叫 SQLExecute 不需要額外的來回行程來取得加密中繼資料來改善效能。Beginning in version 17.6, the driver also caches the encryption metadata for prepared statements, improving performance by allowing subsequent calls to SQLExecute to not require an extra round trip to retrieve the encryption metadata.

個別陳述式的 Always Encrypted 行為Per-Statement Always Encrypted Behavior

若要控制擷取參數化查詢之加密中繼資料的效能影響,您可以改變個別查詢的 Always Encrypted 行為 (如果已在連線上啟用此行為)。To control the performance impact of retrieving encryption metadata for parameterized queries, you can alter the Always Encrypted behavior for individual queries if it has been enabled on the connection. 如此一來,您便可以確保只有針對已知具有以加密資料行為目標之參數的查詢,才會叫用 sys.sp_describe_parameter_encryptionThis way, you can ensure that sys.sp_describe_parameter_encryption is invoked only for queries that you know have parameters targeting encrypted columns. 不過請注意,如此一來,您就可以降低加密的透明度:如果您將資料庫中的多個資料行加密,您可能需要變更應用程式的程式碼,以配合架構變更。Note, however, that by doing so, you reduce transparency of encryption: if you encrypt more columns in your database, you may need to change the code of your application to align it with the schema changes.

若要控制陳述式的 Always Encrypted 行為,請呼叫 SQLSetStmtAttr 以將 SQL_SOPT_SS_COLUMN_ENCRYPTION 陳述式屬性設定為下列其中一個值:To control the Always Encrypted behavior of a statement, call SQLSetStmtAttr to set the SQL_SOPT_SS_COLUMN_ENCRYPTION statement attribute to one of the following values:

Value 描述Description
SQL_CE_DISABLED (0)SQL_CE_DISABLED (0) 針對陳述式停用 Always EncryptedAlways Encrypted is disabled for the statement
SQL_CE_RESULTSETONLY (1)SQL_CE_RESULTSETONLY (1) 僅解密。Decryption Only. 結果集和傳回值已解密,而參數未加密Result sets and return values are decrypted, and parameters are not encrypted
SQL_CE_ENABLED (3)SQL_CE_ENABLED (3) 同時針對參數和結果啟用並使用 Always EncryptedAlways Encrypted is enabled and used for both parameters and results

新陳述式控制代碼若是從已啟用 Always Encrypted 的連線所建立,就會預設為 SQL_CE_ENABLED。New statement handles created from a connection with Always Encrypted enabled default to SQL_CE_ENABLED. 從已停用的連接建立的控制碼預設為 SQL_CE_DISABLED (,因此無法在其上啟用 Always Encrypted。 ) Handles created from a connection with it disabled default to SQL_CE_DISABLED (and it is not possible to enable Always Encrypted on them.)

如果用戶端應用程式的大部分查詢都會存取加密資料行,則建議使用下列幾點:If most of the queries of a client application access encrypted columns, the following points are recommended:

  • ColumnEncryption 連接字串關鍵字設定為 EnabledSet the ColumnEncryption connection string keyword to Enabled.

  • SQL_SOPT_SS_COLUMN_ENCRYPTION SQL_CE_DISABLED 不會存取任何加密資料行的屬性設為 on 語句。Set the SQL_SOPT_SS_COLUMN_ENCRYPTION attribute to SQL_CE_DISABLED on statements that do not access any encrypted columns. 這項設定會停用呼叫 sys.sp_describe_parameter_encryption ,並嘗試解密結果集內的任何值。This setting will disable both calling sys.sp_describe_parameter_encryption and attempts to decrypt any values in the result set.

  • 針對沒有 SQL_SOPT_SS_COLUMN_ENCRYPTION SQL_CE_RESULTSETONLY 任何需要加密的參數的語句,將屬性設為 on,但從加密的資料行中取出資料。Set the SQL_SOPT_SS_COLUMN_ENCRYPTION attribute to SQL_CE_RESULTSETONLY on statements that do not have any parameters requiring encryption, but retrieve data from encrypted columns. 這種設定會停用呼叫 sys.sp_describe_parameter_encryption 和參數加密。This setting will disable calling sys.sp_describe_parameter_encryption and parameter encryption. 包含加密資料行的結果將繼續進行解密。Results containing encrypted columns will continue to be decrypted.

  • 針對將執行一次以上的查詢,使用備妥的語句;使用來準備查詢 SQLPrepare 並儲存語句控制碼,並在 SQLExecute 每次執行時重複使用它。Use prepared statements for queries that will be executed more than once; prepare the query with SQLPrepare and save the statement handle, reusing it with SQLExecute each time it is executed. 即使沒有任何加密資料行,此方法仍是效能慣用的方法,並可讓驅動程式利用快取的中繼資料。This method is the preferred approach for performance even when there are no encrypted columns, and allows the driver to take advantage of cached metadata.

Always Encrypted 安全性設定Always Encrypted Security Settings

強制資料行加密Force Column Encryption

若要強制將參數加密,請透過呼叫 SQLSetDescField 函式來設定 SQL_CA_SS_FORCE_ENCRYPT 實作參數描述項 (IPD) 欄位。To enforce the encryption of a parameter, set the SQL_CA_SS_FORCE_ENCRYPT implementation parameter descriptor (IPD) field through a call to the SQLSetDescField function. 值若不是零,會導致驅動程式在系統未針對相關聯參數傳回任何加密中繼資料時傳回錯誤。A non-zero value causes the driver to return an error when no encryption metadata is returned for the associated parameter.

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 向驅動程式告知參數不需要加密,使用該參數的查詢就會失敗。If SQL Server informs the driver that the parameter does not need to be encrypted, queries using that parameter will fail. 這種行為可提供額外的保護措施,以防止涉及遭入侵的 SQL Server 將不正確的加密中繼資料提供給用戶端的安全性攻擊,這可能會導致資料洩漏。This behavior provides extra protection against security attacks that involve a compromised SQL Server providing incorrect encryption metadata to the client, which may lead to data disclosure.

資料行加密金鑰快取Column Encryption Key Caching

為了將呼叫資料行主要金鑰存放區來解密資料行加密金鑰的次數減少,驅動程式會將純文字 CEK 快取至記憶體中。To reduce the number of calls to a column master key store to decrypt column encryption keys, the driver caches the plaintext CEKs in memory. CEK 快取對驅動程式而言是全域的,未與任何一個連線相關聯。The CEK cache is global to the driver and not associated with any one connection. 從資料庫中繼資料收到 ECEK 之後,驅動程式會先嘗試尋找與快取中加密金鑰值對應的純文字 CEK。After receiving the ECEK from database metadata, the driver first tries to find the plaintext CEK corresponding to the encrypted key value in the cache. 只有在快取中找不到對應的純文字 CEK 時,驅動程式才會呼叫包含 CMK 的金鑰存放區。The driver calls the key store containing the CMK only if it cannot find the corresponding plaintext CEK in the cache.

注意

在 ODBC Driver for SQL Server 中,快取中的項目會在兩小時逾時之後被收回。In the ODBC Driver for SQL Server, the entries in the cache are evicted after a two hour timeout. 此行為意謂著針對指定的 ECEK,驅動程式在應用程式存留期間或每隔兩小時 (以較短者為準) 會連絡金鑰存放區一次。This behavior means that for a given ECEK, the driver contacts the key store only once during the lifetime of the application or every two hours, whichever is less.

從 ODBC Driver 17.1 for SQL Server 開始,即可使用 SQL_COPT_SS_CEKCACHETTL 連線屬性來調整 CEK 快取逾時,此屬性會指定 CEK 將在快取中存留的時間 (秒)。Starting with the ODBC Driver 17.1 for SQL Server, the CEK cache timeout can be adjusted using the SQL_COPT_SS_CEKCACHETTL connection attribute, which specifies the number of seconds a CEK will remain in the cache. 由於快取的全域本質,因此從對驅動程式有效的任何連線控制代碼都可以調整此屬性。Due to the global nature of the cache, this attribute can be adjusted from any connection handle valid for the driver. 當快取 TTL 減少時,也會收回會超過新 TTL 的現有 Cek。When the cache TTL is decreased, existing CEKs that would exceed the new TTL are also evicted. 如果是 0,則不會快取任何 CEK。If it is 0, no CEKs are cached.

受信任的金鑰路徑Trusted Key Paths

從 ODBC Driver 17.1 for SQL Server 開始,SQL_COPT_SS_TRUSTEDCMKPATHS 連線屬性可讓應用程式要求 Always Encrypted 作業只使用指定的 CMK (以其金鑰路徑識別) 清單。Starting with the ODBC Driver 17.1 for SQL Server, the SQL_COPT_SS_TRUSTEDCMKPATHS connection attribute allows an application to require that Always Encrypted operations only use a specified list of CMKs, identified by their key paths. 此屬性預設為 NULL,表示驅動程式會接受任何金鑰路徑。By default, this attribute is NULL, which means that the driver accepts any key path. 若要使用這項功能,請將設定 SQL_COPT_SS_TRUSTEDCMKPATHS 為指向以 null 分隔、以 null 結尾的寬字元字串,以列出 (s) 的允許索引鍵路徑。To use this feature, set SQL_COPT_SS_TRUSTEDCMKPATHS to point to a null-delimited, null-terminated wide-character string that lists the allowed key path(s). 此屬性所指向的記憶體必須在加密或解密期間保持有效 (使用已在記憶體上設定的連線控制代碼) --- 驅動程式會對其檢查伺服器中繼資料所指定的 CMK 路徑在此清單中是否不區分大小寫。The memory pointed to by this attribute must remain valid during encryption or decryption operations using the connection handle on which it is set --- upon which the driver will check if the CMK path as specified by the server metadata is case-insensitively in this list. 如果 CMK 路徑不在清單中,作業就會失敗。If the CMK path is not in the list, the operation fails. 應用程式可以變更此屬性所指向的記憶體內容以變更其受信任的 CMK 清單,而無須重新設定屬性。The application can change the contents of memory this attribute points at, to change its list of trusted CMKs, without setting the attribute again.

使用資料行主要金鑰存放區Working with Column Master Key Stores

若要將資料加密或解密,驅動程式必須取得為目標資料行設定的 CEK。To encrypt or decrypt data, the driver needs to obtain a CEK that is configured for the target column. CEK 會以加密形式 (ECEK) 儲存在資料庫中繼資料中。CEKs are stored in encrypted form (ECEKs) in the database metadata. 每個 CEK 都有一個用來加密它的對應 CMK。Each CEK has a corresponding CMK that was used to encrypt it. 資料庫中繼資料不會儲存 CMK 本身;它只包含金鑰存放區的名稱,以及金鑰存放區可用來尋找 CMK 的資訊。The database metadata does not store the CMK itself; it only contains the name of the keystore and information that the keystore can use to locate the CMK.

為了取得 ECEK 的純文字值,驅動程式會先取得 CEK 及其相對應 CMK 的相關中繼資料,然後使用此資訊來聯絡包含 CMK 的金鑰存放區,並要求它將 ECEK 解密。To obtain the plaintext value of an ECEK, the driver first obtains the metadata about both the CEK and its corresponding CMK, and then it uses this information to contact the keystore containing the CMK and requests it to decrypt the ECEK. 驅動程式會使用金鑰存放區提供者來與金鑰存放區進行通訊。The driver communicates with a keystore using a keystore provider.

內建的金鑰存放區提供者Built-in Keystore Providers

ODBC Driver for SQL Server 隨附下列內建的金鑰存放區提供者:The ODBC Driver for SQL Server comes with the following built-in keystore providers:

名稱Name 描述Description 提供者 (中繼資料) 名稱Provider (metadata) name 可用性Availability
Azure 金鑰保存庫Azure Key Vault 將 CMK 儲存在 Azure Key Vault 中Stores CMKs in an Azure Key Vault AZURE_KEY_VAULT Windows、macOS、LinuxWindows, macOS, Linux
Windows 憑證存放區Windows Certificate Store 將 CMK 儲存在本機 Windows 金鑰存放區中Stores CMKs locally in the Windows keystore MSSQL_CERTIFICATE_STORE WindowsWindows
  • 您 (或您的 DBA) 需要確認設定在資料行主要金鑰中繼資料的提供者名稱是否正確,且資料行主要金鑰路徑符合指定提供者的金鑰路徑格式。You (or your DBA) need to make sure that the provider name, configured in the column master key metadata, is correct and the column master key path complies with the key path format for the given provider. 建議您使用 SQL Server Management Studio 等工具設定金鑰,這樣在發出 CREATE COLUMN MASTER KEY (Transact-SQL) 陳述式時,會自動產生有效的提供者名稱和金鑰路徑。It is recommended that you configure the keys using tools such as SQL Server Management Studio, which automatically generates the valid provider names and key paths when issuing the CREATE COLUMN MASTER KEY (Transact-SQL) statement.

  • 確定您的應用程式可以存取金鑰儲存區中的金鑰。Ensure your application can access the key in the keystore. 此程式可能牽涉到授與您的應用程式存取金鑰和/或金鑰儲存區(視金鑰存放區而定),或執行其他金鑰儲存區特定設定步驟。This process may involve granting your application access to the key and/or the keystore, depending on the keystore, or performing other keystore-specific configuration steps. 例如,若要存取 Azure 金鑰保存庫,您必須將正確的認證提供給金鑰儲存區。For example, to access an Azure Key Vault, you must provide the correct credentials to the keystore.

使用 Azure Key Vault 提供者Using the Azure Key Vault Provider

Azure Key Vault (AKV) 是存放和管理 Always Encrypted 資料行主要金鑰的方便選項 (尤其是當應用程式裝載在 Azure 中時)。Azure Key Vault (AKV) is a convenient option to store and manage column master keys for Always Encrypted (especially if your applications are hosted in Azure). Linux、macOS 及 Windows 上的 ODBC Driver for SQL Server 包含 Azure Key Vault 的內建資料行主要金鑰存放區提供者。The ODBC Driver for SQL Server on Linux, macOS, and Windows includes a built-in column master key store provider for Azure Key Vault. 如需設定 Always Encrypted 的 Azure Key Vault 的詳細資訊,請參閱 Azure Key vault-逐步解說、 開始使用 Key vault,以及 在 Azure Key Vault 中建立資料行主要金鑰For more information on configuring an Azure Key Vault for Always Encrypted, see Azure Key Vault - Step by Step, Getting Started with Key Vault, and Creating Column Master Keys in Azure Key Vault.

注意

ODBC 驅動程式只支援直接針對 Azure Active Directory 的 AKV 驗證。The ODBC Driver only supports AKV authentication directly against Azure Active Directory. 如果使用 AKV 的 Azure Active Directory 驗證,而您的 Active Directory 組態需要驗證 Active Directory 同盟服務端點,則驗證可能會失敗。If you are using Azure Active Directory authentication to AKV and your Active Directory configuration requires authentication against an Active Directory Federation Services endpoint, authentication may fail. 在 Linux 和 macOS 上,針對驅動程式 17.2 版和更新版本,必須要有 libcurl,才能使用此提供者,但這不是明確相依性,因為驅動程式的其他作業並不需要它。On Linux and macOS, for driver version 17.2 and later, libcurl is required to use this provider, but is not an explicit dependency since other operations with the driver do not require it. 如果您遇到有關 libcurl 的錯誤,請確定它已安裝。If you encounter an error regarding libcurl, ensure it is installed.

驅動程式支援使用下列認證類型向 Azure Key Vault 進行驗證:The driver supports authenticating to Azure Key Vault using the following credential types:

  • 使用者名稱/密碼 - 使用此方法時,認證係指 Azure Active Directory 使用者名稱及其密碼。Username/Password - with this method, the credentials are the name of an Azure Active Directory user and its password.

  • 用戶端識別碼/祕密 - 使用此方法時,認證係指應用程式用戶端識別碼和應用程式祕密。Client ID/Secret - with this method, the credentials are an application client ID and an application secret.

  • 受控識別 (17.5.2 +) -系統或使用者指派;如需詳細資訊,請參閱 適用于 Azure 資源的受控識別。Managed Identity (17.5.2+) - either system or user-assigned; for more information, see Managed Identities for Azure resources.

  • Azure Key Vault Interactive (17.7 + Windows 驅動程式) -使用此方法時,認證會透過具有登入識別碼的 Azure Active Directory 進行驗證。Azure Key Vault Interactive (17.7+ Windows drivers) - with this method, the credentials are authenticated through Azure Active Directory with Login ID.

為了允許驅動程式使用儲存在 AKV 中的 CMK 來進行資料行加密,請使用下列僅限連接字串的關鍵字:To allow the driver to use CMKs stored in AKV for column encryption, use the following connection-string-only keywords:

認證類型Credential Type KeyStoreAuthentication KeyStorePrincipalId KeyStoreSecret
使用者名稱/密碼Username/password KeyVaultPassword 使用者主體名稱User Principal Name 密碼Password
用戶端識別碼/祕密Client ID/secret KeyVaultClientSecret 用戶端識別碼Client ID 祕密Secret
受控識別Managed Identity KeyVaultManagedIdentity 物件識別碼 (選擇性,僅適用於使用者指派)Object ID (optional, for user-assigned only) (未指定)(not specified)
AKV 互動AKV Interactive KeyVaultInteractive (未設定)(not set) (未設定)(not set)

範例連接字串Example Connection Strings

下列連接字串顯示如何使用兩種認證類型向 Azure Key Vault 進行驗證:The following connection strings show how to authenticate to Azure Key Vault with the two credential types:

用戶端識別碼/祕密ClientID/Secret:

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

使用者名稱/密碼Username/Password:

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

受控識別 (系統指派)Managed Identity (system-assigned)

DRIVER=ODBC Driver 17 for SQL Server;SERVER=myServer;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultManagedIdentity

受控識別 (使用者指派)Managed Identity (user-assigned)

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

AKV 互動AKV Interactive

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

無須進行其他 ODBC 應用程式變更,即可使用 AKV 來儲存 CMK。No other ODBC application changes are required to use AKV for CMK storage.

注意

驅動程式包含其信任的 AKV 端點清單。The driver contains a list of AKV endpoints which it trusts. 從驅動程式 17.5.2 版開始,您可設定這份清單:在驅動程式或 DSN 的 ODBCINST.INI 或 ODBC.INI 登錄機碼 (Windows),或在 odbcinst.iniodbc.ini 檔案區段 (Linux/macOS) 中,將 AKVTrustedEndpoints 屬性設定為以分號分隔的清單。Starting with driver version 17.5.2, this list is configurable: set the AKVTrustedEndpoints property in the driver or DSN's ODBCINST.INI or ODBC.INI registry key (Windows) or odbcinst.ini or odbc.ini file section (Linux/macOS) to a semicolon-delimited list. 在 DSN 中設定該屬性會優先於驅動程式中的設定。Setting it in the DSN takes precedence over a setting in the driver. 如果值以分號開頭,則會延伸預設清單;否則會取代預設清單。If the value begins with a semicolon, it extends the default list; otherwise, it replaces the default list. 預設清單 (從 17.5 版開始) 為 vault.azure.net;vault.azure.cn;vault.usgovcloudapi.net;vault.microsoftazure.deThe default list (as of 17.5) is vault.azure.net;vault.azure.cn;vault.usgovcloudapi.net;vault.microsoftazure.de. 從17.7 開始,清單也包含 managedhsm.azure.net;managedhsm.azure.cn;managedhsm.usgovcloudapi.net;managedhsm.microsoftazure.deStarting with 17.7, the list also includes managedhsm.azure.net;managedhsm.azure.cn;managedhsm.usgovcloudapi.net;managedhsm.microsoftazure.de.

使用 Windows 憑證存放區提供者Using the Windows Certificate Store Provider

Windows 上的 ODBC Driver for SQL Server 包含「Windows 憑證存放區」的內建資料行主要金鑰存放區提供者,名為 MSSQL_CERTIFICATE_STOREThe ODBC Driver for SQL Server on Windows includes a built-in column master key store provider for the Windows Certificate Store, named MSSQL_CERTIFICATE_STORE. (在 macOS 或 Linux 上無法使用此提供者。 ) 使用此提供者時,會將 CMK 儲存在本機用戶端電腦上,而且應用程式不需要額外設定就能與驅動程式搭配使用。(This provider is not available on macOS or Linux.) With this provider, the CMK is stored locally on the client machine and no extra configuration by the application is necessary to use it with the driver. 不過,應用程式必須能夠存取存放區中的憑證及其私密金鑰。However, the application must have access to the certificate and its private key in the store. 如需詳細資訊,請參閱 建立及儲存資料行主要金鑰 (永遠加密)For more information, see Create and Store Column Master Keys (Always Encrypted).

使用自訂金鑰儲存區提供者Using Custom Keystore Providers

ODBC Driver for SQL Server 也支援使用 CEKeystoreProvider 介面來自訂協力廠商金鑰存放區提供者。The ODBC Driver for SQL Server also supports custom third-party keystore providers using the CEKeystoreProvider interface. 這項功能可讓應用程式載入、查詢和設定金鑰存放區提供者,以便驅動程式用來存取加密的資料行。This feature allows an application to load, query, and configure keystore providers so that they can be used by the driver to access encrypted columns. 應用程式也可以直接與金鑰存放區提供者進行互動,來加密 CEK 以儲存在 SQL Server 中,以及執行以 ODBC 存取加密資料行以外的工作;如需詳細資訊,請參閱自訂金鑰存放區提供者Applications may also directly interact with a keystore provider in order to encrypt CEKs for storage in SQL Server and perform tasks beyond accessing encrypted columns with ODBC; for more information, see Custom Keystore Providers.

有兩個連線屬性會用來與自訂金鑰存放區提供者進行互動。Two connection attributes are used to interact with custom keystore providers. 其中包括:They are:

  • SQL_COPT_SS_CEKEYSTOREPROVIDER

  • SQL_COPT_SS_CEKEYSTOREDATA

前者可用來載入和列舉已載入的金鑰存放區提供者,後者則可啟用應用程式提供者通訊。The former is used to load and enumerate loaded keystore providers, while the latter enables application-provider communications. 這些連線屬性隨時都可使用,不論是在建立連線之前還是之後,因為應用程式提供者互動並未牽涉到與 SQL Server 進行通訊。These connection attributes may be used at any time, before or after establishing a connection, since application-provider interaction does not involve communication with SQL Server. 不過,由於尚未載入驅動程式,因此在連線前設定和取得這些屬性將促使「驅動程式管理員」處理它們,而可能不會產生預期的結果。However, because the driver has not been loaded yet, setting and getting these attributes before connecting will cause them to be processed by the Driver Manager, and may not yield the expected results.

載入金鑰存放區提供者Loading a Keystore Provider

設定 SQL_COPT_SS_CEKEYSTOREPROVIDER 連線屬性可讓用戶端應用程式載入提供者程式庫,讓其中所含的金鑰存放區提供者變成可供使用。Setting the SQL_COPT_SS_CEKEYSTOREPROVIDER connection attribute enables a client application to load a provider library, making available for use the keystore providers contained therein.

SQLRETURN SQLSetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);
引數Argument 描述Description
ConnectionHandle [輸入] 連線控制代碼。[Input] Connection handle. 必須是有效的連線控制代碼,但提供者若是透過一個連線控制代碼載入的,則從相同處理序中的任何其他提供者都可存取這些提供者。Must be a valid connection handle, but providers loaded via one connection handle are accessible from any other in the same process.
Attribute [輸入] 要設定的屬性:SQL_COPT_SS_CEKEYSTOREPROVIDER 常數。[Input] Attribute to set: the SQL_COPT_SS_CEKEYSTOREPROVIDER constant.
ValuePtr [輸入] 以 Null 結尾之字元字串的指標,這是指定提供者程式庫之檔案名稱的字元字串。[Input] Pointer to a null-terminated character string specifying the filename of the provider library. 若為 SQLSetConnectAttrA,此值為 ANSI (多位元組) 字串。For SQLSetConnectAttrA, this value is an ANSI (multibyte) string. 若為 SQLSetConnectAttrW,此值是 Unicode (wchar_t) 字串。For SQLSetConnectAttrW, this value is a Unicode (wchar_t) string.
StringLength [輸入] 輸入 ValuePtr 字串或 SQL_NTS 的長度。[Input] The length of the ValuePtr string, or SQL_NTS.

驅動程式會使用平台定義的動態程式庫載入機制 (在 Linux 和 macOS 上是 dlopen(),在 Windows 上是 LoadLibrary()) 來嘗試載入 ValuePtr 參數所識別的程式庫,然後將該處定義的任何提供者新增至驅動程式已知的提供者清單。The driver attempts to load the library identified by the ValuePtr parameter using the platform-defined dynamic library loading mechanism (dlopen() on Linux and macOS, LoadLibrary() on Windows), and adds any providers defined therein to the list of providers known to the driver. 以下是可能發生的錯誤:The following errors may occur:

錯誤Error 描述Description
CE203 無法載入動態程式庫。The dynamic library could not be loaded.
CE203 在程式庫中找不到 "CEKeyStoreProvider" 匯出的符號。The "CEKeyStoreProvider" exported symbol was not found in the library.
CE203 已經載入程式庫中的一或多個提供者。One or more providers in the library are already loaded.

SQLSetConnectAttr 傳回一般錯誤或成功的值,以及透過標準 ODBC 診斷機制所發生之任何錯誤的詳細資訊。SQLSetConnectAttr returns the usual error or success values, and more information is available for any errors that occurred via the standard ODBC diagnostic mechanism.

注意

應用程式程式設計人員必須確保在透過任何連線傳送需要任何自訂提供者的任何查詢之前,先載入這些提供者。The application programmer must ensure that any custom providers are loaded before any query requiring them is sent over any connection. 無法執行這項操作時,會導致發生錯誤:Failure to do so results in the error:

錯誤Error 描述Description
CE200 找不到金鑰存放區提供者 %1。Keystore provider %1 not found. 請確定已載入適當的金鑰存放區提供者程式庫。Ensure that the appropriate keystore provider library has been loaded.

注意

金鑰存放區提供者實施者應該避免在其自訂提供者的名稱中使用 MSSQLKeystore provider implementors should avoid the use of MSSQL in the name of their custom providers. 這個詞彙是專門保留給 Microsoft 使用,而可能造成與未來的內建提供者發生衝突。This term is reserved exclusively for Microsoft use and may cause conflicts with future built-in providers. 在自訂提供者的名稱中使用這個詞彙可能會造成 ODBC 警告。Using this term in the name of a custom provider may result in an ODBC warning.

取得載入的提供者清單Getting the List of Loaded Providers

取得此連線屬性可讓用戶端應用程式判斷目前在驅動程式中載入的金鑰儲存區提供者 (包括內建的提供者。 ) 此程式只能在連線後執行。Getting this connection attribute enables a client application to determine the keystore providers currently loaded in the driver (including those providers built-in.) This process can only be performed after connecting.

SQLRETURN SQLGetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);
引數Argument 描述Description
ConnectionHandle [輸入] 連線控制代碼。[Input] Connection handle. 必須是有效的連線控制代碼,但提供者若是透過一個連線控制代碼載入的,則從相同處理序中的任何其他提供者都可存取這些提供者。Must be a valid connection handle, but providers loaded via one connection handle are accessible from any other in the same process.
Attribute [輸入] 要擷取的屬性:SQL_COPT_SS_CEKEYSTOREPROVIDER 常數。[Input] Attribute to retrieve: the SQL_COPT_SS_CEKEYSTOREPROVIDER constant.
ValuePtr [輸出] 記憶體的指標,這是其中要傳回下一個所載入提供者名稱的記憶體。[Output] A pointer to memory in which to return the next loaded provider name.
BufferLength [輸入] 緩衝區 ValuePtr 的長度。[Input] The length of the buffer ValuePtr.
StringLengthPtr [輸出] 緩衝區的指標,這是其中要傳回可供在 *ValuePtr 中傳回之位元組總數 (不包括以 Null 結尾的字元) 的緩衝區。[Output] A pointer to a buffer in which to return the total number of bytes (excluding the null-termination character) available to return in *ValuePtr. 如果 ValuePtr 是 Null 指標,則不會傳回任何長度。If ValuePtr is a null pointer, no length is returned. 如果屬性值是字元字串,且可供傳回的位元組數大於 BufferLength 減去以 Null 結尾之字元長度所得的數目,則 *ValuePtr 中的資料會依據 BufferLength 減去以 Null 結尾之字元長度所得的數目截斷,並由驅動程式以 Null 結尾。If the attribute value is a character string and the number of bytes available to return is greater than BufferLength minus the length of the null-termination character, the data in *ValuePtr is truncated to BufferLength minus the length of the null-termination character and is null-terminated by the driver.

為了允許擷取整個清單,每個 Get 作業都會傳回目前提供者的名稱,並讓內部計數器在到下一個提供者時遞增。To allow retrieving the entire list, every Get operation returns the current provider's name, and increments an internal counter to the next one. 當此計數器到達清單結尾時,就會傳回空字串 (""),然後計數器會重設;後續的 Get 作業會接著再次從清單開頭繼續進行。Once this counter reaches the end of the list, an empty string ("") is returned, and the counter is reset; successive Get operations then proceed again from the beginning of the list.

與金鑰存放區提供者進行通訊Communicating with Keystore Providers

SQL_COPT_SS_CEKEYSTOREDATA連接屬性可讓用戶端應用程式與已載入的金鑰儲存區提供者進行通訊,以設定更多參數、金鑰資料等。用戶端應用程式與提供者之間的通訊,會遵循簡單的要求-回應通訊協定,此通訊協定是以使用此連接屬性的 Get 和 Set 要求為基礎。The SQL_COPT_SS_CEKEYSTOREDATA connection attribute enables a client application to communicate with loaded keystore providers for configuring more parameters, keying material, etc. The communication between a client application and a provider follows a simple request-response protocol, based on Get and Set requests using this connection attribute. 通訊只會由用戶端應用程式起始。Communication is initiated only by the client application.

注意

由於 ODBC 的本質是會呼叫 CEKeyStoreProvider 對 (SQLGet/SetConnectAttr) 的回應,因此 ODBC 介面僅支援在解析連線內容時設定資料。Due to the nature of the ODBC calls CEKeyStoreProvider's respond to (SQLGet/SetConnectAttr), the ODBC interface only supports setting data at the resolution of the connection context.

應用程式會透過驅動程式、經由 CEKeystoreData 結構與金鑰存放區提供者進行通訊:The application communicates with keystore providers through the driver via the CEKeystoreData structure:

typedef struct CEKeystoreData {
wchar_t *name;
unsigned int dataSize;
char data[];
} CEKEYSTOREDATA;
引數Argument 描述Description
name [輸入] 進行 Set 時,要作為資料傳送對象的提供者名稱。[Input] Upon Set, the name of the provider to which the data is sent. 進行 Get 時會忽略。Ignored upon Get. 以 Null 結尾的寬字元字串。Null-terminated, wide-character string.
dataSize [輸入] 接在結構之後的資料陣列大小。[Input] The size of the data array following the structure.
data [InOut] 進行 Set 時,要傳送給提供者的資料。[InOut] Upon Set, the data to be sent to the provider. 此資料可能是任意的;驅動程式不會嘗試解讀。This data may be arbitrary; the driver makes no attempt to interpret it. 進行 Get 時,要接收從提供者讀取之資料的緩衝區。Upon Get, the buffer to receive the data read from the provider.

將資料寫入至提供者Writing data to a provider

使用 SQL_COPT_SS_CEKEYSTOREDATA 屬性的 SQLSetConnectAttr 呼叫會將資料「封包」寫入至指定的金鑰存放區提供者。A SQLSetConnectAttr call using the SQL_COPT_SS_CEKEYSTOREDATA attribute writes a "packet" of data to the specified keystore provider.

SQLRETURN SQLSetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);
引數Argument 描述Description
ConnectionHandle [輸入] 連線控制代碼。[Input] Connection handle. 必須是有效的連線控制代碼,但提供者若是透過一個連線控制代碼載入的,則從相同處理序中的任何其他提供者都可存取這些提供者。Must be a valid connection handle, but providers loaded via one connection handle are accessible from any other in the same process.
Attribute [輸入] 要設定的屬性:SQL_COPT_SS_CEKEYSTOREDATA 常數。[Input] Attribute to set: the SQL_COPT_SS_CEKEYSTOREDATA constant.
ValuePtr [輸入] CEKeystoreData 結構的指標。[Input] Pointer to a CEKeystoreData structure. 結構的名稱欄位會識別要作為資料適用對象的提供者。The name field of the structure identifies the provider for which the data is intended.
StringLength [輸入] SQL_IS_POINTER 常數[Input] SQL_IS_POINTER constant

您可以透過 SQLGetDiacRec取得更詳細的錯誤資訊。More detailed error information may be obtained via SQLGetDiacRec.

注意

如果需要,提供者可以使用連線控制代碼將寫入的資料與特定連線建立關聯。The provider can use the connection handle to associate the written data to a specific connection, if it so desires. 這項功能適用于執行每個連線設定。This feature is useful for implementing per-connection configuration. 它也可以忽略連線內容,而不論用來傳送資料的連線為何,都以一致的方式處理資料。It may also ignore the connection context and treat the data identically regardless of the connection used to send the data. 如需詳細資訊,請參閱 內容關聯For more information, see Context Association.

從提供者讀取資料Reading data from a provider

使用 SQL_COPT_SS_CEKEYSTOREDATA 屬性的 SQLGetConnectAttr 呼叫會從「上次寫入的」 提供者讀取資料「封包」。A call to SQLGetConnectAttr using the SQL_COPT_SS_CEKEYSTOREDATA attribute reads a "packet" of data from the last-written-to provider. 如果沒有任何提供者,就會發生「函數順序錯誤」。If there was none, a Function Sequence Error occurs. 建議金鑰存放區提供者實施者支援使用 0 位元組的「虛擬寫入」作為一種選取讀取作業之提供者而不會造成其他副作用的方式 (如果這麼做有意義的話)。Keystore provider implementers are encouraged to support "dummy writes" of 0 bytes as a way of selecting the provider for read operations without causing other side-effects, if it makes sense to do so.

SQLRETURN SQLGetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);
引數Argument 描述Description
ConnectionHandle [輸入] 連線控制代碼。[Input] Connection handle. 必須是有效的連線控制代碼,但提供者若是透過一個連線控制代碼載入的,則從相同處理序中的任何其他提供者都可存取這些提供者。Must be a valid connection handle, but providers loaded via one connection handle are accessible from any other in the same process.
Attribute [輸入] 要擷取的屬性:SQL_COPT_SS_CEKEYSTOREDATA 常數。[Input] Attribute to retrieve: the SQL_COPT_SS_CEKEYSTOREDATA constant.
ValuePtr [輸出] CEKeystoreData 結構的指標,這是其中放置從提供者讀取之資料的結構。[Output] A pointer to a CEKeystoreData structure in which the data read from the provider is placed.
BufferLength [輸入] SQL_IS_POINTER 常數[Input] SQL_IS_POINTER constant
StringLengthPtr [輸出] 緩衝區的指標,這是其中要傳回 BufferLength 的緩衝區。[Output] A pointer to a buffer in which to return BufferLength. 如果 *ValuePtr 是 Null 指標,則不會傳回任何長度。If *ValuePtr is a null pointer, no length is returned.

呼叫端必須確保在 CEKEYSTOREDATA 結構之後配置長度足夠的緩衝區以供提供者寫入。The caller must ensure that a buffer of sufficient length following the CEKEYSTOREDATA structure is allocated for the provider to write into. 傳回時,其 dataSize 欄位中會填入從提供者讀取之資料的實際長度。Upon return, its dataSize field is updated with the actual length of data read from the provider. 您可以透過 SQLGetDiacRec取得更詳細的錯誤資訊。More detailed error information may be obtained via SQLGetDiacRec.

此介面不會對應用程式與金鑰儲存區提供者之間傳輸的資料格式提供任何額外的需求。This interface places no extra requirements on the format of data transferred between an application and a keystore provider. 每個提供者都可以依據其需求,定義自己的通訊協定/資料格式。Each provider can define its own protocol/data format, depending on its needs.

如需有關實作您自己金鑰存放區提供者的範例,請參閱自訂金鑰存放區提供者For an example of implementing your own keystore provider, see Custom Keystore Providers

使用 Always Encrypted 時的 ODBC 驅動程式限制Limitations of the ODBC driver when using Always Encrypted

非同步作業Asynchronous Operations

雖然 ODBC 驅動程式會允許搭配 Always Encrypted 使用非同步作業,但在啟用 Always Encrypted 的情況下,會影響作業效能。While the ODBC driver will allow the use of asynchronous operations with Always Encrypted, there is a performance impact on the operations when Always Encrypted is enabled. 用以判斷陳述式之加密中繼資料的 sys.sp_describe_parameter_encryption 呼叫會封鎖驅動程式,並造成驅動程式會先等候伺服器傳回中繼資料,然後才傳回 SQL_STILL_EXECUTINGThe call to sys.sp_describe_parameter_encryption to determine encryption metadata for the statement is blocking and will cause the driver to wait for the server to return the metadata before returning SQL_STILL_EXECUTING.

使用 SQLGetData 來分段擷取資料Retrieve data in parts with SQLGetData

在 ODBC Driver 17 for SQL Server 之前,無法使用 SQLGetData 來分段擷取加密字元和二進位資料行。Before ODBC Driver 17 for SQL Server, encrypted character and binary columns cannot be retrieved in parts with SQLGetData. 只能搭配長度足以包含整個資料行之資料的緩衝區,對 SQLGetData 進行一次呼叫。Only one call to SQLGetData can be made, with a buffer of sufficient length to contain the entire column's data.

使用 SQLPutData 來分段傳送資料Send data in parts with SQLPutData

在 ODBC Driver 17.3 for SQL Server 之前,無法使用 SQLPutData 來分段傳送用於插入或比較的資料。Before ODBC Driver 17.3 for SQL Server, data for insertion or comparison cannot be sent in parts with SQLPutData. 只能搭配包含整個資料的緩衝區,對 SQLPutData 進行一次呼叫。Only one call to SQLPutData can be made, with a buffer containing the entire data. 若要將長資料插入至加密資料行,請使用「大量複製 API」(下一節會提供說明) 搭配輸入資料檔。For inserting long data into encrypted columns, use the Bulk Copy API, described in the next section, with an input data file.

加密的 money 和 smallmoneyEncrypted money and smallmoney

加密的 moneysmallmoney 資料行不能以參數為目標,因為沒有對應至這些類型的特定 ODBC 資料類型,因此會導致運算元類型衝突錯誤。Encrypted money or smallmoney columns cannot be targeted by parameters, since there is no specific ODBC data type that maps to those types, resulting in Operand Type Clash errors.

大量複製加密資料行Bulk Copy of Encrypted Columns

從 ODBC Driver 17 for SQL Server 開始,便支援搭配 Always Encrypted 使用 SQL 大量複製函式bcp 公用程式。Use of the SQL Bulk Copy functions and the bcp utility is supported with Always Encrypted since ODBC Driver 17 for SQL Server. 不論是純文字 (在插入時加密,然後在擷取時解密) 還是加密文字 (逐字傳輸),您都可以使用「大量複製 (bcp_*) API」和 bcp 公用程式來插入和擷取。Both plaintext (encrypted on insertion and decrypted on retrieval) and ciphertext (transferred verbatim) can be inserted and retrieved using the Bulk Copy (bcp_*) APIs and the bcp utility.

  • 若要以 Varbinary (max) 形式取出加密文字 (例如,將大量載入至不同的資料庫) ,請在不使用選項的情況下連接, ColumnEncryption 或將它設定為 Disabled (,然後執行 BCP OUT 作業。To retrieve ciphertext in varbinary(max) form (for example, for bulk loading into a different database), connect without the ColumnEncryption option (or set it to Disabled) and perform a BCP OUT operation.

  • 若要插入和擷取純文字,並讓驅動程式視需要在背景中自動執行加密和解密,則將 ColumnEncryption 設定為 Enabled 即已足夠。To insert and retrieve plaintext, and let the driver transparently perform encryption and decryption as required, setting ColumnEncryption to Enabled is sufficient. 除此之外,BCP API 的功能則不變。The functionality of the BCP API is otherwise unchanged.

  • 若要在 Varbinary (max) 表單中插入加密文字 (例如,如) 以上所示,請將 BCPMODIFYENCRYPTED 選項設定為 TRUE,然後執行 BCP in 作業。To insert ciphertext in varbinary(max) form (for example, as retrieved above), set the BCPMODIFYENCRYPTED option to TRUE and perform a BCP IN operation. 為了將產生的資料解密,請確定目的地資料行的 CEK 與原先取得加密文字的 CEK 是相同的 CEK。In order for the resulting data to be decrypted, ensure that the destination column's CEK is the same CEK as the one from which the ciphertext was originally obtained.

使用 bcp 公用程式時:若要控制 ColumnEncryption 設定,請使用 -D 選項並指定包含所需值的 DSN。When using the bcp utility: To control the ColumnEncryption setting, use the -D option and specify a DSN containing the desired value. 若要插入加密文字,請確定已啟用使用者的 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 設定。To insert ciphertext, ensure the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS setting of the user is enabled.

下表提供在加密資料行上操作時的動作摘要:The following table provides a summary of the actions when operating on an encrypted column:

ColumnEncryption BCP 方向BCP Direction 描述Description
Disabled OUT (至用戶端)OUT (to client) 擷取加密文字。Retrieves ciphertext. 觀察到的資料類型是 varbinary(max)The observed datatype is varbinary(max).
Enabled OUT (至用戶端)OUT (to client) 擷取純文字。Retrieves plaintext. 驅動程式會將資料行資料解密。The driver will decrypt the column data.
Disabled IN (至伺服器)IN (to server) 插入加密文字。Inserts ciphertext. 這項設定的目的是要在不需要解密的情況下,以透明方式移動加密的資料。This setting is intended for opaquely moving encrypted data without requiring it to be decrypted. 如果未在使用者上設定 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 選項,或未在連線控制代碼上設定 BCPMODIFYENCRYPTED,此作業就會失敗。The operation will fail if the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option is not set on the user, or BCPMODIFYENCRYPTED is not set on the connection handle. 如需詳細資訊,請參閱下列內容。For more information, see below.
Enabled IN (至伺服器)IN (to server) 插入純文字。Inserts plaintext. 驅動程式會將資料行資料加密。The driver will encrypt the column data.

BCPMODIFYENCRYPTED 選項The BCPMODIFYENCRYPTED option

為了防止資料損毀,伺服器通常不允許將加密文字直接插入至加密資料行,因此嘗試這麼做會失敗;不過,針對使用 BCP API 來大量載入加密資料,將 BCPMODIFYENCRYPTED bcp_control 選項設定為 TRUE 將可允許直接插入加密文字,並降低有關在使用者帳戶上設定 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 選項所造成的加密資料損毀風險。To prevent data corruption, the server normally does not allow inserting ciphertext directly into an encrypted column, and thus attempts to do so will fail; however, for bulk loading of encrypted data using the BCP API, setting the BCPMODIFYENCRYPTED bcp_control option to TRUE will allow ciphertext to be inserted directly, and reduces the risk of corrupting encrypted data over setting the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option on the user account. 不過,金鑰必須與資料相符,而理想的做法是在進行大量插入後及在進一步使用之前,對插入的資料執行一些唯讀檢查。Nonetheless, the keys must match the data and it is a good idea to perform some read-only checks of the inserted data after the bulk insertion and before further use.

如需詳細資訊,請參閱移轉透過 Always Encrypted 保護的機密資料For more information, see Migrate Sensitive Data Protected by Always Encrypted.

Always Encrypted API 摘要Always Encrypted API Summary

連接字串關鍵字Connection String Keywords

名稱Name 描述Description
ColumnEncryption 接受的值為 Enabled/DisabledAccepted values are Enabled/Disabled.
Enabled -- 啟用連線的 Always Encrypted 功能。Enabled -- enables Always Encrypted functionality for the connection.
Disabled -- 停用連線的 Always Encrypted 功能。Disabled -- disable Always Encrypted functionality for the connection.
證明通訊協定證明 URL -- (17.4 版和更新版本) 使用指定的證明通訊協定和證明 URL,以安全記憶體保護區來啟用 Always Encrypted。attestation protocol, attestation URL -- (version 17.4 and later) enables Always Encrypted with secure enclave using the specified attestation protocol and the attestation URL.

預設為 DisabledThe default is Disabled.
KeyStoreAuthentication 有效的值: KeyVaultPasswordKeyVaultClientSecretKeyVaultInteractiveValid Values: KeyVaultPassword, KeyVaultClientSecret, KeyVaultInteractive
KeyStorePrincipalId KeyStoreAuthentication = KeyVaultPassword 時,請將此值設定為有效的「Azure Active Directory 使用者主體名稱」。When KeyStoreAuthentication = KeyVaultPassword, set this value to a valid Azure Active Directory User Principal Name.
KeyStoreAuthetication = KeyVaultClientSecret 時,請將此值設定為有效的「Azure Active Directory 應用程式用戶端識別碼」When KeyStoreAuthetication = KeyVaultClientSecret set this value to a valid Azure Active Directory Application Client ID
KeyStoreSecret KeyStoreAuthentication = KeyVaultPassword 時,請將此值設定為相對應使用者名稱的密碼。When KeyStoreAuthentication = KeyVaultPassword set this value to the password for the corresponding user name.
KeyStoreAuthentication = KeyVaultClientSecret 時,請將此值設定為與有效「Azure Active Directory 應用程式用戶端識別碼」相關聯的「應用程式祕密」When KeyStoreAuthentication = KeyVaultClientSecret set this value to the Application Secret associated with a valid Azure Active Directory Application Client ID

連接屬性Connection Attributes

名稱Name 類型Type 描述Description
SQL_COPT_SS_COLUMN_ENCRYPTION 連線前Pre-connect SQL_COLUMN_ENCRYPTION_DISABLE (0) -- 停用 Always EncryptedSQL_COLUMN_ENCRYPTION_DISABLE (0) -- Disable Always Encrypted
SQL_COLUMN_ENCRYPTION_ENABLE (1) -- 啟用 Always EncryptedSQL_COLUMN_ENCRYPTION_ENABLE (1) -- Enable Always Encrypted
證明通訊協定證明 URL 字串的指標-- (17.4 版和更新版本,) 以安全記憶體保護區啟用pointer to attestation protocol,attestation URL string -- (version 17.4 and later) enable with secure enclave
SQL_COPT_SS_CEKEYSTOREPROVIDER 連線後Post-connect [設定]-嘗試載入 CEKeystoreProvider[Set] - Attempt to load CEKeystoreProvider
[Get]-傳回 CEKeystoreProvider 名稱[Get] - Return a CEKeystoreProvider name
SQL_COPT_SS_CEKEYSTOREDATA 連線後Post-connect [Set]-將資料寫入至 CEKeystoreProvider[Set] - Write data to CEKeystoreProvider
[Get]-從 CEKeystoreProvider 讀取資料[Get] - Read data from CEKeystoreProvider
SQL_COPT_SS_CEKCACHETTL 連線後Post-connect [設定]-設定 CEK 快取 TTL[Set] - Set the CEK cache TTL
[Get]-取得目前的 CEK 快取 TTL[Get] - Get the current CEK cache TTL
SQL_COPT_SS_TRUSTEDCMKPATHS 連線後Post-connect [設定]-設定受信任的 CMK 路徑指標[Set] - Set the trusted CMK paths pointer
[Get]-取得目前的受信任 CMK 路徑指標[Get] - Get the current trusted CMK paths pointer

陳述式屬性Statement Attributes

名稱Name 描述Description
SQL_SOPT_SS_COLUMN_ENCRYPTION SQL_CE_DISABLED (0) -- 針對陳述式停用 Always EncryptedSQL_CE_DISABLED (0) -- Always Encrypted is disabled for the statement
SQL_CE_RESULTSETONLY (1) -- 僅解密。SQL_CE_RESULTSETONLY (1) -- Decryption Only. 結果集和傳回值已解密,而參數未加密Result sets and return values are decrypted, and parameters are not encrypted
SQL_CE_ENABLED (3) -- 同時針對參數和結果啟用並使用 Always EncryptedSQL_CE_ENABLED (3) -- Always Encrypted is enabled and used for both parameters and results

描述項欄位Descriptor Fields

IPD 欄位IPD Field 大小/類型Size/Type 預設值Default Value 描述Description
SQL_CA_SS_FORCE_ENCRYPT (1236)SQL_CA_SS_FORCE_ENCRYPT (1236) WORD (2 個位元組)WORD (2 bytes) 00 若為 0 (預設):加密此參數的決定會取決於加密中繼資料的可用性。When 0 (default): decision to encrypt this parameter is determined by availability of encryption metadata.

若不為 0:如果有加密中繼資料可供此參數使用,就會加密。When nonzero: if encryption metadata is available for this parameter, it is encrypted. 否則,要求會因以下錯誤而失敗:[CE300] [Microsoft][ODBC Driver 17 for SQL Server]已為參數指定了強制加密,但伺服器沒有提供任何加密中繼資料。Otherwise, the request fails with error [CE300] [Microsoft][ODBC Driver 17 for SQL Server]Mandatory encryption was specified for a parameter but no encryption metadata was provided by the server.

bcp_control 選項bcp_control Options

選項名稱Option Name 預設值Default Value 描述Description
BCPMODIFYENCRYPTED (21)BCPMODIFYENCRYPTED (21) FALSEFALSE 若為 TRUE,允許將 varbinary(max) 值插入至加密資料行。When TRUE, allows varbinary(max) values to be inserted into an encrypted column. 若為 FALSE,除非提供正確的類型和加密中繼資料,否則會防止插入。When FALSE, prevents insertion unless correct type and encryption metadata is supplied.

疑難排解Troubleshooting

如果使用 Always Encrypted 時遇到困難,請先檢查下列幾點:When encountering difficulties in using Always Encrypted, start by checking the following points:

  • 加密所需資料行的 CEK 存在,而且可在伺服器上存取。The CEK that encrypts the desired column is present and accessible on the server.

  • 加密 CEK 的 CMK 在伺服器上具有可存取的中繼資料,而且也可從用戶端存取。The CMK that encrypts the CEK has accessible metadata on the server and is also accessible from the client.

  • ColumnEncryption 在 DSN、連接字串或連接屬性中啟用,而且如果使用安全記憶體保護區,則具有正確的格式。ColumnEncryption is enabled in the DSN, connection string, or connection attribute, and if using the secure enclave, has the correct format.

此外,使用安全記憶體保護區時,證明失敗會根據下表,識別發生失敗的證明程式中的步驟:Additionally, when using the secure enclave, attestation failures identify the step in the attestation process where the failure occurred, according to the following table:

步驟Step 描述Description
0-990-99 無效的證明回應或簽章驗證錯誤。Invalid attestation response, or signature verification error.
100-199100-199 從證明 URL 擷取憑證時發生錯誤。Error retrieving certificates from attestation URL. 請確定 <attestation URL>/v2.0/signingCertificates 有效且可供存取。Ensure <attestation URL>/v2.0/signingCertificates is valid and accessible.
200-299200-299 記憶體保護區身分識別的格式錯誤或不是預期的格式。Unexpected or incorrect format of enclave's identity.
300-399300-399 使用記憶體保護區建立安全通道時發生錯誤。Error establishing secure channel with enclave.

另請參閱See Also