CODEWITHSUNDEEP
entity-frameworkcode-firstidentitydynamic-proxyalways-encrypted
Jeff Davies
Jeff Davies

Reputation: 21

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

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?

Upvotes: 2

Views: 5368

Answers (3)

Nick
Nick

Reputation: 481

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);

Upvotes: 1

Dov Miller
Dov Miller

Reputation: 2058

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.

Upvotes: 1

user3480147
user3480147

Reputation: 41

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

Upvotes: 1

Related Questions