Issue
Your application is intermittently failing with the following error right after adding or dropping Always Encrypted columns while executing INSERT or UPDATE statement.
SQL Error: 206, SQLState: S0002 Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK01', column_encryption_key_database_name = 'AlwaysEncryptedTestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'
The first line of the error message is changing, such as:
- Operand type clash: varchar is incompatible with varchar(8000)
- Operand type clash: varchar(1) is incompatible with varchar(8000)
- Operand type clash: varchar(20) is incompatible with varchar(8000)
Cause
This could be due to a stale execution plan in your database.
Below is the solution based on scenario:
- If you are using a stored procedure, user-defined function, view, or DML triggers that reference the table with the added/removed AE columns, you can refresh the parameters by executing sp_refresh_parameter_encryption.
E.g. sp_refresh_parameter_encryption [‘stored proc name’];
More details on usage can be found here.
OR
Also, you can recompile the stored procedure using sp_recompile.
E.g. sp_recompile N'stored proc name';
- To clear the plan cache for all stored procedures, execute the following command:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; - If your application is executing INSERT or UPDATE statements, you will need to execute DBCC FREEPROCCACHE. Please note that executing this command may result in temporary query performance degradation.
Important Notes
- If this is your development environment, ensure all developers are notified that they need to clear the cache after adding or dropping Always Encrypted columns from any table or make SQL statements part of your script.
- The issue can occur in both, Always Encrypted with or without Secure Enclave.
References
Microsoft