Ms.data.sqlclient doesn't work w/ .NetStandard 2.0 class library

[TheOriginalTom]

Describe the bug

A simple ‘SELECT * FROM Table’ on a SQL Server 2017 table that has three Always Encrypted columns in a .NetStandard 2.0 class library returns a SqlException.

Describe the error

If you are seeing an exception, include the full exceptions details (message and stack trace):
Failed to decrypt column 'Name'. The type initializer for 'Microsoft.Data.SqlClient.SqlSymmetricKeyCache' threw an exception. The InnerException is: System.Runtime.Caching is not supported on this platform.

Describe any tests

The following code works in a .NetFramework4.8 class library, and works in .NetCore2.2 class library. It does NOT work in .NetStandard2.0 class library.

Code to reproduce

public static DataTable RunSqlQueryGetDataTable()
{
var sqlquery = "SELECT * FROM ACCOUNT";
DataTable dt = new DataTable();
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder
{
// local db
IntegratedSecurity = true,
InitialCatalog = "JL",
DataSource = "T\SQLSERVERDEV"
};
using (var sqlConnection = new SqlConnection(string.Concat(sb.ConnectionString, ";Column Encryption Setting=enabled")))
{
using (SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlConnection))
{
sda.SelectCommand.CommandType = CommandType.Text;
sda.Fill(dt);
}
}
return dt;
}

Expected behavior

Expect call to return a DataTable object with unencrypted results of the full Select * From statement. This is the result from the .NetFramework and .NetCore calls.

Further technical details

Microsoft.Data.SqlClient version: 19249.1
.NET target: .NetStandard2.0
SQL Server version: SQL Server 2017
Operating system: Windows 10

[TheOriginalTom]

ALSO:
I queried the documentation for the API under the system.runtime.caching namespace, which states that these APIs should be supported in the .NET framework and the .net core. But the documentation does not say that these APIs support .net standard. But the description of github makes us confused too. - aasfsfsf

[Wraith2]

Had a poke at it and it looks like it's using MemoryCache which is in NS2.0+platform extensions. Looking at the project file in netcore it should be generating an NS assembly and I have an assembly locally in my corefx build tree. So it looks like it should be supported and available for SqlClient to use as a dependency. This might be an issue for someone in corefx.

[TheOriginalTom]

Okay. Is this going to get fixed or are you going to remove the reference to MS.Data.SqlClient working with NS2.0+ in the md?

[cheenamalhotra]

Hi @TheOriginalTom
Yes, we are looking into it and intend to fix the behavior soon!

[TheOriginalTom]

ETA?

[cheenamalhotra]

@TheOriginalTom It is in our Radar, but not rising above other Active and High Priority list items right now. We'll update you when there's an update.

[saurabh500]

AFAIK, Always Encrypted wasn't supposed to work correctly with netstandard2.0. I think this is a bug where an PlatformNotSupportedException should have been thrown instead of Type Initialization failing.
This was due to a bunch of APIs needed for AE, which were missing on .netstandard2.0
We wont be able to provide support for AE on netstandard2.0, however we should do the error handling better and document this.

cc @David-Engel

[Wraith2]

Any idea which apis were missing?

[saurabh500]

I don't remember at the top of my head. I will have to dig through the PRs and mail threads.
The work was done by another MS employee on the AE team.

[saurabh500]

I do remember that there were a couple APIs related to crypto.

[TheOriginalTom]

So basically there is no way to access AE data from UWP code. (UWP can't ref .NetCore or .NetFramework class libraries.)

[David-Engel]

From an email I saved:

As I recall, the only encryption dependency that was a problem was the System.Security.Cryptography.ECDiffieHellmanCng class. Most of the other crypto base abstract classes had a method called Create() that would instantiate the appropriate implementation based on the platform. ECdiffieHelman.Create() was not available then but is now available in .NET Standard 2.1 preview. Very soon this should not be an issue I am thinking. Once this is available then I am pretty sure that there will be no need for any of the .netcoreapp.cs files; that is, they probably can be merged into one single C# file.

Additionally, I am thinking that this non .NET Standard support only applies to V2 of AE since it is the only part that uses ECDH crypto functionality. I’d have to double check to be sure.

[ckadluba]

We are affected by this problem too in the Serilog project: serilog-mssql/serilog-sinks-mssqlserver#208 (comment)

Are there any news on this? Can a fix be expected in the future?

[ckadluba]

One more observation. From what I have seen in my tests, the problem seems to occur only when using Microsoft.Data.SqlClient from a .NET Standard lib which is used from a .NET Framework app but not when the app is targeting .NET Core. Can anyone confirm if my findings are correct?

Thank you very much!

[Wraith2]

if you're seeing PlatformNotSupportedExceptions it usually means that you've restored without specifying a runtime. Because the Linux and windows implementations differ the restore has to be done with a valid runtime identifier and while you can develop against the netstandard ref library you can't run against it.

[ckadluba]

Hi @Wraith2! Thanks for the hint!

I was able to fix the problem by adding a package reference to Microsoft.Data.SqlClient directly in the .NET Framework app, just like described here: #361.

[karinazhou]

Close this one because we have the fix in the latest v2.1.0-preview1.