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

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 NOT NULL,
[EmployeeID] char 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 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 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

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.

StackExchange Database Administrators Q#264926, answer score: 2