Can't modify SQL Server table structure with encrypted column

Question

'Donors (dbo)' table

Unable to modify table. Operand type clash: nvarchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Column Encryption Key', column_encryption_key_database_name = 'GODonationsDB') is incompatible with nvarchar

I get this error when trying to make any modification to the structure of the Donors table. I'm not modifying the encrypted SSN column, but it still happens. I'm trying to change the datatype of a normal date field. This database has been around for awhile. The encrypted field datatype is Nvarchar(11), but maybe years later it is supposed to be varbinary?

Trigger? Computed column? Temporal? Change tracking / change data capture? — Aaron Bertrand, Jul 02 '20 at 19:31
Also how are you "trying to make any modification" - are you loading the rows up in a grid in SSMS (Edit Top n Rows)? — Aaron Bertrand, Jul 02 '20 at 19:42

score 1 · Answer 1 · answered Oct 26 '20 at 16:09

The operand clash error will occur if you're trying to alter a table with Always Encrypted fields using the SSMS table designer or diagram. Use ALTER TABLE ALTER COLUMN script instead.

Adding new columns: You can only add new columns to the end of a table with Always Encrypted fields -- trying to insert a new column above an existing column causes the operand clash error. (You can still use the table designer to add new columns to the end of a table, though.)

Can't modify SQL Server table structure with encrypted column

1 Answers1