Lesson Learned #257: Encryption scheme mismatch for columns/variables

Today, I worked on a service request that our customer using Always Encrypted is facing the following error message:  SqlErrorNumber=33277,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=33277,State=2,Message=Encryption scheme mismatch for columns/variables 'ColumnName'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'xxx') and the expression near line '1' expects it to be DETERMINISTIC, or PLAINTEXT.

Using Azure Data Studio -> SQL Server Profiler extension during the execution of the application we saw:

• The customer has a table [SalesLT].[SalesOrderDetail] with an encrypted column 'ColumnName' and they want to use the following batch to create an internal table plus insert the data from encrypted table to this temporal table.

IF OBJECT_ID('[SalesLT].[TemporalTable]', 'U') IS NULL 
BEGIN 
 select * into [SalesLT].[TemporalTable] from [SalesLT].[SalesOrderDetail] where 1 = 2
 union select * from [SalesLT].[SalesOrderDetail]
END

• The first command select * into [SalesLT].[TemporalTable] from [SalesLT].[SalesOrderDetail] where 1 = 2 is used to create the temporal table table without rows just only the structure.
• But running union select * from [SalesLT].[SalesOrderDetail], SQL SERVER detects that the application wants to insert data from [SalesLT].[SalesOrderDetail] (encrypted table) to temporal table (PlainText).
• As temporal table has not an encrypted column and the source table has an encrypted, we are doing an INSERT … SELECT* … Always Encrypted - SQL Server | Microsoft Learn , the error is raised due to an unsupported operation.

Enjoy!