SQL Server: Investigate current state of support for Always Encrypted

[MSRiganti]

Hi,

please read this EU regulation GDPR.

We will need functional support for SQL Always Encrypted before 25. may 2018.

When it will be implemented in EFCore? (Please, give it priority. It will be before 25 may 2018?)

Thanks

[ajcvickers]

As stated in the other thread, for this to work on .NET Core requires support from SqlClient, which is tracked by https://github.com/dotnet/corefx/issues/18276

This issue will track support in EF Core. It may already work, with limitations, when running on .NET Framework. We should discuss in triage.

[MSRiganti]

Hi, my first destination was the correct one. This issue have to be in dotnet/corefx. Please read dotnet/corefx#22324, there is an explanation.

If someone needs to reopen this one, so it doesn't bother me. ;-)

[ajcvickers]

As I said above, we are using this issue to track work in EF.

[MSRiganti]

OK, sorry for closing issue. :-)

[divega]

We will use this issue to track investigation work @maumar will be doing during 2.1. The initial goals of this investigation are:

1. Identify issues in areas of EF Core and its SQL Server provider which may block always encrypted from working. E.g. when a column is encrypted, parameter type or facet mismatches may cause failures, so we should validate whether we need to change our type mapping logic for parameters.
2. Estimate cost of fixing any issues identified.
3. Help estimate cost of fully supporting always encrypted end to end, including DDL and migrations support (#3380).
4. Follow up with SQL Server team on known limitations, e.g. when a column is encrypted (even with deterministic encryption) SQL Server blocks usage of ORDER BY (since encryption does not preserve order), but very often EF Core just needs to produce results with deterministic order, hence it would be good to have some kind of flag to override this behavior.
5. Follow up with SqlClient team on the timing for them to add support for always encrypted in .NET Core (in the meanwhile we can do the testing on .NET Framework).

[larryeby]

Any updates on this?

[MSAAAAuto]

Current URL at GDPR. (Update)

[ajcvickers]

The latest information we have on this is that always-encrypted should work fine with EF Core as long as:

• Key (primary, alternate, and foreign) columns are not encrypted
• All type mappings are explicitly and correctly specified with full SQL Server types

Encryption has issues with ordering, and EF Core needs to do ordering on keys for things like Include to work correctly, which is the main reason for the first restriction.

The second point comes from the lack of implicit conversions for encrypted columns, which means that EF really needs to know the exact types for the SQL generated. The easiest way to do this is to use [Column] or HasColumnType to specify the SQL Server types.

[ErikEJ]

And reveng explicitly defines all column types?

[ajcvickers]

@ErikEJ End-to-end work for always encrypted is tracked by #3380, which is not in the plan for 2.1.

[omora-RA]

At our organization we have a mix of EF6 and EF Core microservices. We are trying to figure out our column encryption strategy. For EF6 SQL Always Encrypted works like a charm. For EF Core I don't see a clear path. I see they added support to convert data types, and interesting enough the documentation specifically says:

(for example, encrypting strings)

To me that's a strong indicator that BYOE (Bring Your Own Encryption) is the way to go in EF Core, at least for now. Is there an official milestone/timeline for SQL Always Encrypted in EF Core?

[ajcvickers]

@omora-RA What specific issues are you seeing with EF Core?

[omora-RA]

@ajcvickers I am trying to use SQL Always Encrypted with EF Core. I just tried the latest EF Core 2.1.0-preview1-final. If I add "Column Encryption Setting=enabled" to the connection string It throws an error:

Keyword not supported: 'column encryption setting'.

[elwray]

I have the same error:

Keyword not supported: 'column encryption setting'.

[ErikEJ]

.NET Core SqlClient does not support Always Encrypted, you must use .NET Framework 4.6.1 or later

[sandorfr]

@erikEJ I think people are confused with the previous update from @ajcvickers. It could give the feeling that there was a partial support when reading quickly.

What is not clear for someone reading is that EF core is not tied to dotnet core. And that in that case he was meaning EF Core running on the full framework.

Sad to seee support for that very useful feature (given GDPR and Australia privacy act) on dotnet core delayed to who knows when. Switching to the old windows only framework is not an option for us.

[ErikEJ]

@sandorfr Read the comments here: https://github.com/aspnet/EntityFrameworkCore/issues/9193#issuecomment-320755801

[sandorfr]

I’m well aware of this 8 month old comment ;). I’m just stating why people are confused, and reaffirming the importance of that feature on dotnet core for us in the current context.

I understand that there’s al lot of other important work on SQL client and prioritizarion had to happen. I just hope we could see it coming in the next release after 2.1. Not in two years.

[omora-RA]

Our .netcore code is deployed on Linux Docker containers so .net 4.6.1 is not an option. Since EF Core 2.1 provides the hooks to convert data types I could bring my own encryption. It would be great if there was some guidance on how to tackle this.

[ajcvickers]

Sorry I wasn't clear that I was referring specifically to EF Core rather than the more general support for always encrypted in .NET Core.

[daveabrock]

Does anyone have a timeline for if this will be implemented? We have an extensive project on EF Core and have to scale back for this. Scaling back is an option but not a preferable one.

[ajcvickers]

@daveabrock Implemented in what sense? Do you mean when will always encrypted be available on .NET Core? If so, that is tracked here: https://github.com/dotnet/corefx/issues/22324

[ajcvickers]

Closing in favor of #3380, since it covers more than this.