communication with 2 databases with always encryption through procedure

Question

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

Answer 1

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