A documentação oficial da Microsoft para Always Encrypted no SQL Server 2017 afirma:
A criptografia determinística sempre gera o mesmo valor criptografado para qualquer valor de texto simples.
O uso de criptografia determinística permite pesquisas de ponto, junções de igualdade , agrupamento e indexação em colunas criptografadas.
(grifo meu)
Atualmente, estou usando o SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1 Standard Edition.
Minha conexão SSMS (atualmente usando v18.4) já está configurada com a Enable Always Encrypted (column encryption)caixa de seleção marcada, e as opções de consulta -> Execução -> configuração avançada Enable Parameterization for Always Encryptedtambém estão marcadas.
Abaixo está o esquema da tabela que tenho.
As colunas EmployeeIDe são criptografadas com .FullNameDeterministic Encryption Type
A Tempcoluna é criptografada com 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
O Stored Procedurecódigo abaixo é usado para recuperar os dados criptografados (eventualmente será usado por um aplicativo Web ASPX Single Page Application (SPA)).
--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';
Quando tento criar ou alterar o procedimento acima, recebo o erro abaixo:
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.
Isso parece indicar que o problema é com esta cláusula JOIN:
ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted
Nessa junção, o HR.[Employee_ID]não é criptografado e faz parte da [vw_Employees]exibição, e o ET.[EmployeeID]é a coluna criptografada.
Por que essa união de igualdade não funciona? A documentação afirma que as colunas criptografadas podem ser usadas em junções de igualdade, o que claramente é.
Esse é o problema nº 1.
O problema nº 2 parece estar ISNULLenvolvendo a coluna criptografada ET.[FullName].
Se eu comentar essa junção e fizer ON 1 = 1para fins de depuração, recebo um erro adicional:
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
Alguma sugestão para lidar com esse cenário?
Solicitei que o MS Docs fosse atualizado com exemplos no GitHub: https://github.com/MicrosoftDocs/sql-docs/issues/4550
