CODEWITHSUNDEEP
sql-serveralways-encrypted
Scott Pendleton
Scott Pendleton

Reputation: 1037

Can't modify SQL Server table structure with encrypted column

'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?

Upvotes: 1

Views: 1229

Answers (1)

Dana
Dana

Reputation: 729

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

Upvotes: 1

Related Questions