始終加密：如何將非加密列與確定性加密列進行相等連接？

SQL Server 2017 上 Always Encrypted的官方 Microsoft 文件指出：

確定性加密總是為任何給定的純文字值生成相同的加密值。

使用確定性加密允許對加密列進行點查找、等式連接、分組和索引。

（粗體強調我的）

我目前正在使用 SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1 標準版。

我的 SSMS（目前使用 v18.4）連接已配置為Enable Always Encrypted (column encryption)選中復選框，並且Enable Parameterization for Always Encrypted還選中了 Query Options -> Execution -> Advanced 設置。

下面是我的表模式。

EmployeeID和列使用FullName加密Deterministic Encryption Type。

該Temp列使用 加密Randomized Encryption Type。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EmployeeTemperature]
(
   [Entry] [int] IDENTITY(1,1) NOT NULL,
   [CheckerID] [varchar](26) NOT NULL,
   [EmployeeID] [char](10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
   [FullName] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
   [Temp] [decimal](4, 1) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
   [Date] [date] NOT NULL, -- to support Date-CheckerID-FullName unique constraint
   [DateTime] [datetime] NOT NULL,
   [Station] [smallint] NOT NULL,
   [Question1] [bit] NOT NULL,
   [Question2] [bit] NOT NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

CREATE UNIQUE CLUSTERED INDEX [UCI_EmployeeTemperature]
ON [dbo].[EmployeeTemperature]
(
   [Date] ASC,
   [CheckerID] ASC,
   [FullName] ASC
)
WITH
(
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

GO

以下Stored Procedure程式碼用於檢索加密數據（最終將由 ASPX 單頁應用程序 (SPA) Web 應用程序使用）。

--SELECT OBJECT_ID('dbo.sp_GetEmployeeTemps','P') -- debug below
IF OBJECT_ID('dbo.sp_GetEmployeeTemps','P') IS NULL
  EXEC('CREATE PROCEDURE [dbo].[sp_GetEmployeeTemps] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[sp_GetEmployeeTemps]
AS

SELECT
ET.[Entry]
,CASE
   WHEN HR.[Employee_ID] IS NOT NULL THEN 'E'
 ELSE 'V'
END AS [Visitor] -- Show if record is for Employee or Visitor
,ISNULL(HR.[Name],ET.[FullName]) AS [Name] -- ISNULL for visitor. return visitor's name if not an employee.
,ET.[Temp]
,(SELECT DISTINCT chk.[Name] FROM [dbo].[Checker] AS chk INNER JOIN [dbo].[EmployeeTemperature] ON ET.[CheckerID] = chk.[LoginID]) AS [Checker]
,CAST(FORMAT(ET.[DateTime], 'yyyy-MM-dd hh:mm:ss', 'en-US') AS DATETIME) AS [Time] -- so that it doesn't round seconds to minutes (converting to SMALLDATETIME does that) and shows to the second.
,CASE
   WHEN ET.[Question1] = 1 THEN 'Yes'
   WHEN ET.[Question1] = 0 THEN 'No' 
ELSE NULL
END AS [Question1]
,CASE
   WHEN ET.[Question2] = 1 THEN 'Yes'
   WHEN ET.[Question2] = 0 THEN 'No' 
ELSE NULL
END AS [Question2]
FROM [dbo].[vw_Employees] AS HR
FULL JOIN -- to allow Visitors to be retrieved
(
   SELECT
    [Entry]
   ,[Temp]
   ,[CheckerID]
   ,[FullName]
   ,[EmployeeID]
   ,[DateTime]
   ,[Question1]
   ,[Question2]
   FROM [dbo].[EmployeeTemperature]
   WHERE CONVERT(DATE, [DateTime]) = CONVERT(DATE, GETDATE())
) AS ET
ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted
WHERE ET.[Entry] IS NOT NULL -- to not show unchecked employees.

GO

EXEC sp_refresh_parameter_encryption 'dbo.sp_GetEmployeeTemps';

當我嘗試創建或更改上述過程時，我收到以下錯誤：

The data types char and char(10) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Employee_Temperature') collation_name = 'Latin1_General_BIN2' are incompatible in the equal to operator.

這似乎表明問題出在這個 JOIN 子句上：

ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted

在此連接中，HR.[Employee_ID]未加密，並且是[vw_Employees]視圖的一部分，並且ET.[EmployeeID]是加密列。

為什麼這種平等加入不起作用？該文件指出加密列可以用於相等連接，這顯然是。

這就是問題#1。

---

問題 #2 似乎與我ISNULL涉及加密列有關ET.[FullName]。

如果我註釋掉該連接並ON 1 = 1出於調試目的進行，我會收到一個額外的錯誤：

Operand type clash: varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Employee_Temperature') collation_name = 'Latin1_General_BIN2' is incompatible with varchar

有什麼建議來處理這種情況嗎？

我已要求使用 GitHub 上的範例更新 MS Docs： https ://github.com/MicrosoftDocs/sql-docs/issues/4550

在此加入，HR。

$$ Employee_ID $$未加密，部分$$ vw_Employees $$視圖和 ET。$$ EmployeeID $$是加密列。 為什麼這種平等加入不起作用？該文件指出加密列可以用於相等連接，這顯然是。

仔細查看文件：

確定性加密總是為任何給定的純文字值生成相同的加密值。

使用確定性加密允許對加密列進行點查找、等式連接、分組和索引。

（強調我的）並記住 Always Encrypted 的基本案例：

Always Encrypted 允許客戶端加密客戶端應用程序中的敏感數據，並且永遠不會向數據庫引擎洩露加密密鑰…

如果引擎從不知道未加密的值，它如何能夠比較未加密和加密的連接？

您可以對確定性加密進行查找、連接等操作，因為您將為靜態輸入獲得相同的加密值。但是，它沒有提到您可以將加密與未加密進行比較。

在您的情況下，您需要加密您的搜尋密鑰才能在加密列中找到匹配項，這是確定性的，因此如果它們是相同的起始值，您應該能夠匹配加密值。

TL;DR - 將確定性加密列連接到確定性加密列是可以的，將非加密連接到加密則不行。

引用自：https://dba.stackexchange.com/questions/264926