Entity Framework (Net Core), Azure Key Vault keys and SQL Always Encrypted columns

The problem

I've been struggling to get working examples that use Entity Framework, SQL Server Always Encrypted (in this case SQL Azure) and Azure KeyVault keys together. I needed these in order to help me understand how to implement a solution that used all three resources. In this article, I've pulled a github example together from a number of sources.

Note that when using Always Encrypted columns with netcore 3.1+, the package to be used should be Microsoft.Data.SqlClient and Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider rather than System.Data.SqlClient.

Nuget package
https://www.nuget.org/packages/Microsoft.Data.SqlClient/

The Azure KeyVault resource contains the certificate that is used to encrypt the data in the encrypted columns.
The Azure KeyVault key is used to create a SQL Always Encrypted encryption key in the database.

https://github.com/dotnet/SqlClient

Thanks to these articles that helped me pull this post together:

Hope this helps.

Requirements to get this going

  • Visual Studio 2019 or VS Code
  • Net 5.0 SDK. The example test should work with Net Core 3.1 as well.
  • locally running sql instance preferred (development, express for example)
  • Azure Key Vault key
  • Azure application registration with access to the key vault key
  • valid values in the appsettings.Development.json. See here for further info.

Steps

  • Create a file called the appsettings.Development.json file in the same directory as appsettings.json (test project). This file has been excluded from commits by the gitignore file. Populate the details as shown in the appsettings.json with actual values.
  • Create a local sql database called eftest
  • Build the solution and then run the tests provided.
  • run the tests

Appsettings to update

appsettings.json

Test to run or debug

Troubleshooting.

Couple of things to look out for:

  • If you get an error like this: Operand type clash: nvarchar is incompatible with varchar(8000) encrypted with (encryptiontype = 'DETERMINISTIC', encryptionalgorithmname = 'AEADAES256CBCHMACSHA256', columnencryptionkeyname = 'CEKAuto1', columnencryptionkeydatabasename = 'Developmentv2qa') collationname = 'SQLLatin1GeneralCP1CI_AS'**. It seems that if the model string fields that are being encrypted don't have a Ensure you have set the entity data types on your model fields. Don't leave them to default. See here. For example, this sets the field to varchar and not nvarchar.

    [Column(TypeName = "varchar(20)")]
    public string SSN { get; set; }
    
  • Note: using nvarchars seem to be problematic anyway, especially when dealing with them in queries. Avoid them and use varchars if possible. See this document. Search for nvarchar to see what i mean: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-ver15

Send me a PR if you find any problems with it.

github source