EF Core Decryption of SQL Column Level Encryption

Question

My team and I are attempting to decrypt values from a db (Azure SQL) that has column level encryption turned on; with certs stored in key vault.

Expected Result:

var someLinqQuery = _contex.Users.First();
someLinqQuery.SSN = "000-00-0000";

Actual Result:

var someLinqQuery = _context.Users.First();
someLinqQuery.SSN = "the var binary (encrypted) version of the ssn";

FWIW, this works fine w/ raw sql. But we would like the option of not doing it this way, and encrypting more data.

Also we have the azure keyvault code here:

 //Key Vault Code Below
    private static ClientCredential _clientCredential;

    public static void InitializeAzureKeyVaultProvider()
    {
        if (!isActivated)
        {
            _clientCredential = new ClientCredential(_applicationId, _clientKey);

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
        {
            { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
        };

            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
            isActivated = true;

        }
    }

    public static async Task<string> GetToken(string authority, string resource, string scope)
    {
        var authContext = new AuthenticationContext(authority);
        AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

        if (result == null)
        {
            throw new InvalidOperationException("Failed to obtain the access token");
        }
        AccessToken = result.AccessToken;
        return result.AccessToken;
    }

This is loaded in the startup class. I've also moved the code to other places in the app w/ the same results.

My question is this, on .net core 2.1.x and EF core 2.1.x is this possible using LINQ? Do I need to upgrade?

Answer 1

According to my research, if we want to use Always Encryption( Column Encryption), we need to use the Microsoft.Data.SqlClient. For more details, please refer to the document. Besides, Microsoft.EntityFrameworkCore.SqlServer 3.x depends on Microsoft.Data.SqlClient, so I suggest you use EF core 3.x

For example. I do a test in the console application.

1. Configure database

2. Application

   a. Install SDK

   <PackageReference Include="Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider" Version="1.1.0" />
   <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
   <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
   <PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.2.7" />
   

   b. data model

   class Patient
   {
   
     public int PatientId { get; set; }
     public string SSN { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public DateTime BirthDate { get; set; }
   }
   

   c. database context

   private static Boolean isInitialized;
   public TestContext(DbContextOptions<TestContext> options) : base(options) {
            if(! isInitialized) { InitializeAzureKeyVaultProvider(); isInitialized = true; }
   
       }
   
       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
          // your sql server connnection string
           var constr = "Server=<>;Initial Catalog=<>;User ID=<>;Password=<>;Column Encryption Setting=enabled";
          SqlConnection connection = new SqlConnection(constr);
   
   
           optionsBuilder.UseSqlServer(connection);
       }
       public DbSet<Patient> Patients { get; set; }
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           modelBuilder.Entity<Patient>().ToTable("Patients");
       }
   
       private static string clientId = "";
       private static string clientSecret = "";
       private static ClientCredential _clientCredential;
   
       private static void InitializeAzureKeyVaultProvider()
       {
            _clientCredential = new ClientCredential(clientId, clientSecret);
   
           SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
             new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);
   
           Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
             new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
   
           providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
           SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
       }
   
       private  static async  Task<string> GetToken(string authority, string resource, string scope)
       {
           var authContext = new AuthenticationContext(authority);
           AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);
   
           if (result == null)
               throw new InvalidOperationException("Failed to obtain the access token");
           return result.AccessToken;
       }
   

   d. test

    static void Main(string[] args)
     {
         Console.WriteLine("Hello World!");
         DbContextOptions<TestContext> options = new DbContextOptions<TestContext>();
         var db =new TestContext(options);
         var results =db.Patients.ToListAsync().Result;
         foreach (var r in results) {
             Console.WriteLine(r.SSN);
   
   
         }
   
         Console.ReadLine();
     }