Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?

Question

The official Microsoft documentation for Always Encrypted on SQL Server 2017 states:

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.

(bold emphasis mine)

I'm currently using SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1 Standard Edition.

My SSMS (currently using v18.4) connection is already configured with the Enable Always Encrypted (column encryption) checkbox checked, and the Query Options -> Execution -> Advanced setting Enable Parameterization for Always Encrypted is also checked.

Below is the table schema I have.

The EmployeeID and FullName columns are encrypted with Deterministic Encryption Type.

The Temp column is encrypted with 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

The below Stored Procedure code is used to retrieve the encrypted data (eventually will be used by an ASPX Single Page Application (SPA) web app).

--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';

When I try to create or alter the above procedure, I receive the below error:

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.

This seems to indicate that the issue is with this JOIN clause:

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

In this join, the HR.[Employee_ID] is not encrypted, and part of the [vw_Employees] view, and the ET.[EmployeeID] is the encrypted column.

Why does this equality join not work? The documentation states that encrypted columns can be used in equality joins, which this clearly is.

That's problem #1.

---

Problem #2 seems to be with my ISNULL involving the encrypted column ET.[FullName].

If i comment out that join and do ON 1 = 1 for debugging purposes, I get an additional error:

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

Any suggestions to handle that scenario?

I have requested the MS Docs be updated with examples on GitHub: https://github.com/MicrosoftDocs/sql-docs/issues/4550

Answer 1

In this join, the HR.[Employee_ID] is not encrypted, and part of the [vw_Employees] view, and the ET.[EmployeeID] is the encrypted column.

Why does this equality join not work? The documentation states that encrypted columns can be used in equality joins, which this clearly is.

Take a closer look at the documentation:

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.

(Emphasis mine) and remember the basic use case of Always Encrypted:

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine...

If the engine is never aware of the unencrypted value, how would it be able to compare an unencrypted vs. encrypted on a join?

You can do lookups, joins, etc. on deterministic encryption since you are going to get the same encrypted value for a static input. Nowhere does it mention you can compare encrypted to unencrypted, however.

In your case you'd need to encrypt your search key in order to find a match in the encrypted column, which is deterministic, so if they are the same starting value you should be able to match the encrypted values.

TL;DR - Joining deterministic encrypted columns to deterministic encrypted columns is OK, joining non-encrypted to encrypted is not.