1

Currently I am working on migration of my DB to SQL server 2016 with always encryption. I have 2 Databases and selecting data from one DB from other table through stored procedures. when selecting or updating the encrypted columns from DB1 by using stored procedure in DB2 I got Operand type clash: error. I have created column master key and column for the 2 databases with same name and same certificate. 

use CustDb1

Select  @custSSN = customer 
        from    CustDb2..customer a
        where   a.SSN = @psSSN

SSN column in customer in the both DB are encrypted but when I run this I got the below error:

Msg 33277, Level 16, State 6, Procedure copy_customer, Line 891 [Batch Start Line 167] Encryption scheme mismatch for columns/variables '@psSSN'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'TestCEK', column_encryption_key_database_name = 'CustDb2') and the expression near line '940' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'TestCEK', column_encryption_key_database_name = 'CustDb1').

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
Kiran
  • 23
  • 4

1 Answers1

0

Best practices for data migration using Always Encrypted are documented here. Please follow the directions specified in the blog for data migration.

Currently, cross Database comparisons on encrypted data are not supported by Always encrypted

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • Thank you for your reply Nikhil. Its a working product and there are more than 250 procedures that are using this kind of logic. It is very difficult to move all the logic to .net within a short time. is there is any alternative way to handle this in sql server itself? – Kiran Dec 21 '16 at 06:11