Entity Framework 6 and reading encrypted fields using SqlQuery and ExecuteSqlCommand

Question

We had to update our server, MS SQL 2019, and encrypt some fields.

Now, in both our application and in MS SQL Managament Studio, it works with a full read of a table (select * from whatever). However, if we add a where-clause it fails.

The general Entity framework queries seem to work, but using SqlQuery. Like:

 var personToAddOrUpdate = impro_context.Persone.SqlQuery(
$"select * from persone where signum='{resultedUser.SamAccountName}'").
FirstOrDefault();

This does not work:

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'COL_ENCRTY_MASTER_TEST', column_encryption_key_database_name = 'Impro_V2_test') collation_name = 'Estonian_CI_AS'

Also, it does not work in SQL Server management studio

For both, just reading "select * from persone" will work for both

Searching the net and I found e.g. How to query data when columns are encrypted in SQL Server 2016

 var personToAddOrUpdate = impro_context.Persone.SqlQuery(
$"DECLARE @MyValue nvarchar(100) = '{resultedUser.SamAccountName}'; \r\n
SELECT Signum FROM persone WHERE Signum = @MyValue).
FirstOrDefault();

Still, this causes:

Encryption scheme mismatch for columns/variables '@MyValue'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '1' expects it to be DETERMINISTIC, or PLAINTEXT.

The query works in SQL Server Management Studio

The page mention DETERMINISTIC, which does also apply to me.

The connection string has the encryption enables, and it actually works for the full reads data correctly, but only queries do now work

data source=eetlsw0115;initial catalog=test;persist security info=True;
user id=something;password=something;multipleactiveresultsets=True;
application name=EntityFramework; Column Encryption Setting = Enabled;

I did, but I found out a lot more today, and this question is more specific. I did not get any real answers as my first post was more generic. I will delete it Here it is related to "SqlQuery" — Sonnich Jensen
– Sonnich Jensen, Commented May 9, 2022 at 18:52
@Larnu how do I extend the question then? I hit the next one.... — Sonnich Jensen
– Sonnich Jensen, Commented May 11, 2022 at 17:19
Don't cha he the question after you have answers; ask a new one about the new problem. — Thom A
– Thom A ♦, Commented May 11, 2022 at 17:31

Charlieface · Accepted Answer · 2022-05-09 23:01:31Z

0

The particular link you reference refers to using SSMS, which has specific handling for DECLARE variables which need to be encrypted.

For SqlClient in C#, and by extension Entity Framework, you need to pass in the data as a parameter and use it directly to the query. Do not store it in an intermediate variable, it will not work

cons string query = @"
SELECT Signum
FROM persone
WHERE Signum = @MyValue;
";

var personToAddOrUpdate = impro_context.Persone.SqlQuery(query,
    new SqlParameter("@MyValue", SqlDbType.NVarChar, 100) {Value = resultedUser.SamAccountName}
  ).FirstOrDefault();

This also shows the correct way to pass parameters from C# to SQL. Do not interpolate values into your query.

You also need to enable Column Encryption Setting=enabled in your connection string, and the certificate needs to be accessible. See also the documentation.

answered May 9, 2022 at 23:01

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sonnich Jensen Over a year ago

the result of you idea is : The data reader is incompatible with the specified 'Test_EF.Persone_EF'. A member of the type, 'Active', does not have a corresponding column in the data reader with the same name. Active is a boolean field, which fails when reading

Charlieface Over a year ago

Well you haven't selected Active but it is present in your EF model for Persone, so EF is trying to load it but can't find it. You would need to select that also. This is no different to without any encryption. Note that your SQL query did not error: if it had you would not have got as far as getting a DataReader coming back

Sonnich Jensen Over a year ago

Thanks it works actually. Dont know why it failed with * yesterday evening. I guess I was tired and did something wrong

Collectives™ on Stack Overflow

Entity Framework 6 and reading encrypted fields using SqlQuery and ExecuteSqlCommand

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related