0

How to perform Join operation with Encrypted and Non Encrypted Column in Sql server 2016. Im using .net framework 4.6.1. Column Encryption Setting = Enabled in Connection string.

Code : 

cmd = new SqlCommand("select determin.name as name from determin inner join determinjoin on determin.name = determinjoin.name ", con);

Error :

    The data types varchar(20) encrypted with (encryption_type = 
'DETERMINISTIC', encryption_algorithm_name = 
'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4', 
column_encryption_key_database_name = 'depdb') collation_name = 
'Latin1_General_BIN2' and varchar are incompatible in the equal to operator.
Bharat
  • 2,441
  • 3
  • 24
  • 36
Karthikeyan
  • 173
  • 4
  • 18
  • Should you not be asking yourself deeper questions at this point? Such as, "if this data is worth encrypting, why is it only encrypted in some tables and not in others?" – Damien_The_Unbeliever Oct 28 '16 at 07:05
  • this is requirement. I hve to put join on encrypted column in 1 table, and non encrypted column in another table. – Karthikeyan Oct 28 '16 at 07:23
  • 1
    I would imagine this is not possible. The value stored in sql server is encrypted. The column encryption setting does not change the underlying value on your data pages. You would have to hash the non encrypted value using MS's always encrypted algorithm to get the two values to match on a join. – dfundako Oct 28 '16 at 15:59

1 Answers1

1

As is, you cannot. The query processor does not have access to the key to decrypt for the join. If you REALLY must join the two tables, you should encrypt both columns with deterministic encryption.

That said, you might still not want to do it. Performance will be anywhere from bad to abysmal for anything more than a few million rows. If you really must and there's no option to modify the schema, then test and see if your users still feel the same after they see how it performs. If you can make small tweaks to the schema, there are options to make this easier. For instance, using some alternate column for such joins. Could be a surrogate key or perhaps even a natural key that isn't security sensitive.

SQLmojoe
  • 1,924
  • 1
  • 11
  • 15