Insert data using EF6 into SQL2016 table with Always Encrypted columns

Question

User865464427 posted

I've been experimenting with the Always Encrypted feature of SQL 2016 and I have been successful in inserting data into a simple table with encrypted columns so long as I explicitly write a parameterised query.

However if I try and insert the same data using an EF6 object automatically generated from the database I always get errors.

System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Operand type clash: nvarchar(max) is incompatible with nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'DatabaseName') Incorrect parameter encryption metadata was received from the client. 

It was my understanding that EF used parameterised queries under the hood so what do I need to configure to get this to work? Following another forum post I have tried setting all columns in the database to nvarchar(max) incase EF is setting the data types to the max length by default. I have tried using DataAnnotations to set the datatype and length although I have struggled to find examples of how to do this in VB. I still get variations on the error above, seemingly to do with nvarchar length.

I'm doing this in VB in a .net 4.6 web forms project, Database First approach. At the moment both the SQL instance and the IIS instance are both on my machine.  It isn't compulsory for me to achieve this in EF, I'd just like to know if it can be done. Any advice? Thanks in advance!

Answer 1

User753101303 posted

Hi,

The table is created by EF ? According to the error message encrypted columns should be created with additional options which seems to be confirmed at https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql - note the ENCRYPTED WITH (<options>).

Answer 2

User1120430333 posted

It was my understanding that EF used parameterised queries under the hood so what do I need to configure to get this to work?

You can use the EF backdoor and do it yourself with parameterized T-SQL using an EF Entity. 

Answer 3

User-832373396 posted

Hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">withag</g>,

Sir, I found this for you.

Connection string adjustments

In order to allow querying, connection string that is being used need to be modified by adding:

Column Encryption Setting=Enabled

From https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/ 

and execute sp_refresh_parameter_encryption to update the Always Encrypted metadata for the object. 

With regards, Angelina Jolie

Answer 4

User865464427 posted

PatriceSC, No the table was created in SQL, the project is using a Database first approach. As I say, I can insert data just fine from my application using a parameterized query but I just can't insert data using an EF model generated from that table.

DA924, I've just looked into the EF backdoor and whilst it might work there it seems to take just as much, if not more code than if I was just to stick with a regular SQL client parameterized query.  Thanks anyway!

Answer 5

User1120430333 posted

DA924, I've just looked into the EF backdoor and whilst it might work there it seems to take just as much, if not more code than if I was just to stick with a regular SQL client parameterized query. Thanks anyway!

EF can't do everything, and besides, you are using regular ADO.NET with SQL Command objects if you have to go the route of the EF backdoor, sharing the same connectionstring that EF is using  instead of having to create a new connectionstring for the regular SQL Client approach. Either way, it's the route you are going to have to take here , and take EF out of the picture.  

Answer 6

User865464427 posted

Yes that's what I'm thinking too. It's not a huge problem, I just happened to be bringing myself up to speed with EF6 and SQL2016 Always Encrypted at the same time and was surprised to find they wouldn't communicate easily. 

This article suggests Inserting should work "out of the box" and as it was written in 2015 I was hoping any kinks would have been worked out by now. 

https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/

"Insert, Update and Delete Operations

Insert, update and delete operations work properly without any additional customization. EF automatically parametrizes all insert/update/delete queries."

One of the comments beneath the article suggests that DataAnnotations and MaxLength might solve my problem but as I say, I couldn't get it to work. 

I'm happy to stick with ADO.NET and just get the job done.