0

We are currently implementing Always Encrypted for some columns in our database, but we are hitting a limitation we cannot explain when joining with temporary tables. We looked at Microsoft documentation and looked at many articles, but we cannot find an explanation as to why this is not working. We are using the latest version of SQL 2017: RTM-CU31.

When we have a query and we join on a temp table, SQL Server seems unable to detect that the parameter should be encrypted. Calling sp_describe_parameter_encryption is telling us that the SSN parameter is not encrypted which is wrong:

exec sp_describe_parameter_encryption
    N'
        CREATE TABLE #AvailablePatients (
            PatientID INT NOT NULL
            PRIMARY KEY (PatientID)
        )
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        INNER JOIN #AvailablePatients ON #AvailablePatients.PatientID = Patients.PatientID
        WHERE SSN=@SSN
        DROP TABLE #AvailablePatients',
    N'
        @SSN char(11)'

If we remove the join on the temp table, calling sp_describe_parameter_encryption is now correctly telling us that the SSN parameter is encrypted as it should:

exec sp_describe_parameter_encryption
    N'
        CREATE TABLE #AvailablePatients (
            PatientID INT NOT NULL
            PRIMARY KEY (PatientID)
        )
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        WHERE SSN=@SSN
        DROP TABLE #AvailablePatients',
    N'
        @SSN char(11)'

If we change the temp table to be a table variable, sp_describe_parameter_encryption is also telling us that the SSN parameter is encrypted as it should:

exec sp_describe_parameter_encryption
    N'
        DECLARE @AvailablePatients TABLE (
            PatientID INT NOT NULL
            PRIMARY KEY (PatientID)
        )
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        INNER JOIN @AvailablePatients AS AvailablePatients ON AvailablePatients.PatientID = Patients.PatientID
        WHERE SSN=@SSN',
    N'
        @SSN char(11)'

Here's how the Patients table was created:

CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1) NOT NULL,
[SSN] [char](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [TestCek], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[BirthDate] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [TestCek], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
PRIMARY KEY CLUSTERED ([PatientId] ASC))

We don't understand this behavior and it does not make sense to us that this would be happening as this is a simple use case. And as you can see above, there are no encrypted columns in the tempdb. We are looking to an explanation as to why this is not working and if there's a way to make this work with temporary tables.

Marco
  • 1

1 Answers1

0

I had posted the same question in the Microsoft forums and I was able to get a good answer: https://learn.microsoft.com/en-us/answers/questions/1023525/sql-server-2017-always-encrypted-issue-when-joinin.html.

Basically, sp_describe_parameter_encryption is not able to properly analyze the query because the temp table does not exist. This is different for a table variable because the table variable is a declared entity. The trick to make this work is to do the following instead:

CREATE TABLE #AvailablePatients (
    PatientID INT NOT NULL
    PRIMARY KEY (PatientID)
)

EXEC sp_describe_parameter_encryption
    N'
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        INNER JOIN #AvailablePatients ON #AvailablePatients.PatientID =  Patients.PatientID
        WHERE SSN=@SSN',
    N'
        @SSN char(11)'

DROP TABLE #AvailablePatients
Marco
  • 1