0

I was exploring Always encrypted feature in SQL Server 2016 in local database. And in our project we were using temp tables extensively in stored procedures, jobs etc.

But when I tried to insert Always Encrypted columns to temp tables, I'm not allowed to do so.

In further research, I have found that the CMK, CEK used in the main database should be used in the tempdb too. So I created same CMK, CEK using ‘create’ statement, in temp database.

But the issue still persists, which is not allowing me to insert AE column data in main table to temp table which is also encrypted using the same keys.

Error getting:Operand type clash: nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'testDB') is incompatible with nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'tempdb')

Any help in getting this resolved is much appreciated.

Sruthy John
  • 21
  • 6
  • Might need a reboot / sql service restart - many temp db properties are only applied when the temp db is first created on each service restart. – Sql Surfer Sep 01 '19 at 12:17
  • @SqlSurfer : tested the same after restarting the sql service. Still no luck. Getting `Operand type clash: nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'testDB') is incompatible with nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'tempdb')` error – Sruthy John Sep 11 '19 at 07:30

1 Answers1

0

You have found a limitation of Always Encrypted. It does not allow the use of keys across databases. In addition to the 4 components of the column data type that you can describe in the CREATE TABLE statement, namely:

  1. NVARCHAR(50)
  2. encryption_type = 'DETERMINISTIC'
  3. encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  4. column_encryption_key_name = 'CEK_Auto1'

SQL Server adds a 5th component -- the database name:

  1. column_encryption_key_database_name = 'testDB'

This renders the data type database-specific in spite of the fact that the other elements are identical.

I assume that this is a safeguard that was built in to save developers from themselves because there is no guarantee that a key called CEK_Auto1 in one database is the same as CEK_Auto1 in another database. The method you used, which I assume involved generating CREATE scripts from the CEK and CMK from the first database does ensure that the key metadata is the same and will encrypt/decrypt using the same certificate (Master encryption key).

However, if a developer used SSMS to generate a new CEK in the second database with the same name as in the first, the key would actually be a new, unique key and would be incompatible with the other. It looks like Microsoft wanted to avoid that potentially catastrophic scenario.

Please describe what you do with the temp tables. There is a good alternative to the speed of temp tables, e.g. INSERT INTO permanent tables with TABLOCK or TABLOCKX

There are many limitations using encrypted columns, the foremost is that you can do no processing on the contents in your queries. Processing requires having the data in plaintext which necessitates temporarily having a copy of the rows to process without encryption.

Michael Herstine
  • 36
  • 3