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

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.