Add EFCore support (or documentation) for enabling AlwaysEncrypted w/ SQL Server

[joschmo80]

Add EFCore support (or documentation) for enabling AlwaysEncrypted w/ SQL Server. I develop medical device software, and encryption of patient health information (PHI) is critical. My product is using .NET Core 6, EFCore 6, SQL2019, and Win10.

Current documentation on the internet is lacking or out of date (or I am searching in the wrong spots):

• I have not found any documentation on how to do this through EFCore natively.
  • Ideally this would involve the use of data annotations (like [EncryptedColumn]) above each property, or through fluent API.
• https://stackoverflow.com/questions/41027953/sql-server-always-encrypted-with-net-core-not-compatible calls out AzureKeyVaultProvider. However, Azure sounds like the cloud which won't work in my case, as Hospital devices typically don't have internet access. For this reason, it uses the local Windows certificate manager.
• I have not found any documentation on how to do this through C# natively (all published solutions are through SSMS or powershell).
  • https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver15 requires use of SSMS, which isn't viable as this won't be included on customer machines.
  • https://docs.microsoft.com/en-us/powershell/module/sqlserver/set-sqlcolumnencryption?view=sqlserver-ps requires Set-SqlColumnEncryption which is only supported in Powershell 5- (.NET Framework). Calling this from .NET Core is problematic, due to assembly dll clashes for .NET Framework vs. .NET Core. Also, using Powershell 5- is less than ideal, for performance and security reaons. I have not found any examples for enabling AlwaysEncrypted w/ Powershell 7+, or what is the PowerShell 7+ equivalence of Set-SqlColumnEncryption, New-SqlColumnMasterKey, or New-SqlColumnEncryptionKey
• https://emrekizildas.medium.com/encrypt-your-database-columns-with-entityframework-1f129b19bdf8 sounds promising, but doesn't enable AlwaysEncrypted within SQL server (the conversion is done through client side converters).
• I tried doing this through SMO, but the documentation for say https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.columnencryptionkey?view=sql-smo-160 is lacking (i.e no code examples)

Thanks in advance!

[roji]

Duplicate of #23970

[roji]

@joschmo80 EF Core doesn't currently provide any specific APIs or support for Always Encrypted, but you should be able to set up your database schema with Always Encrypted, and then using EF Core to interact with the data. I can see there's at least some support for enabling Always Encrypted via T-SQL (e.g. this), so you may be able to use raw SQL in your migrations to set Always Encrypted after your tables/columns are created by EF Core. Otherwise, I'd advise looking at general SQL Server resources on Always Encrypted - for now this is out of the scope of the EF Core documentation.

Of course this isn't as good as having EF Core manage everything for you out of the box, but it should still be doable.

[joschmo80]

Thanks @roji. I was able to get AlwaysEncrypted working today by code generating a powershell file, and forking a PowerShell5 process with that as an argument. This allowed me to circumvent the aforementioned dll hell regarding calling PS5 (.NET Framework) in process from .NET Core code.

However, this took a lot of research/effort, as described in this post and #23970. In the future, I hope Microsoft considers 1) Adding support for this in EFCore 2) Providing docs on how to do this from C# and 3) Providing docs on how to do this from PowerShell 7+.

[roji]

Glad to hear you could get it to work. I realize the situation isn't ideal, but it's important to realize that EF Core will probably not provide a full turn-key setup solution for Always Encrypted, because of security concerns (keys and so forth); so there will likely still be various steps users will need to take in order to set this up. We'll of course do the best to help out in our docs, but at the end I'd encourage you to share your experience with the SQL Server team, since that setup process needs to be made easy also for non-EF users.

[joschmo80]

Thanks again for your help. To close the loop, I reached out to the SQL Server team here.