始终加密：如何将非加密列与确定性加密列进行相等连接？

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