SQL Server 2017 Always Encrypted issue when joining on temp table

Question

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.

Answer 1

I did not try to repro this myself, but I can guess what is going on. sp_describe_parameter_encryption presumably looks at the metadata that is generated at compile-time. The problem is that at this point the temp table does not exist. However, this does not result in an error, since SQL Server employs deferred name resolution for tables. That is, if table does not exist, when SQL Server tries to compile a query, it does not raise error. It is not until the statement is reach at run-time you get an error.

When you have a table variable it is a different matter, since the table variable is a declared entity.

You can test my hypothesis this way:

   CREATE TABLE #AvailablePatients (  
                PatientID INT NOT NULL  
                PRIMARY KEY (PatientID)  
            )  
     
        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)'  

That is you create the temp table before you call sp_describe_parameter_encryption . Because the temp table exists, SQL Server will now perform full binding.

Note that I am not suggesting this is a workaround, because I realise that it is not really practical to do this in your application. This would be to test my theory.

Answer 2

Hi @publicmarco ,

Welcome to Microsoft Q&A!
Erland made a good suggestion about using table variable.
Please refer to this article: Temporary Tables in SQL Server.
Perhaps, you could try using a global temporary table.
I've listed the differences between them.
Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.