始终加密:如何与非加密列连接到确定性加密列？

Question

微软针对在Server 2017上始终加密的官方文档声明：

确定性加密总是为任何给定的纯文本值生成相同的加密值。使用确定性加密允许对加密列进行点查找、相等连接、分组和索引。

(黑体重点是我的)

我目前正在使用SQLServer2017 RTM-CU17 (KB4515579) v14.0.3238.1标准版。

我的SSMS (目前正在使用v18.4)连接已经配置了Enable Always Encrypted (column encryption)复选框，并且还选中了查询选项-> Execution ->高级设置Enable Parameterization for Always Encrypted。

下面是我的表模式。

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代码用于检索加密数据(最终将被Application (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.

这似乎表明问题在于这个联接条款：

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

在这个联接中，HR.[Employee_ID]不是加密的，[vw_Employees]视图的一部分，ET.[EmployeeID]是加密的列。

为什么这种平等不起作用呢？文档指出，加密的列可以用于相等的联接，这一点很明显。

这是第一个问题。

问题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：https://github.com/MicrosoftDocs/sql-docs/issues/4550上的示例更新MS文档

Answer 1

在此联接中，HR.员工_ID未加密，部分为大众_员工视图，ET.EmployeeID为加密列。为什么这种平等不起作用呢？文档指出，加密的列可以用于相等的联接，这一点很明显。

仔细看一看文档：

确定性加密总是为任何给定的纯文本值生成相同的加密值。使用确定性加密允许对加密列进行点查找、相等连接、分组和索引。

(强调我的)并记住始终加密的基本用例：

始终加密允许客户端加密客户端应用程序中的敏感数据，并且从不向数据库引擎显示加密密钥。

如果引擎从来不知道未加密的值，那么它如何能够在连接上比较未加密和加密的值呢？

您可以对确定性加密执行查找、联接等操作，因为对于静态输入，您将获得相同的加密值。但是，它没有提到你可以比较加密和未加密。

在您的示例中，您需要加密搜索密钥，以便在加密的列中找到匹配，这是确定性的，因此，如果它们是相同的起始值，则应该能够匹配加密的值。

TL；将确定性加密列与确定性加密列连接是可以的，而将非加密的列与加密的列连接则不是。