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.
- 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!