Always EncryptedAlways Encrypted

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

一律加密Always Encrypted

Always Encrypted 是一個設計來保護儲存於 Azure SQL DatabaseAzure SQL DatabaseSQL ServerSQL Server 資料庫中之敏感性資料的功能,像是信用卡號碼或全國性的身分證字號 (例如美國社會安全號碼)。Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in Azure SQL DatabaseAzure SQL Database or SQL ServerSQL Server databases. Always Encrypted 可讓用戶端將用戶端應用程式內的敏感性資料進行加密,且絕不會顯示 Database EngineDatabase Engine (SQL DatabaseSQL DatabaseSQL ServerSQL Server) 的加密金鑰。Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database EngineDatabase Engine (SQL DatabaseSQL Database or SQL ServerSQL Server). 如此一來,Always Encrypted 在資料擁有者 (且可以檢視資料) 和資料管理者 (但應該不具備存取權) 之間做出區隔。As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. 透過確定內部部署資料庫系統管理員,雲端資料庫操作員,或其他高權限未經授權的使用者則無法存取加密資料,Always Encrypted 可讓客戶有信心地將機密資料存放在他們無法直接控制的位置。By ensuring on-premises database administrators, cloud database operators, or other high-privileged unauthorized users, can't access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. 這讓組織能夠將其資料儲存在 Azure 中,並將內部部署資料庫管理委派給第三方,或是降低組織本身 DBA 人員的安全性許可需求。This allows organizations to store their data in Azure, and enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Always Encrypted 藉由啟用 Database EngineDatabase Engine 來處理對加密資料的一些查詢,同時保留資料的機密性並提供上述安全性優點,從而提供機密計算功能。Always Encrypted provides confidential computing capabilities by enabling the Database EngineDatabase Engine to process some queries on encrypted data, while preserving the confidentiality of the data and providing the above security benefits. SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL Database 中,Always Encrypted 透過決定性加密支援相等比較。In SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Azure SQL DatabaseAzure SQL Database, Always Encrypted supports equality comparison via deterministic encryption. 請參閱選擇決定性加密或隨機加密See Selecting Deterministic or Randomized Encryption.

注意

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,安全記憶體保護區會利用模式比對、其他比較運算子和就地加密,大幅擴充 Always Encrypted 的機密計算功能。In SQL Server 2019 (15.x)SQL Server 2019 (15.x), secure enclaves substantially extend confidential computing capabilities of Always Encrypted with pattern matching, other comparison operators and in-place encryption. 請參閱具有安全記憶體保護區的 Always EncryptedSee Always Encrypted with secure enclaves.

永遠加密讓應用程式加密變得透明化。Always Encrypted makes encryption transparent to applications. 安裝在用戶端電腦上且啟用永遠加密的驅動程式,透過自動將用戶端應用程式中的機密資料進行加密與解密,進而達成此目的。An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. 驅動程式會先將敏感資料行中的資料進行加密,才會將資料傳遞至 Database EngineDatabase Engine,並自動重寫查詢以保留應用程式的語意。The driver encrypts the data in sensitive columns before passing the data to the Database EngineDatabase Engine, and automatically rewrites queries so that the semantics to the application are preserved. 同樣地,驅動程式會將儲存在加密資料庫資料行並包含在查詢結果中的資料明確解密。Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Always Encrypted 在 Azure SQL DatabaseAzure SQL Database 所有的版本中都有提供,從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL DatabaseSQL Database 的所有服務層級開始。Always Encrypted is available in all editions of Azure SQL DatabaseAzure SQL Database, starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and all service tiers of SQL DatabaseSQL Database. (在 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 之前,Always Encrypted 僅限於 Enterprise Edition 中使用)。如需包含「永遠加密」的 Channel 9 簡報,請參閱 使用 [永遠加密] 保護機密資料安全性(Prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, Always Encrypted was limited to the Enterprise Edition.) For a Channel 9 presentation that includes Always Encrypted, see Keeping Sensitive Data Secure with Always Encrypted.

典型案例Typical Scenarios

用戶端和內部部署資料Client and data on-premises

客戶的用戶端應用程式和 SQL ServerSQL Server 皆在其業務位置的內部部署執行。A customer has a client application and SQL ServerSQL Server both running on-premises, at their business location. 而客戶希望聘雇外部廠商來管理 SQL ServerSQL ServerThe customer wants to hire an external vendor to administer SQL ServerSQL Server. 為了保護儲存於 SQL ServerSQL Server中的機密資料,客戶使用了 [永遠加密] 確保資料庫系統管理員和應用程式系統管理員之間的責任有所區隔。In order to protect sensitive data stored in SQL ServerSQL Server, the customer uses Always Encrypted to ensure the separation of duties between database administrators and application administrators. 客戶可將 Always Encrypted 金鑰的純文字值儲存在用戶端應用程式可以存取的信任的金鑰存放區中。The customer stores plaintext values of Always Encrypted keys in a trusted key store, which the client application can access. SQL ServerSQL Server 系統管理員不具備金鑰的存取權,因此無法解密儲存於 SQL ServerSQL Server中的機密資料。administrators have no access to the keys and, therefore, are unable to decrypt sensitive data stored in SQL ServerSQL Server.

內部部署用戶端與 Azure 中的資料Client on-premises with data in Azure

客戶在其業務位置擁有內部部署的用戶端應用程式。A customer has an on-premises client application at their business location. 該應用程式會處理儲存於 Azure 託管之資料庫 (SQL DatabaseSQL Database 或在 Microsoft Azure 虛擬機器中執行的 SQL ServerSQL Server ) 中的敏感性資料。The application operates on sensitive data stored in a database hosted in Azure (SQL DatabaseSQL Database or SQL ServerSQL Server running in a virtual machine on Microsoft Azure). 客戶可使用 [永遠加密] 並將 Always Encrypted 金鑰儲存在內部部署託管之受信任的金鑰存放區中,以確保 MicrosoftMicrosoft 雲端系統管理員無法存取敏感性資料。The customer uses Always Encrypted and stores Always Encrypted keys in a trusted key store hosted on-premises, to ensure MicrosoftMicrosoft cloud administrators have no access to sensitive data.

用戶端和 Azure 中的資料Client and Data in Azure

客戶的用戶端應用程式由 Microsoft Azure 託管 (例如,背景工作角色或 Web 角色),該應用程式也會處理儲存於 Azure 託管之資料庫 (SQL Database 或在 Microsoft Azure 虛擬機器中執行的 SQL Server) 中的敏感性資料。A customer has a client application, hosted in Microsoft Azure (for example, in a worker role or a web role), which operates on sensitive data stored in a database hosted in Azure (SQL Database or SQL Server running in a virtual machine on Microsoft Azure). 雖然 Always Encrypted 並未讓資料與雲端系統管理員完全隔離 (亦即託管用戶端層之平台的雲端系統管理員仍可看到資料和金鑰),客戶仍具有降低安全性攻擊面 (資料庫中一律會加密資料) 的優勢。Although Always Encrypted doesn't provide complete isolation of data from cloud administrators, as both the data and keys are exposed to cloud administrators of the platform hosting the client tier, the customer still benefits from reducing the security attack surface area (the data is always encrypted in the database).

運作方式How it Works

您可以針對包含敏感性資料的個別資料庫資料行設定 [永遠加密]。You can configure Always Encrypted for individual database columns containing your sensitive data. 設定資料行加密時,可指定加密演算法和密碼編譯金鑰的相關資訊,以用來保護資料行中的資料。When setting up encryption for a column, you specify the information about the encryption algorithm and cryptographic keys used to protect the data in the column. [永遠加密] 會使用兩種類型的金鑰:資料行加密金鑰和資料行主要金鑰。Always Encrypted uses two types of keys: column encryption keys and column master keys. 資料行加密金鑰用來加密已加密資料行中的資料。A column encryption key is used to encrypt data in an encrypted column. 資料行主要金鑰可為一或多個資料行加密金鑰進行加密,針對金鑰提供雙重保護。A column master key is a key-protecting key that encrypts one or more column encryption keys.

Database Engine 會將每個資料行的加密設定儲存在資料庫中繼資料中。The Database Engine stores encryption configuration for each column in database metadata. 但請注意,Database Engine 絕不會以純文字的任何類型儲存或使用金鑰。Note, however, the Database Engine never stores or uses the keys of either type in plaintext. 它只會將資料行加密金鑰的加密值以及資料行主要金鑰位置的資訊儲存在外部受信任的金鑰存放區,例如 Azure 金鑰保存庫、在用戶端電腦的 Windows 憑證存放區或硬體安全模組。It only stores encrypted values of column encryption keys and the information about the location of column master keys, which are stored in external trusted key stores, such as Azure Key Vault, Windows Certificate Store on a client machine, or a hardware security module.

若要存取儲存在加密資料行的純文字資料,應用程式必須使用支援 [永遠加密] 的用戶端驅動程式。To access data stored in an encrypted column in plaintext, an application must use an Always Encrypted enabled client driver. 當應用程式發出參數化查詢時,驅動程式會明確地與 Database Engine 共同作業並決定哪些參數是以加密的資料行為目標,因此應該受到加密。When an application issues a parameterized query, the driver transparently collaborates with the Database Engine to determine which parameters target encrypted columns and, thus, should be encrypted. 驅動程式會針對需要加密的每一個參數,取得資料行的加密演算法以及資料行加密金鑰加密值的相關資訊、參數目標,以及其對應的資料行主要金鑰位置。For each parameter that needs to be encrypted, the driver obtains the information about the encryption algorithm and the encrypted value of the column encryption key for the column, the parameter targets, as well as the location of its corresponding column master key.

接下來,驅動程式會連絡內含資料行主要金鑰的金鑰存放區,以將加密的資料行加密金鑰值解密,再使用純文字資料行加密金鑰來加密參數。Next, the driver contacts the key store, containing the column master key, in order to decrypt the encrypted column encryption key value and then, it uses the plaintext column encryption key to encrypt the parameter. 系統會快取產生的純文字資料行加密金鑰,以減少後續使用相同資料行加密金鑰的金鑰存放區來回行程。The resultant plaintext column encryption key is cached to reduce the number of round trips to the key store on subsequent uses of the same column encryption key. 驅動程式會將以加密資料行為目標之參數的純文字值取代為其加密的值,再將查詢傳送至伺服器進行處理。The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the server for processing.

伺服器會計算結果集,並針對結果集中包含的任何加密資料行,附加資料行的加密中繼資料,包括加密演算法和對應金鑰的相關資訊。The server computes the result set, and for any encrypted columns included in the result set, the driver attaches the encryption metadata for the column, including the information about the encryption algorithm and the corresponding keys. 驅動程式會先嘗試在本機快取中尋找純文字資料行加密金鑰;如果快取中找不到金鑰,它只會在資料行主要金鑰中來回一次。The driver first tries to find the plaintext column encryption key in the local cache, and only makes a round to the column master key if it can't find the key in the cache. 接下來,驅動程式會解密結果,並將純文字值傳給應用程式。Next, the driver decrypts the results and returns plaintext values to the application.

用戶端驅動程式會使用資料行主要金鑰存放區提供者,來與內含資料行主要金鑰的金鑰存放區互動;該提供者為一種用戶端軟體元件,可封裝含有資料行主要金鑰的金鑰存放區。A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key. Microsoft 的用戶端驅動程式程式庫有提供常見的金鑰存放區提供者,或是作為獨立下載項目提供。Providers for common types of key stores are available in client-side driver libraries from Microsoft or as standalone downloads. 您也可以實作自己的提供者。You can also implement your own provider. Always Encrypted 功能與內建的資料行主要金鑰存放區提供者,會因驅動程式程式庫和其版本而異。Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.

如需如何使用特定用戶端驅動程式與 Always Encrypted 來開發應用程式的詳細資訊,請參閱使用 Always Encrypted 開發應用程式For details of how to develop applications using Always Encrypted with particular client drivers, see Develop applications using Always Encrypted.

備註Remarks

加密和解密會透過用戶端驅動程式發生。Encryption and decryption occurs via the client driver. 這表示使用 Always Encrypted 時,僅出現在伺服器端的某些動作將無法運作。This means that some actions that occur only server-side will not work when using Always Encrypted. 範例包括透過 UPDATE、BULK INSERT(T-SQL)、SELECT INTO、INSERT SELECT 將某個資料行的資料複製到另一個資料行。Examples include copying data from one columng to another via an UPDATE, BULK INSERT(T-SQL), SELECT INTO, INSERT..SELECT.

下列 UPDATE 範例會嘗試將資料從加密的資料行移至未加密的資料行,而不傳回結果集給用戶端:Here's an example of an UPDATE that attempts to move data from an encrypted column to an unencrypted column without returning a result set to the client:

update dbo.Patients set testssn = SSN

如果 SSN 是使用 Always Encrypted 加密的資料行,則上述 update 陳述式會失敗並出現類似以下的錯誤:If SSN is a column encrypted using Always Encrypted, the above update statement will fail with an error similar to:

Msg 206, Level 16, State 2, Line 89
Operand type clash: char(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_1', column_encryption_key_database_name = 'ssn') collation_name = 'Latin1_General_BIN2' is incompatible with char

若要成功更新資料行,請執行下列動作:To successfully update the column, do the following:

  1. 從 SSN 資料行中選取資料,並將它儲存為應用程式中的結果集。SELECT the data out of the SSN column, and store it as a result set in the application. 這可讓應用程式 (用戶端「驅動程式」 ) 將資料行解密。This will allow for the application (client driver) to decrypt the column.
  2. 將結果集中的資料插入 SQL Server。INSERT the data from the result set into SQL Server.

重要

在此案例中,資料會在傳回伺服器時予以解密,因為目的地資料行是不接受加密資料的一般 varchar。In this scenario, the data will be unencrypted when sent back to the server because the destination column is a regular varchar that does not accept encrypted data.

選擇決定性加密或隨機加密Selecting Deterministic or Randomized Encryption

Database Engine 絕不會處理儲存於加密資料行中的純文字資料,但仍可根據資料行的加密類型,支援某些加密資料的查詢。The Database Engine never operates on plaintext data stored in encrypted columns, but it still supports some queries on encrypted data, depending on the encryption type for the column. [永遠加密] 支援兩種類型的加密:隨機加密和決定性加密。Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption.

  • 確定性加密一律會針對特定的純文字值產生相同的加密值。Deterministic encryption always generates the same encrypted value for any given plain text value. 使用確定性加密時,您能根據加密資料行進行點查閱、相等聯結、分組和編製索引等作業。Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. 但它也可能讓未獲授權使用者透過檢查加密資料行中的模式,來猜出加密值資訊,尤其當其中有一小組可能的加密值時 (例如 True/False 或北/南/東/西區域)。However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there's a small set of possible encrypted values, such as True/False, or North/South/East/West region. 確定性加密必須針對字元資料行使用 binary2 排序次序的資料行定序。Deterministic encryption must use a column collation with a binary2 sort order for character columns.

  • 隨機化加密會使用更難預測的方式來加密資料。Randomized encryption uses a method that encrypts data in a less predictable manner. 隨機化加密雖較安全,但會讓您無法針對加密資料行進行搜尋、分組、編製索引和聯結等作業。Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

針對將做為搜尋或分組參數的資料行,請使用決定性加密。Use deterministic encryption for columns that will be used as search or grouping parameters. 例如,政府識別碼數字。For example, a government ID number. 針對未利用其他記錄且未用來聯結資料表的資料 (例如機密調查註解),請使用隨機加密。Use randomized encryption for data such as confidential investigation comments, which aren't grouped with other records and aren't used to join tables. 如需 Always Encrypted 密碼編譯演算法的詳細資訊,請參閱 Always Encrypted 密碼編譯For details on Always Encrypted cryptographic algorithms, see Always Encrypted cryptography.

設定永遠加密Configuring Always Encrypted

資料庫中的 [永遠加密] 初始設定包括:產生 [永遠加密] 金鑰、建立金鑰中繼資料、設定所選資料庫資料行的加密屬性,及/或針對需要加密之資料行中可能已存在的資料進行加密。The initial setup of Always Encrypted in a database involves generating Always Encrypted keys, creating key metadata, configuring encryption properties of selected database columns, and/or encrypting data that may already exist in columns that need to be encrypted. 請注意,其中有些工作並不支援 Transact-SQL,而需要使用用戶端工具。Please note that some of these tasks are not supported in Transact-SQL and require the use of client-side tools. 由於 Always Encrypted 金鑰和受保護的敏感性資料絕不會以純文字形式顯示給伺服器,因此 Database Engine 無法佈建金鑰和執行資料加密或解密作業。As Always Encrypted keys and protected sensitive data are never revealed in plaintext to the server, the Database Engine can't be involved in key provisioning and perform data encryption or decryption operations. 您可以使用 SQL Server Management Studio 或 PowerShell 來完成這類工作。You can use SQL Server Management Studio or PowerShell to accomplish such tasks.

TaskTask SSMSSSMS PowerShellPowerShell T-SQLT-SQL
搭配相對應的資料行主要金鑰,佈建資料行主要金鑰、資料行加密金鑰和加密的資料行加密金鑰。Provisioning column master keys, column encryption keys and encrypted column encryption keys with their corresponding column master keys. Yes Yes No
在資料庫中建立金鑰中繼資料。Creating key metadata in the database. Yes Yes Yes
建立含加密資料行的新資料表Creating new tables with encrypted columns Yes Yes Yes
加密所選資料庫資料行中的現有資料Encrypting existing data in selected database columns Yes Yes No

注意

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中所引進具有安全記憶體保護區的 Always Encrypted 支援使用 Transact-SQL 來加密現有資料。Always Encrypted with secure enclaves, introduced in SQL Server 2019 (15.x)SQL Server 2019 (15.x), does support encrypting existing data using Transact-SQL. 此外,也不需要將資料移到資料之外,就能進行密碼編譯作業。It also eliminates the need to move the data outside of the data for cryptographic operations.

注意

執行金鑰佈建或資料加密工具時,請務必在裝載資料庫之電腦以外的電腦且安全的環境中進行。Make sure you run key provisioning or data encryption tools in a secure environment, on a computer that is different from the computer hosting your database. 否則,敏感性資料或金鑰可能會洩漏到伺服器環境中,而縮減使用 [永遠加密] 的優點。Otherwise, sensitive data or the keys could leak to the server environment, which would reduce the benefits of the using Always Encrypted.

如需設定 [永遠加密] 的詳細資訊,請參閱︰For details on configuring Always Encrypted see:

開始使用 [永遠加密]Getting Started with Always Encrypted

使用 永遠加密的精靈 來快速開始使用 Always Encrypted。Use the Always Encrypted Wizard to quickly start using Always Encrypted. 精靈將會佈建必要的金鑰,並針對所選的資料行設定加密。The wizard will provision the required keys and configure encryption for selected columns. 如果您要設定加密的資料行已經包含一些資料,則精靈會加密這些資料。If the columns you're setting encryption for already contain some data, the wizard will encrypt the data. 下列範例會示範加密資料行的程序。The following example demonstrates the process for encrypting a column.

注意

如需使用精靈的影片,請參閱 Getting Started with Always Encrypted with SSMS(搭配 SSMS 開始使用永遠加密)。For a video that includes using the wizard, see Getting Started with Always Encrypted with SSMS.

  1. 您可連接到現有的資料庫,其中包含您想要使用 Management Studio 物件總管 加密之資料行的資料表;或者,建立新的資料庫,再以要加密的資料行建立一或多個資料表,然後連接到該資料庫。Connect to an existing database that contains tables with columns you wish to encrypt using the Object Explorer of Management Studio, or create a new database, create one or more tables with columns to encrypt, and connect to it.
  2. 以滑鼠右鍵按一下您的資料庫,指向 [工作] ,然後按一下 [加密資料行] 以開啟 [Always Encrypted 精靈] 。Right-click your database, point to Tasks, and then click Encrypt Columns to open the Always Encrypted Wizard.
  3. 檢閱[簡介] 頁面,然後按一下 [下一步] 。Review the Introduction page, and then click Next.
  4. 在 [資料行選取] 頁面上,展開資料表,並選取您想要加密的資料行。On the Column Selection page, expand the tables, and select the columns that you want to encrypt.
  5. 針對每個已選取要進行加密的資料行,將 [加密類型] 設定為 [決定性] 或 [隨機化] 。For each column selected for encryption, set the Encryption Type to either Deterministic or Randomized.
  6. 針對每個已選取要進行加密的資料行,選取 [加密金鑰] 。For each column selected for encryption, select an Encryption Key. 如果您之前沒有針對此資料庫建立任何加密金鑰,請選取新的自動產生金鑰的預設選項,然後按一下 [下一步] 。If you have not previously created any encryption keys for this database, select the default choice of a new autogenerated key, and then click Next.
  7. 在 [主要金鑰組態] 頁面上,選取要儲存新金鑰的位置,並選取主要金鑰來源,然後按一下 [下一步] 。On the Master Key Configuration page, select a location to store the new key, and select a master key source, and then click Next.
  8. 在 [驗證] 頁面上,選擇是否要立即執行指令碼或建立 PowerShell 指令碼,然後按一下 [下一步] 。On the Validation page, choose whether to run the script immediately or create a PowerShell script, and then click Next.
  9. 在 [摘要] 頁面上,檢閱您已選取的選項,然後按一下 [完成] 。On the Summary page, review the options you've selected, and then click Finish. 完成時請關閉精靈。Close the wizard when completed.

功能詳細資料Feature Details

  • 查詢可以在使用決定性加密進行加密的資料行上執行相等比較,但無法在其他的作業上執行 (例如大於/小於、使用 LIKE 運算子的模式比對或算術運算)。Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

  • 在使用隨機加密進行加密的資料行上的查詢,無法在任何這些資料行上執行作業。Queries on columns encrypted by using randomized encryption can't perform operations on any of those columns. 不支援建立使用隨機加密進行加密之資料行的索引。Indexing columns encrypted using randomized encryption isn't supported.

注意

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中引進的具有安全記憶體保護區的 Always Encrypted,可以藉由啟用模式比對、比較運算子,以及使用隨機加密來對資料行編製索引,來解決上述限制。Always Encrypted with secure enclaves, introduced in SQL Server 2019 (15.x)SQL Server 2019 (15.x), addresses the above limitation by enabling pattern matching, comparison operators and indexing on columns using randomized encryption.

  • 資料行加密金鑰最多可以有兩個不同的加密值,每個都使用不同的資料行主要金鑰進行加密。A column encryption key can have up to two different encrypted values, each encrypted with a different column master key. 這有助於資料行主要金鑰輪替。This facilitates column master key rotation.

  • 決定性加密要求資料行具備其中一個 binary2 定序Deterministic encryption requires a column to have one of the binary2 collations.

  • 變更加密物件的定義之後,執行 sp_refresh_parameter_encryption 更新物件的 Always Encrypted 中繼資料。After changing the definition of an encrypted object, execute sp_refresh_parameter_encryption to update the Always Encrypted metadata for the object.

具有下列特性的資料行不支援 Always Encrypted。Always Encrypted isn't supported for the columns with the below characteristics. 例如,如果下列任何條件適用於資料行,則 ENCRYPTED WITH 子句不能用於資料行的 CREATE TABLE/ALTER TABLE 中:For example, if any of the following conditions apply to the column, the ENCRYPTED WITH clause can't be used in CREATE TABLE/ALTER TABLE for a column:

  • 使用下列其中一種資料類型的資料行︰xmltimestamp/rowversionimagentexttextsql_varianthierarchyidgeographygeometry、別名、使用者定義類型。Columns using one of the following data types: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.
  • FILESTREAM 資料行FILESTREAM columns
  • 屬性為 IDENTITY 的資料行。Columns with the IDENTITY property.
  • 屬性為 ROWGUIDCOL 的資料行。Columns with ROWGUIDCOL property.
  • 包含非 bin2 定序的字串 (varcharchar 等) 資料行。String (varchar, char, etc.) columns with non-bin2 collations.
  • 使用隨機化加密時,叢集和非叢集索引索引鍵的資料行 (支援確定性加密)。Columns that are keys for clustered and nonclustered indices when using randomized encryption (deterministic encryption is supported).
  • 包含在全文檢索索引中的資料行 (Always Encrypted 不支援全文檢索搜尋)。Columns included in full-text indexes (Always Encrypted does not support Full Text Search).
  • 計算資料行。Computed columns.
  • 計算資料行所參考之資料行 (當運算式執行 Always Encrypted 不支援的作業)。Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted).
  • 疏鬆資料行集合。Sparse column set.
  • 使用隨機化加密時,統計資料所參考的資料行 (支援決定性加密)。Columns that are referenced by statistics when using randomized encryption (deterministic encryption is supported).
  • 使用別名資料型別的資料行。Columns using alias types.
  • 資料分割資料行。Partitioning columns.
  • 包含預設條件約束的資料行。Columns with default constraints.
  • 使用隨機加密時,唯一條件約束所參考的資料行 (支援決定性加密)。Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported).
  • 使用隨機加密時的主索引鍵資料行 (支援決定性加密)。Primary key columns when using randomized encryption (deterministic encryption is supported).
  • 當使用隨機加密或使用決定性加密時 (如果已參考和參考資料行使用不同的索引鍵或演算法),外部索引鍵條件約束中的參考資料行。Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms.
  • 檢查條件約束所參考之資料行。Columns referenced by check constraints.
  • 使用異動資料擷取來擷取/追蹤的資料行。Columns captured/tracked using change data capture.
  • 具有變更追蹤之資料表上的主索引鍵資料行。Primary key columns on tables that have change tracking.
  • 已遮罩的資料行 (使用動態資料遮罩)。Columns that are masked (using Dynamic Data Masking).
  • Stretch Database 資料表中的資料行。Columns in Stretch Database tables. (包含使用 [永遠加密] 進行加密之資料行的資料表可針對 Stretch 啟用。)(Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)
  • 外部 (PolyBase) 資料表中的資料行 (附註:支援使用外部資料表和包含加密資料行的資料表)。Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported).
  • 不支援以加密的資料行為目標的資料表值參數。Table-valued parameters targeting encrypted columns aren't supported.

下列子句不能用於加密的資料行:The following clauses can't be used for encrypted columns:

  • FOR XML
  • FOR JSON PATH

下列功能無法在加密的資料行運作:The following features don't work on encrypted columns:

  • 交易式或合併式複寫Transactional or merge replication
  • 分散式查詢 (連結的伺服器,OPENROWSET(T-SQL)、OPENDATASOURCE(T-SQL))Distributed queries (linked servers, OPENROWSET(T-SQL), OPENDATASOURCE(T-SQL))

工具需求Tool Requirements

  • 建議使用 SQL Server Management Studio 18 版或更高版本來執行查詢,以解密從加密資料行取得的結果,或插入、更新或篩選加密的資料行。SQL Server Management Studio version 18 or higher is recommended to run queries that decrypt the results retrieved from encrypted columns or insert, update, or filter encrypted columns.
  • 需要 sqlcmd 13.1 版或更高版本,可從下載中心取得。Requires sqlcmd version 13.1 or higher, which is available from the Download Center.

資料庫權限Database Permissions

永遠加密有下列四個權限:There are four permissions for Always Encrypted:

  • ALTER ANY COLUMN MASTER KEY (需具備才能建立和刪除資料行主要金鑰)。ALTER ANY COLUMN MASTER KEY (Required to create and delete a column master key.)

  • ALTER ANY COLUMN ENCRYPTION KEY (需具備才能建立和刪除資料行加密金鑰)。ALTER ANY COLUMN ENCRYPTION KEY (Required to create and delete a column encryption key.)

  • VIEW ANY COLUMN MASTER KEY DEFINITION (需具備才能存取和讀取資料行主要金鑰的中繼資料,以管理金鑰或查詢加密資料行)。VIEW ANY COLUMN MASTER KEY DEFINITION (Required to access and read the metadata of the column master keys to manage keys or query encrypted columns.)

  • VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (需具備才能存取和讀取資料行加密金鑰的中繼資料,以管理金鑰或查詢加密資料行)。VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns.)

下表摘要說明一般動作所需的權限。The following table summarizes the permissions required for common actions.

狀況Scenario ALTER ANY COLUMN MASTER KEY ALTER ANY COLUMN ENCRYPTION KEY VIEW ANY COLUMN MASTER KEY DEFINITION VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
金鑰管理 (在資料庫中建立/變更/檢閱金鑰中繼資料)Key management (creating/changing/reviewing key metadata in the database) XX XX XX XX
查詢加密資料行Querying encrypted columns XX XX

重要事項:Important notes:

  • 您可使用 Transact-SQLTransact-SQLManagement StudioManagement Studio (對話方塊和精靈) 或 PowerShell 將權限套用至動作。The permissions apply to actions using Transact-SQLTransact-SQL, Management StudioManagement Studio (dialog boxes and wizard), or PowerShell.

  • 選取加密的資料行時需具備這兩種「檢視」 權限 (即使使用者沒有解密資料行的權限亦同)。The two VIEW permissions are required when selecting encrypted columns, even if the user doesn't have permission to decrypt the columns.

  • SQL ServerSQL Server預設會將這兩個「檢視」 權限授與 public 固定資料庫角色。In SQL ServerSQL Server, both VIEW permissions are granted by default to the public fixed database role. 資料庫管理員可以選擇撤銷 (或拒絕) 授與 角色的「檢視」 public 權限,而將其授與特定角色或使用者,以實作更嚴格的控制。A database administrator may choose to revoke (or deny) the VIEW permissions to the public role and grant them to specific roles or users to implement more restricted control.

  • SQL DatabaseSQL Database 中,預設不會將「檢視」 權限授與 public 固定資料庫角色。In SQL DatabaseSQL Database, the VIEW permissions aren't granted by default to the public fixed database role. 這可讓某些現有的舊版工具 (使用舊版 DacFx) 正常運作。This enables certain existing, legacy tools (using older versions of DacFx) to work properly. 因此,若要使用加密的資料行 (即使不要加以解密),資料庫管理員必須明確授與這兩個「檢視」 權限。Consequently, to work with encrypted columns (even if not decrypting them) a database administrator must explicitly grant the two VIEW permissions.

範例Example

下列 Transact-SQLTransact-SQL 會建立資料行主要金鑰中繼資料、資料行加密金鑰中繼資料以及含加密資料行的資料表。The following Transact-SQLTransact-SQL creates column master key metadata, column encryption key metadata, and a table with encrypted columns. 如需如何建立中繼資料參考金鑰的資訊,請參閱︰For information how to create the keys, referenced in the metadata, see:

CREATE COLUMN MASTER KEY MyCMK  
WITH (  
     KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',   
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'  
   );  
---------------------------------------------  
CREATE COLUMN ENCRYPTION KEY MyCEK   
WITH VALUES  
(  
    COLUMN_MASTER_KEY = MyCMK,   
    ALGORITHM = 'RSA_OAEP',   
    ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003200660061006600640038003100320031003400340034006500620031006100320065003000360039003300340038006100350064003400300032003300380065006600620063006300610031006300284FC4316518CF3328A6D9304F65DD2CE387B79D95D077B4156E9ED8683FC0E09FA848275C685373228762B02DF2522AFF6D661782607B4A2275F2F922A5324B392C9D498E4ECFC61B79F0553EE8FB2E5A8635C4DBC0224D5A7F1B136C182DCDE32A00451F1A7AC6B4492067FD0FAC7D3D6F4AB7FC0E86614455DBB2AB37013E0A5B8B5089B180CA36D8B06CDB15E95A7D06E25AACB645D42C85B0B7EA2962BD3080B9A7CDB805C6279FE7DD6941E7EA4C2139E0D4101D8D7891076E70D433A214E82D9030CF1F40C503103075DEEB3D64537D15D244F503C2750CF940B71967F51095BFA51A85D2F764C78704CAB6F015EA87753355367C5C9F66E465C0C66BADEDFDF76FB7E5C21A0D89A2FCCA8595471F8918B1387E055FA0B816E74201CD5C50129D29C015895CD073925B6EA87CAF4A4FAF018C06A3856F5DFB724F42807543F777D82B809232B465D983E6F19DFB572BEA7B61C50154605452A891190FB5A0C4E464862CF5EFAD5E7D91F7D65AA1A78F688E69A1EB098AB42E95C674E234173CD7E0925541AD5AE7CED9A3D12FDFE6EB8EA4F8AAD2629D4F5A18BA3DDCC9CF7F352A892D4BEBDC4A1303F9C683DACD51A237E34B045EBE579A381E26B40DCFBF49EFFA6F65D17F37C6DBA54AA99A65D5573D4EB5BA038E024910A4D36B79A1D4E3C70349DADFF08FD8B4DEE77FDB57F01CB276ED5E676F1EC973154F86  
);  
---------------------------------------------  
CREATE TABLE Customers (  
    CustName nvarchar(60)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    SSN varchar(11)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = DETERMINISTIC ,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    Age int NULL  
);  
GO  
  

另請參閱See Also