Updating an Always Encrypted Column

When recently troubleshooting an issue, I needed to update a database record to test application functionality. Because the table had an Always Encrypted column, some extra steps were needed to make the UPDATE successfully. Let’s look at the error encountered and how it was resolved.

What is Always Encrypted?

Always Encrypted was made available to all editions with SQL Server 2016 SP1 and can be used to encrypt columns with deterministic encryption or randomized encryption. Your choice on which encryption is better for you depends on how you plan to use the encrypted data. Deterministic encryption will produce the same encrypted value every time whereas randomized will not have the same encrypted value. For more information on getting started with Always Encrypted, check out this post.

Handling Operand Type Clash Error

When trying to update a table where a column is encrypted, you may get some variation of an “Operand type clash” error message. In my case, there error was:

Msg 206, Level 16, State 2, Line 5
Operand type clash: varchar is incompatible with varchar(8000) encrypted with…

SQL Server could not handle the conversion on its own. Instead, I needed to create a variable to specify the type, set the variable to the value I wanted to use in my update, and then reference that variable in my update statement.

For example, this update would fail:

UPDATE TestTable
SET Password = 'Sup3rS3cur3P@$$w0rd'
WHERE Id = 444;
GO

If I created a @MyPassword variable with the type matching Password and set @MyPassword to my desired password value, the update completed successfully. The query should look more like this:

DECLARE @MyPassword AS VARCHAR(256) = 'MyS3cur3P@$$w0rd';

UPDATE TestTable
SET Password = @MyPassword
WHERE Id = 444;
GO

More Settings to Consider

You’ll also want to make sure you’re connection has “column encryption setting=enabled” and “Enable Parameterization for Always Encrypted” checked in Query Options settings. For more info, check out Microsoft’s Parameterization for Always Encrypted – Using SSMS to Insert into, update and Filter by Encrypted Columns article.

Thanks for reading!