Efcore: Exploratory testing: Always Encrypted end-to-end

[rowanmiller]

(edited to cover the full end-to-end support for always encrypted)

This issue covers:

• Make sure EF Core and our SQL Server provider work well with the always encrypted feature.

• Add APIs to migrations (and potentially higher in the stack) to make it easier for folks to enable Always Encrypted on a schema that EF is managing.

[ghost]

Has there been any progress on the .NET Core framework supporting MS SQL's Always Encrypted feature?

[maumar]

We haven't added any explicit support yet, however AlwaysEncrypted feature is fairly transparent, so you should be able to make it work with EF Core for the most part. You do need to provide DDL for the encrypted columns yourself (e.g by adding raw sql in your migrations) and we advise against encrypting PKs/FKs (many query scenarios add order by on PKs and those don't work with encrypted columns). Some time ago we tried to make AlwaysEncrypted work with EF6 without making any changes to the codebase and results were quite encouraging. Here is the blog post describing the experience: https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/

[ghost]

The migration itself works flawless.

Generated Migration minus Column SSN

    migrationBuilder.CreateTable(
                name: "Employee",
                columns: table => new
                {
                    Id = table.Column<long>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employee", x => x.Id);
                });

DDL for encrypted Column SSN

migrationBuilder.Sql(@"ALTER TABLE [dbo].[Employee]
                             ADD [SSN] [nvarchar](4000)
                             COLLATE Latin1_General_BIN2 ENCRYPTED WITH(
                                        ENCRYPTION_TYPE = DETERMINISTIC, 
                                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
                                        COLUMN_ENCRYPTION_KEY = CEK) NULL");

What's not working in a PoC is the ability of EF Code to handle the setting in the connection string for _column encryption setting=enabled_

There's an answered stackoverflow question for this here

Is there any news on this regard?

Thanks in advance

[ErikEJ]

Missing support in the .NET Core ADO.NET SqlClient is not an EF issue, please raise an issue in the .NET Core repo. In the meantime, you can use the .NET 4.6.1+ client instead

[divega]

Note for triage: Currently we have this in the backlog, and https://github.com/aspnet/EntityFrameworkCore/issues/9193 that is only about investigating what gap there is for us to support Always Encrypted in 3.0. I suggest we only need one issue to track this, and it should be for supporting always encrypted.

Obviously we also depend on .NET Core support being added to SqlClient, but that is already under discussion in https://github.com/dotnet/corefx/issues/18276.

[ajcvickers]

Just do smoke testing for 3.0

[ferronsw]

Is this supported for .Net Core 3.1?

[ajcvickers]

@ferronsw Yes, but there are some wrinkles to work out in the experience. For example: #19293. We are following up with the SQLClient team.

[ferronsw]

@ajcvickers Okay, good to hear. Are there docs for how to configure this? I want to test some things.

[ajcvickers]

@ferronsw It's basically transparent to EF. This doc should help set it up with the underlying SqlClient: https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/sqlclient-support-always-encrypted?view=sql-server-ver15

Let us know if you run into issues or places where this is made hard by EF.

[coldacid]

Considering we're on EF Core 3.1 at this point, should this issue be closed as completed?

[nishanperera]

Does it work with EF Core 3.1 ?

[ajcvickers]

@nishanperera Yes, although you may run into this: #19293

[mindlink]

Hello team, we are running into an issue using MigrationBuilder.InsertData or MigrationBuilder.UpdateData if it applies across an encrypted column. Specifically calling InsertData to insert data into an encrypted column during a migration step, where the target column is nvarchar results in:

'Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Message_CEK', column_encryption_key_database_name = 'EFCoreEncryption')

Using DbContext to insert the same data works, but we are exploring the limitations of EF Core and how to support different data migration scenarios. It raises the question, why does this work with the DbContext and not via the InsertData API?

This is on a simple toy project that creates a single-table database with a nvarchar column, there are two migrations (Initial and NewPropertyAdded).

1. We migrate to the "Initial" migration to create the database and tables and seed some data.
2. We go in and enable column encryption on the target table manually.
3. We migrate to the "NewPropertyAdded" migration and seed some extra data.

It is step 3 where we run into this problem. Is there a feasible alternative in these migration situations i.e. the ability to insert or update data that is in an encrypted column as part of the migration?

[ajcvickers]

@mindlink This sounds like a bug. Can you please file a new issue for it so that we can investigate.

[mindlink]

@ajcvickers Sure! => Opened #20734

[charleszipp]

I recently required encryption for storing sensitive data on a recent project. We ended up using the approach here as we didnt realize Always Encrypted was an option when using entity framework core. Would offer couple of suggestions

Easier developer experience for configuration

Leverage the fluent configurations and annotations to mark what properties should be encrypted. This would have a side benefit of starting a more generic contract that could be applied to other DbProviders such as cosmos.

public class MyDbContext : DbContext
{
    private readonly string _encryptionKey;

    public MyDbContext(DbContextOptions<PpemDbContext> options, string encryptionKey) : base(options)
    {
        // inject key, likely from configuration
        _encryptionKey = encryptionKey;

        // might be nicer if this could be retreived from options
        // _encryptionKey = options.encryptionKey;
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        // set the key to be used globally
        builder.UseEncryption(_encryptionKey);

        builder.Entity<Organization>(ConfigureOrganizations);
    }

    private void ConfigureOrganizations(EntityTypeBuilder<Organization> builder)
    {
    // annotate the property to be encrypted on write and decrypted on read
        builder.Property(org => org.AccountNumber).IsEncrypted();
    }
}

public class Organization
{
    // alternatively could annotate the property to indicate encryption
    [Encrypted]
    public string AccountNumber { get; set; }
}

Key Separated from Database

Most compliance regulations (at least in FinTech and Healthcare) require that the encryption key be kept separately from the database. Therefore, this would need to support the ability to register the Azure Key Vault provider. Secondary objective would be to register custom key providers.

Key Rotation

I havent seen mention of key rotation in the earlier comments. It would also be good to consider how one might rotate an encryption key. For our project we didnt get to a solution on this and I dont know of a good standardized strategy for this. However, if we were to update the key in key vault, the data would need to be decrypted using the original key and re-encrypted using the new key. It would seem like migrations might be able to help with this.

[ajcvickers]

Note from triage: pulling this into 5.0 to do manual testing of always-encrypted end-to-end. See also #3380.