0

How to perform join with Always Encrypted column? I tried this (name is the encrypted column)

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

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    Console.WriteLine(dr["name"]);
}

Console.ReadLine();
con.Close();

I'm getting this error :

Additional information: 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Karthikeyan
  • 173
  • 4
  • 18

1 Answers1

1

You have to set ColumnEncryptionSetting value to Enabled in the connection string, to make it work.

Please refer below link.

https://msdn.microsoft.com/en-us/library/mt757097.aspx

Should be on dotnet version 4.6 and above to use most of the supported features. Please go through some good information from a private link in my comments

Pavan Chandaka
  • 11,671
  • 5
  • 26
  • 34
  • Yes i enabled. Select, update,insert,delete command are working fine. But unable to perform join. – Karthikeyan Oct 27 '16 at 07:22
  • Hmmm.. The documentation says....Once you enable Always Encrypted for application queries, you can use standard ADO.NET APIs (see Retrieving and Modifying Data in ADO.NET) – Pavan Chandaka Oct 27 '16 at 07:25
  • Do you have a where condition – Pavan Chandaka Oct 27 '16 at 07:32
  • yes. Where working. I send the values as parameter. Ex : cmd.CommandText = @"delete from determin where Name =@Name"; SqlParameter Breed = cmd.CreateParameter(); Breed.ParameterName = @"Name"; cmd.Parameters.Add(Breed); cmd.ExecuteNonQuery(); But in case of JOIN, its shows above error. – Karthikeyan Oct 27 '16 at 08:14
  • Strange. Will try exploring – Pavan Chandaka Oct 27 '16 at 08:24
  • http://devcenter.wintellect.com/paulballard/6-things-you-should-know-about-sql-server-2016-always-on-encryption. Try this link. See you met all six rules. Especially dotnet version – Pavan Chandaka Oct 27 '16 at 08:37
  • the problem is, in case of where condition we sending value as parameter so .net automatically decrypt the value. But in case of join, we comparing encrypted value to no encrypted values. Here we unable to send parameter. – Karthikeyan Oct 27 '16 at 08:38
  • Based on the link just I put in comment join should work. Goto rule 3 first and next to 4 – Pavan Chandaka Oct 27 '16 at 08:39