Using always encrypted on a entity framework [code first] database

Question

I have an MVC application that uses entity framework / code first. I'm trying to set up always encrypted in order to encrypt a column (social security number / SSN). I'm running everything in Azure, including using Azure vault to store keys.

I have two models, SystemUser and Person. SystemUser is essentially an account / login which can administer 1 or more People.

The definitions look a bit like:

public class Person
{
        [StringLength(30)]
        [Column(TypeName = "varchar")]
        public string SSN { get; set; } // Social Security Number
        ...
        [Required, MaxLength(128)]
        public string SystemUserID { get; set; }
        [ForeignKey("SystemUserID")]
        public virtual SystemUser SystemUser { get; set; }
        ...
}

public class SystemUser
{
        ...
        [ForeignKey("SystemUserID")]
        public virtual HashSet<Person> People { get; set; }
        ...
}

I have a very basic page set up that just looks up a user and prints out their SSN. This works. I then adapted the page to update SSN and this also works. This to me implies that the Always Encrypted configuration and Azure Vault is set up correctly. I've got "Column Encryption Setting=Enabled" in the connection string and I encrypted the column SSN using SSMS (I'm using deterministic).

In my SystemUser class I have the following method as an implementation for Identity:

public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<SystemUser> manager)
{
        ...
        if (this.People.Any())
        {
                ...
        }
        ...
}

This is used for user logins. Running the code results in a:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Operand type clash: varchar is incompatible with varchar(30) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto11', column_encryption_key_database_name = 'xxx') collation_name = 'Latin1_General_BIN2'

It seems to fail on the line above "if (this.People.Any())". Putting a break point just before that line reveals the following about this.People:

'((System.Data.Entity.DynamicProxies.SystemUser_9F939A0933F4A8A3724213CF7A287258E76B1C6775B69BD1823C0D0DB6A88360)this).People' threw an exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' System.Collections.Generic.HashSet {System.Data.Entity.Core.EntityCommandExecutionException}

Any ideas here? Am I doing something that Always Encrypted does not support?

score 1 · Answer 1 · answered Dec 20 '17 at 10:47

1

Always encryption is not having support in entity framework. MS still working.

answered Dec 20 '17 at 10:47

user3480147

41
1
7

score 1 · Answer 2 · answered Feb 04 '18 at 10:53

This Blog Using Always Encrypted with Entity Framework 6 explains how to use Always Encrypted with Entity Framework 6 for DataBase first and Code First From existing database and Code first-Migrations with work arounds for different scenarios and problems.

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

1

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

Pass the constant argument as closure – this will force parametrization, producing >correct query:

var ssn = "123-45-6789";

context.Patients.Where(p => p.SSN == ssn);

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 18 '18 at 12:28

Nick

461
5
17

Using always encrypted on a entity framework [code first] database

3 Answers3