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

本主題適用於: 是SQL Server 2019 與更新版本 (僅限 Windows) 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server 2019 and later (Windows only) noAzure SQL DatabasenoAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

具有安全記憶體保護區的 Always Encrypted 可為 Always Encrypted 功能提供額外功能。Always Encrypted with secure enclaves provides additional functionality to the Always Encrypted feature.

Always Encrypted 已在 SQL Server 2016 中引進,可保護敏感性資料的機密性,免於遭受惡意程式碼和 SQL Server 高權限的「未經授權」 使用者威脅。Introduced in SQL Server 2016, Always Encrypted protects the confidentiality of sensitive data from malware and high-privileged unauthorized users of SQL Server. 高權限的未經授權使用者為 DBA、電腦系統管理員、雲端系統管理員,或是對伺服器執行個體、硬體等具有合法存取權,但不應該具有部分或所有實際資料存取權的其他人。High-privileged unauthorized users are DBAs, computer admins, cloud admins, or anyone else who has legitimate access to server instances, hardware, etc., but who should not have access to some or all of the actual data.

若沒有此文章討論的增強功能,Always Encrypted 將透過在用戶端將資料加密來保護資料,並且絕不允許資料或對應的密碼編譯金鑰以純文字形式出現在 SQL Server 引擎內部。Without the enhancements discussed in this article, Always Encrypted protects the data by encrypting it on the client side and never allowing the data or the corresponding cryptographic keys to appear in plaintext inside the SQL Server Engine. 因此,資料庫內部的加密資料行功能受到嚴格限制。As a result, the functionality on encrypted columns inside the database is severely restricted. SQL Server 可對加密資料執行相的唯一作業是相等比較 (而且僅適用於確定性加密)。The only operations SQL Server can perform on encrypted data are equality comparisons (only available with deterministic encryption). 資料庫內部不支援所有其他作業,包括密碼編譯作業 (初始資料加密或金鑰輪替) 及/或豐富計算 (例如,模式比對)。All other operations, including cryptographic operations (initial data encryption or key rotation) and or rich computations (for example, pattern matching) are not supported inside the database. 使用者需要將資料移出資料庫以在用戶端執行這些作業。Users need to move their data outside of the database to perform these operations on the client-side.

具有安全記憶體保護區的 Always Encrypted 允許在伺服器端的安全記憶體保護區內部進行純文字資料計算,藉以解決這些限制。Always Encrypted with secure enclaves addresses these limitations by allowing computations on plaintext data inside a secure enclave on the server side. 安全記憶體保護區是 SQL Server 處理序內受保護的記憶體區域,並可作為受信任的執行環境來處理 SQL Server 引擎內部的敏感性資料。A secure enclave is a protected region of memory within the SQL Server process, and acts as a trusted execution environment for processing sensitive data inside the SQL Server engine. 安全記憶體保護區對於主控電腦上其餘部分的 SQL Server 和其他處理序會顯示為黑盒子。A secure enclave appears as a black box to the rest of the SQL Server and other processes on the hosting machine. 即使使用偵錯工具,也沒有辦法從外部檢視記憶體保護區內部的任何資料或程式碼。There is no way to view any data or code inside the enclave from the outside, even with a debugger.

Always Encrypted 會使用安全記憶體保護區,如下圖所示:Always Encrypted uses secure enclaves as illustrated in the following diagram:


剖析時應用程式的查詢時,SQL Server 引擎會判斷查詢是否包含任何需要使用安全記憶體保護區的加密資料作業。When parsing an application's query, the SQL Server Engine determines if the query contains any operations on encrypted data that require the use of the secure enclave. 針對需要存取安全記憶體保護區的查詢:For queries where the secure enclave needs to be accessed:

  • 用戶端驅動程式會將作業所需的資料行加密金鑰傳送至安全記憶體保護區 (透過安全通道)。The client driver sends the column encryption keys required for the operations to the secure enclave (over a secure channel).
  • 接著,用戶端驅動程式會送出要執行的查詢,以及加密的查詢參數。Then, the client driver submits the query for execution along with the encrypted query parameters.

在查詢處理期間,資料或資料行加密金鑰不會以純文字形式公開在安全記憶體保護區外的 SQL Server 引擎中。During query processing, the data or the column encryption keys are not exposed in plaintext in the SQL Server Engine outside of the secure enclave. SQL Server 引擎會將密碼編譯作業和加密資料行計算委派給安全記憶體保護區。The SQL Server Engine delegates cryptographic operations and computations on encrypted columns to the secure enclave. 若有需要,安全記憶體保護區會解密查詢參數及/或加密資料行中儲存的資料,並執行所要求的作業。If needed, the secure enclave decrypts the query parameters and/or the data stored in encrypted columns and performs the requested operations.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,具有安全記憶體保護區的 Always Encrypted 會使用虛擬式安全性 (VBS) (英文) 來保護 Windows 中的記憶體保護區 (也稱為虛擬安全模式或 VSM 記憶體保護區)。In SQL Server 2019 (15.x)SQL Server 2019 (15.x), Always Encrypted with secure enclaves uses Virtualization-based Security (VBS) secure memory enclaves (also known as Virtual Secure Mode, or VSM enclaves) in Windows.

為何要使用具有安全記憶體保護區的 Always Encrypted?Why use Always Encrypted with secure enclaves?

運用安全記憶體保護區,Always Encrypted 可保護敏感性資料的機密性,同時提供下列好處:With secure enclaves, Always Encrypted protects the confidentiality of sensitive data while providing the following benefits:

  • 就地加密 - 敏感性資料的密碼編譯作業 (例如:初始資料加密或輪替資料行加密金鑰) 會在安全記憶體保護區內執行,而不需要將資料移出資料庫。In-place encryption - cryptographic operations on sensitive data, for example: initial data encryption or rotating a column encryption key, are performed inside the secure enclave and do not require moving the data outside of the database. 您可以使用 ALTER TABLE Transact-SQL 陳述式來發出就地加密,而不需要使用 SSMS 中的 [Always Encrypted 精靈] 或 Set-SqlColumnEncryption PowerShell Cmdlet 等工具。You can issue in-place encryption using the ALTER TABLE Transact-SQL statement, and you do not need to use tools, such as the Always Encrypted wizard in SSMS or the Set-SqlColumnEncryption PowerShell cmdlet.

  • 豐富計算 (預覽) - 安全記憶體保護區內支援加密資料行作業,包括模式比對 (LIKE 述詞) 及範圍比較,這可為需要在資料庫系統內執行這類計算的各種應用程式和案例解除鎖定 Always Encrypted 功能。Rich computations (preview) - operations on encrypted columns, including pattern matching (the LIKE predicate) and range comparisons, are supported inside the secure enclave, which unlocks Always Encrypted to a broad range of applications and scenarios that require such computations to be performed inside the database system.

安全記憶體保護區證明Secure Enclave Attestation

SQL Server 引擎內部的安全記憶體保護區能夠以純文字形式存取加密資料庫資料行中儲存的敏感性資料與對應的資料行加密金鑰。The secure enclave inside the SQL Server Engine can access sensitive data stored in encrypted database columns and the corresponding column encryption keys in plaintext. 將涉及記憶體保護區計算的查詢提交給 SQL Server 之前,應用程式內部的用戶端驅動程式必須確認安全記憶體保護區是根據指定技術 (例如 VBS) 的真正記憶體保護區,而且已簽署在記憶體保護區內部執行的程式碼,以便在記憶體保護區內部執行。Before submitting a query that involves enclave computations to SQL Server, the client driver inside the application must verify the secure enclave is a genuine enclave based on a given technology (for example, VBS) and the code running inside the enclave has been signed for running inside the enclave.

驗證記憶體保護區的處理序稱為記憶體保護區證明,它會同時涉及應用程式內的用戶端驅動程式,以及與外部證明服務連絡的 SQL Server。The process of verifying the enclave is called enclave attestation, and it involves both a client driver within the application and SQL Server contacting an external attestation service. 證明處理序的細節取決於記憶體保護區技術和證明服務。The specifics of the attestation process depend on the enclave technology and the attestation service.

SQL Server 在 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中支援 VBS 安全記憶體保護區的證明處理序是 Windows Defender 系統防護執行階段證明,它會使用主機守護者服務 (HGS) 作為證明服務。The attestation process SQL Server supports for VBS secure enclaves in SQL Server 2019 (15.x)SQL Server 2019 (15.x) is Windows Defender System Guard runtime attestation, which uses Host Guardian Service (HGS) as an attestation service. 您需要在環境中設定 HGS,並在 HGS 中註冊裝載 SQL Server 執行個體的電腦。You need to configure HGS in your environment and register the machine hosting your SQL Server instance in HGS. 您還必須使用 HGS 證明來設定用戶端應用程式或工具 (例如 SQL Server Management Studio)。You also must configure you client applications or tools (for example, SQL Server Management Studio) with an HGS attestation.

支援的用戶端驅動程式Supported Client Drivers

若要使用具有安全記憶體保護區的 Always Encrypted,應用程式必須使用支援此功能的用戶端驅動程式。To use Always Encrypted with secure enclaves, an application must use a client driver that supports the feature. 您必須設定應用程式和用戶端驅動程式,以啟用記憶體保護區計算和記憶體保護區證明。You need to configure the application and the client driver to enable enclave computations and enclave attestation. 如需詳細資訊 (包括支援的用戶端驅動程式清單),請參閱具有安全記憶體保護區的 Always EncryptedFor details, including the list of supported client drivers, see Always Encrypted with secure enclaves.

已啟用記憶體保護區的金鑰Enclave-enabled Keys

具有安全記憶體保護區的 Always Encrypted 引入了已啟用記憶體保護區的金鑰概念:Always Encrypted with secure enclaves introduces the concept of enclave-enabled keys:

  • 已啟用記憶體保護區的資料行主要金鑰 - 在資料庫內部資料行主要金鑰中繼資料物件中已指定 ENCLAVE_COMPUTATIONS 屬性的資料行主要金鑰。Enclave-enabled column master key - a column master key that has the ENCLAVE_COMPUTATIONS property specified in the column master key metadata object inside the database. 資料行主要金鑰中繼資料物件也必須包含中繼資料屬性的有效簽章。The column master key metadata object must also contain a valid signature of the metadata properties.
  • 已啟用記憶體保護區的資料行加密金鑰 - 以已啟用記憶體保護區的資料行主要金鑰進行加密的資料行加密金鑰。Enclave-enabled column encryption key - a column encryption key that is encrypted with an enclave-enabled column master key.

當 SQL Server 引擎判斷查詢中指定的作業需要在安全記憶體保護區內部執行時,SQL Server 引擎會要求用戶端驅動程式共用使用安全記憶體保護區進行計算所需的資料行加密金鑰。When the SQL Server Engine determines operations, specified in a query, need to be performed inside the secure enclave, the SQL Server Engine requests the client driver shares the column encryption keys that are needed for the computations with the secure enclave. 只有在金鑰已啟用記憶體保護區 (意即以已啟用記憶體保護區的資料行主要金鑰進行加密),並已正確簽署時,用戶端驅動程式才會共用資料行加密金鑰。The client driver shares the column encryption keys only if the keys are enclave-enabled (that is, encrypted with enclave-enabled column master keys) and they're properly signed. 否則,此查詢會失敗。Otherwise, the query fails.

如需詳細資訊,請參閱為具有安全記憶體保護區的 Always Encrypted 管理金鑰For more information, see Manage keys for Always Encrypted with secure enclaves.

已啟用記憶體保護區的資料行Enclave-enabled Columns

已啟用記憶體保護區的資料行是以已啟用記憶體保護區資料行加密金鑰進行加密的資料庫資料行。An enclave-enabled column is a database column encrypted with an enclave-enabled column encryption key. 已啟用記憶體保護區資料行可使用的功能取決於資料行所使用加密類型。The functionality available for an enclave-enabled column depends on the encryption type the column is using.

  • 確定性加密 - 已啟用記憶體保護區的資料行若使用確定性加密,就能在安全記憶體保護區內部支援就地加密,但不支援任何其他作業。Deterministic encryption - Enclave-enabled columns using deterministic encryption support in-place encryption, but no other operations inside the secure enclave. 相等比較雖然受到支援,但它會透過在記憶體保護區外部比較加密文字來執行。Equality comparison is supported, but it is performed by comparing the ciphertext outside of the enclave.
  • 隨機化加密 - 已啟用記憶體保護區的資料行若使用隨機化加密,就能在安全記憶體保護區內部支援就地加密及豐富計算。Randomized encryption - Enclave-enabled columns using randomized encryption support in-place encryption as well as rich computations inside the secure enclave. 支援的豐富計算是模式比對和比較運算子,包括相等比較。The supported rich computations are pattern matching and comparison operators, including equality comparison.

如需加密類型的詳細資訊,請參閱 Always Encrypted 密碼編譯For more information about encryption types, see Always Encrypted Cryptography.

下表摘要說明可用於加密資料行的功能,這取決於資料行是否使用已啟用記憶體保護區的資料行加密金鑰和加密類型。The following table summarizes the functionality available for encrypted columns, depending on whether the columns use enclave-enabled column encryption keys and an encryption type.

運算Operation 資料行不是已啟用記憶體保護區的資料行Column is NOT enclave-enabled 資料行不是已啟用記憶體保護區的資料行Column is NOT enclave-enabled 資料行是已啟用記憶體保護區的資料行Column is enclave-enabled 資料行是已啟用記憶體保護區的資料行Column is enclave-enabled
隨機化加密Randomized encryption 確定性加密Deterministic encryption 隨機化加密Randomized encryption 確定性加密Deterministic encryption
就地加密In-place encryption 不支援Not Supported 不支援Not Supported 支援Supported 支援Supported
相等比較Equality comparison 不支援Not Supported 支援記憶體保護區外部Supported outside of the enclave 支援 (記憶體保護區內部)Supported (inside the enclave) 支援記憶體保護區外部Supported outside of the enclave
超出相等的比較運算子Comparison operators beyond equality 不支援Not Supported 不支援Not Supported 支援Supported 不支援Not Supported
LIKELIKE 不支援Not Supported 不支援Not Supported 支援Supported 不支援Not Supported

就地加密包含對下列記憶體保護區內部作業的支援:In-place encryption includes support for the following operations inside the enclave:

  • 初始加密現有資料行中儲存的資料。Initial encryption of data stored in an existing column.

  • 重新加密資料行中的現有資料,例如:Re-encrypting existing data in a column, for example:

    • 輪替資料行加密金鑰 (使用新的金鑰重新加密資料行)。Rotating the column encryption key (re-encrypting the column with a new key).
    • 變更加密類型。Changing the encryption type.
  • 解密加密資料行中儲存的資料 (將資料行轉換成純文字資料行)。Decrypting data stored in an encrypted column (converting the column into a plaintext column).

為了能夠進行就地加密,密碼編譯作業所涉及的資料行加密金鑰必須是已啟用記憶體保護區的金鑰:For in-place encryption to be possible, the column encryption key (or keys), involved in the cryptographic operations, must be enclave-enabled:

  • 初始加密:所要加密資料行的資料行加密金鑰必須是已啟用記憶體保護區的金鑰。Initial encryption: the column encryption key for the column being encrypted must be enclave-enabled.
  • 重新加密:目前和目標資料行加密金鑰 (如果不同於目前的金鑰) 都必須是已啟用記憶體保護區的金鑰。Re-encryption: both the current and the target column encryption key (if different than the current key) must be enclave-enabled.
  • 解密:資料行的目前資料行加密金鑰必須為已啟用記憶體保護區。Decryption: the current column encryption key of the column must be enclave-enabled.

使用隨機加密啟用記憶體保護區資料行上的索引Indexes on Enclave-enabled Columns using Randomized Encryption

您可以在使用隨機加密啟用記憶體保護區的資料行上建立非叢集索引,使豐富查詢速度更快。You can create nonclustered indexes on enclave-enabled columns using randomized encryption to make rich queries run faster. 為了確保使用隨機加密進行加密資料行上的索引不會外洩敏感性資料,並且在同時確保能夠針對在記憶體保護區內部處理的查詢發揮用途,索引資料結構 (B 型樹狀結構) 中的索引鍵值會進行加密,並根據其純文字的值排序。To ensure an index on a column encrypted using randomized encryption doesn't leak sensitive data and, at the same time, it's useful for processing queries inside the enclave, the key values in the index data structure (B-tree) are encrypted and sorted based on their plaintext values. 當 SQL Server 引擎中的查詢執行程式使用加密資料行上的索引,於記憶體保護區內部進行計算時,它會搜尋索引來尋找儲存在資料行中的特定值。When the query executor in the SQL Server Engine uses an index on an encrypted column for computations inside the enclave, it searches the index to look up specific values stored in the column. 每個搜尋都可能會涉及多次比較。Each search may involve multiple comparisons. 查詢執行程式會將每一次的比較委派給記憶體保護區,記憶體保護區則會解密儲存在資料行中的值,以及要比較的加密索引鍵值;它接著會在純文字上執行比較,然後將比較的結果傳回執行程式。The query executor delegates each comparison to the enclave, which decrypts a value stored in the column and the encrypted index key value to be compared, it performs the comparison on plaintext and it returns the result of the comparison to the executor.

目前仍不支援在使用隨機加密,但沒有啟用記憶體保護區的資料行上建立索引。Creating indexes on columns that use randomized encryption and are not enclave-enabled remains unsupported.

如需詳細資訊,請參閱使用具有安全記憶體保護區的 Always Encrypted 在資料行上建立及使用索引For more information, see Create and use indexes on columns using Always Encrypted with secure enclaves. 如需 SQL Server 中編製索引方式的一般資訊 (而非 Always Encrypted 特定的資訊),請參閱叢集與非叢集索引說明For general information, not specific to Always Encrypted, on how indexing in SQL Server works, see Clustered and Nonclustered Indexes Described.

資料庫復原Database Recovery

若 SQL Server 的執行個體失敗,其資料庫可能會處於資料檔案仍包含交易中修改項目未完成的狀態。If an instance of SQL Server fails, its databases may be left in a state where the data files may contain some modifications from incomplete transactions. 執行個體啟動時,它會執行稱為資料庫復原的處理序,該處理序會涉及復原交易記錄中所找到的每個未完成交易,確保資料庫的完整性能獲得保留。When the instance is started, it runs a process called database recovery, which involves rolling back every incomplete transaction found in the transaction log to make sure the integrity of the database is preserved. 若未完成的交易更動了索引,那些變更也必須要復原。If an incomplete transaction made any changes to an index, those changes also need to be undone. 例如,索引中的某些索引鍵值可能需要移除或重新插入。For example, some key values in the index may need to be removed or reinserted.


Microsoft 強烈建議先為您的資料庫啟用高速資料庫復原 (ADR)使用隨機加密進行加密,以在啟用記憶體保護區的資料行上建立第一個索引。Microsoft strongly recommends enabling Accelerated database recovery (ADR) for your database, before creating the first index on an enclave-enabled column encrypted with randomized encryption.

若透過傳統式資料庫復原處理序 (遵循 ARIES) 復原對索引進行的變更,SQL Server 必須等待應用程式將資料行的資料行加密金鑰提供給記憶體保護區,這可能會花費很長的時間。With the traditional database recovery process (that follows the ARIES recovery model), to undo a change to an index, SQL Server needs to wait until an application provides the column encryption key for the column to the enclave, which can take a long time. ADR 可大幅減少因為無法在記憶體保護區內的快取中取得資料行加密金鑰,而需延遲的復原作業數。ADR dramatically reduces the number of undo operations that must be deferred because a column encryption key is not available in the cache inside the enclave. 因此,它可以透過將封鎖新交易的機會降至最低,來大幅增加資料庫的可用性。Consequently, it substantially increases the database availability by minimizing a chance for a new transaction to get blocked. 啟用 ADR 後,雖然 SQL Server 仍需要資料行加密金鑰來完成清理舊的資料版本,但它會以背景工作的形式進行,不會影響資料庫的可用性或使用者交易。With ADR enabled, SQL Server still may need a column encryption key to complete cleaning up old data versions but it does that as a background task that does not impact the availability of the database or user transactions. 但是,您仍然可能會在錯誤記錄中看到錯誤訊息,指出因缺少資料行加密金鑰而無法完成清理作業。You may, however, see error messages in the error log, indicating failed cleanup operations due to a missing column encryption key.

使用決定性加密啟用記憶體保護區資料行上的索引Indexes on Enclave-enabled Columns using Deterministic Encryption

使用決定性加密資料行上的索引會根據加密文字 (而非純文字) 排序,無論資料行是否已啟用記憶體保護區。An index on a column using deterministic encryption are sorted based on ciphertext (not plaintext), regardless if the column is enclave-enabled or not.

安全性考量Security Considerations

下列安全性考量事項適用於具備安全記憶體保護區的 Always Encrypted。The following security considerations apply to Always Encrypted with secure enclaves.

  • 您記憶體保護區中資料的安全性取決於證明通訊協定和證明服務。The security of your data inside the enclave depends on an attestation protocol and an attestation service. 因此,您必須確保證明服務及證明服務強制執行的證明原則,都是由信任的管理員管理。Therefore, you need to ensure the attestation service and attestation policies, the attestation service enforces, are managed by a trusted administrator. 此外,證明服務通常支援不同的原則和證明通訊協定,其中有些只會執行最小的記憶體保護區及環境驗證,並且旨在用於測試及開發。Also, attestation services typically support different policies and attestation protocols, some of which perform minimal verification of the enclave and its environment, and are designed for testing and development. 請仔細遵循您證明服務的指導方針,確保您針對生產部署使用建議的設定和原則。Closely follow the guidelines specific to your attestation service to ensure you are using the recommended configurations and policies for your production deployments.
  • 搭配啟用記憶體保護區的 CEK 使用隨機加密來加密資料行,可能會外洩儲存在資料行中資料的順序,因為這類資料行支援範圍比較。Encrypting a column using randomized encryption with an enclave-enabled CEK may result in leaking the order of data stored in the column, as such columns support range comparisons. 例如,若包含員工薪資的加密資料行中具備索引,惡意 VBA 便可以掃描索引來尋找最大的加密薪資值,並識別薪資最高的人員 (假設人員的名稱未加密的話)。For example, if an encrypted column, containing employee salaries, has an index, a malicious DBA could scan the index to find the maximum encrypted salary value and identify a person with the maximum salary (assuming the name of the person is not encrypted).
  • 若您使用 Always Encrypted 來保護敏感性資料,使其不受未獲授權的 DBA 存取,請不要與 DBA 共用資料行主要金鑰或資料行加密金鑰。If you use Always Encrypted to protect sensitive data from unauthorized access by DBAs, do not share the column master keys or column encryption keys with the DBAs. DBA 可以透過利用記憶體保護區內的資料行加密金鑰快取,在無須直接存取金鑰的情況下管理加密資料行上的索引。A DBA can manage indexes on encrypted columns without having direct access to the keys, by leveraging the cache of column encryption keys inside the enclave.

可用性群組和資料庫移轉的考量事項Considerations for Availability Groups and Database Migration

設定支援使用記憶體保護區進行查詢所需要的 Always On 可用性群組時,您需要確保所有裝載可用性群組中資料庫的 SQL Server 執行個體都支援使用安全記憶體保護區的 Always Encrypted 功能,並已設定記憶體保護區。When configuring an Always On availability group that is required to support queries using enclaves, you need to ensure that all SQL Server instances hosting the databases in the availability group support Always Encrypted with secure enclaves and have an enclave configured. 若主要資料庫支援記憶體保護區,但次要複本不支援,任何嘗試搭配安全記憶體保護區使用 Always Encrypted 功能的查詢都會失敗。If the primary database supports enclaves, but a secondary replica does not, any query that attempts to use the functionality of Always Encrypted with secure enclaves will fail.

當您還原搭配安全記憶體保護區使用 Always Encrypted 功能的資料庫備份檔案,而該 SQL Server 執行個體並未設定記憶體保護區時,還原作業將會成功,並且所有不依賴記憶體保護區的功能都會開放使用。When you restore a backup file of a database that uses the functionality of Always Encrypted with secure enclaves on a SQL Server instance that doesn't have the enclave configured, the restore operation will succeed and all the functionality that doesn't rely on the enclave will be available. 但是,後續任何使用記憶體保護區功能的查詢都會失敗,且使用隨機加密啟用記憶體保護區資料行上的索引都會失效。However, any subsequent queries using the enclave functionality will fail, and indexes on enclave-enabled columns using randomized encryption will become invalid. 相同的情況也會在您將搭配安全記憶體保護區使用 Always Encrypted 的資料庫附加到並未設定記憶體保護區的執行個體上發生。The same applies when you attach a database using Always Encrypted with secure enclaves on the instance that doesn't have the enclave configured.

若您資料庫包含使用隨機加密啟用記憶體保護區資料行上的索引,請務必在資料庫中先啟用高速資料庫復原 (ADR),再建立資料庫備份。If your database contains indexes on enclave-enabled columns using randomized encryption, make sure you enable Accelerated database recovery (ADR) in the database before creating a database backup. ADR 將會確保資料庫 (包含索引) 在還原資料庫後立即開放使用。ADR will ensure the database, including the indexes, is available immediately after you restore the database. 如需詳細資訊,請參閱資料庫復原For more information, see Database Recovery.

當您使用 bacpac 檔案遷移資料庫時,您需要確保在建立 bacpac 檔案前,您已卸除所有使用隨機加密啟用索引記憶體保護區的資料行。When you migrate your database using a bacpac file, you need to make sure you drop all indexes enclave-enabled columns using randomized encryption before creating the bacpac file.

已知限制Known Limitations

具有安全記憶體保護區的 Always Encrypted 會藉由啟用下列作業來解決 Always Encrypted 的一些限制:Always Encrypted with secure enclaves addresses some limitations of Always Encrypted, by enabling the following operations:

  • 就地密碼編譯作業。In-place cryptographic operations.
  • 在使用隨機加密進行加密的資料行上使用模式比對 (LIKE) 和比較運作子。Pattern matching (LIKE) and comparison operators on column encrypted using randomized encryption.


    目前針對使用 binary2 排序次序 (BIN2 定序) 來定序的字元字串資料行支援以上作業。The above operations are supported for character string columns that use collations with a binary2 sort order (BIN2 collations). 使用非 BIN2 定序的字元字串資料行,可使用隨機加密和啟用記憶體保護區的資料行加密金鑰來進行加密。Character string columns using non-BIN2 collations can be encrypted using randomized encryption and enclave-enabled column encryption keys. 不過,針對這類資料行所啟用的唯一新功能就是就地加密。However, the only new functionality that is enabled for such columns is in-place encryption.

  • 使用隨機加密在資料行上建立非叢集索引。Creating nonclustered indexes on columns using randomized encryption.

功能詳細資料所列出之 Always Encrypted 的所有其他限制,也適用於具有安全記憶體保護區的 Always Encrypted。All other limitations for Always Encrypted listed at Feature Details also apply to Always Encrypted with secure enclaves.

下列限制僅適用於具備安全記憶體保護區的 Always Encrypted:The following limitations are specific to Always Encrypted with secure enclaves:

  • 無法在使用隨機加密啟用記憶體保護區的資料行上建立叢集索引。Clustered indexes can't be created on enclave-enabled columns using randomized encryption.
  • 使用隨機加密啟用記憶體保護區的資料行不能作為主索引鍵資料行,且無法由外部索引鍵條件約束或唯一索引鍵條件約束參考。Enclave-enabled columns using randomized encryption can't be primary key columns and cannot be referenced by foreign key constraints or unique key constraints.
  • 在已啟用記憶體保護區 (使用隨機化加密) 的資料行上,僅支援巢狀迴圈聯結 (使用索引,如果有的話)。Only nested loop joins (using indexes, if available) are supported on enclave-enabled columns using randomized encryption. 不支援雜湊聯結和合併聯結。Hash joins and merged joins are not supported.
  • 除了變更相同字碼頁中的定序和可 NULL 性外,就地密碼編譯作業無法與資料行中繼資料的任何其它變更合併。In-place cryptographic operations cannot be combined with any other changes of column metadata, except changing a collation within the same code page and nullability. 例如,您無法加密、重新加密或解密資料行,同時在單一 ALTER TABLE/ALTER COLUMN Transact-SQL 陳述式中變更資料行的資料類型。For example, you cannot encrypt, re-encrypt, or decrypt a column AND change a data type of the column in a single ALTER TABLE/ALTER COLUMN Transact-SQL statement. 請使用兩個個別的陳述式。Use two separate statements.
  • 不支援對記憶體內部資料表中的資料行使用已啟用記憶體保護區的金鑰。Using enclave-enabled keys for columns in in-memory tables isn't supported.
  • 定義計算資料行的運算式,無法使用隨機化加密,在具備記憶體保護區功能的資料行上,執行任何計算 (即使是類似 LIKE 及範圍比較等計算皆無法)。Expressions defining computed columns cannot perform any computations on enclave-enabled columns using randomized encryption (even if the computations are LIKE and range comparisons).
  • 在已啟用記憶體保護區 (使用隨機化加密) 的資料行上,LIKE 運算子的參數中不支援逸出字元。Escape characters are not supported in parameters of the LIKE operator on enclave-enabled columns using randomized encryption.
  • 透過使用 LIKE 運算子或具有使用下列任一資料類型 (在加密之後會成為大型物件) 查詢參數的比較運算子所進行的查詢會忽略索引,並執行資料表掃描。Queries with the LIKE operator or a comparison operator that has a query parameter using one of the following data types (that become large objects after encryption) ignore indexes and perform table scans.
    • nchar[n]nvarchar[n],如果 n 大於 3967。nchar[n] and nvarchar[n], if n is greater than 3967.
    • char[n]varchar[n]binary[n]varbinary[n],如果 n 大於 7935。char[n], varchar[n], binary[n], varbinary[n], if n is greater than 7935.
  • 工具限制:Tooling limitations:
    • 若要儲存已啟用記憶體保護區的資料行主要金鑰,唯一支援的金鑰存放區是 Windows 憑證存放區和 Azure Key Vault。The only supported key stores for storing enclave-enabled column master keys are Windows Certificate Store and Azure Key Vault.
    • 不支援匯入/匯出包含已啟用記憶體保護區金鑰的資料庫。Importing/exporting databases containing enclave-enabled keys is not supported.
    • 若要透過 ALTER TABLE/ALTER COLUMN 觸發就地密碼編譯作業,您必須使用 SSMS 中的查詢視窗發出陳述式,也可以撰寫自己的程式來發出陳述式。To trigger an in-place cryptographic operation via ALTER TABLE/ALTER COLUMN, you need to issue the statement using a query window in SSMS, or you can write your own program that issues the statement. 目前,SqlServer PowerShell 模組中的 Set-SqlColumnEncryption Cmdlet 和 SQL Server Management Studio 中的 [Always Encrypted 精靈] 尚未支援就地加密,即使用於作業之資料行加密金鑰是已啟用記憶體保護區的金鑰,但它們會將資料移出資料庫以進行密碼編譯作業。Currently, the Set-SqlColumnEncryption cmdlet in the SqlServer PowerShell module and the Always Encrypted wizard in SQL Server Management Studio do not support in-place encryption - they move the data out of the database for cryptographic operations, even if the column encryption keys used for the operations are enclave-enabled.

後續步驟Next steps

另請參閱See also