Sqlclient: "Microsoft.Data. SqlClient is not supported on this platform." when trying to use it in PowerShell 7.0.2

[MasayukiOzawa]

The following environments are used as PowerShell Core.

Name                           Value
----                           -----
PSVersion                      7.0.2
PSEdition                      Core
GitCommitId                    7.0.2
OS                             Microsoft Windows 10.0.19041
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Download 2.0.0.

Install-Package Microsoft.Data.SqlClient

Now, the following script will run Exception calling ".ctor" with "0" argument(s): "Microsoft.Data. SqlClient is not supported on this platform. It occurs.

$dllPath = Join-Path $ENV:USERPROFILE "\AppData\Local\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.2.0.0\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll"
Add-Type -path $dllPath
$con = New-Object Microsoft.Data.SqlClient.SqlConnection

Can't I run Microsoft.Data. SqlClient 2.0.0 on PowerShell Core 7.0.2?

[MartinHBA]

try use \Microsoft.Data.SqlClient.2.0.0\runtimes\win\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll istead of \Microsoft.Data.SqlClient.2.0.0\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll"

[MasayukiOzawa]

Thank you.
I used a DLL under the win directory and it worked!

[MasayukiOzawa]

I was able to load the assembly, but when I tried to open it, I got the following error As a result, we were unable to open a connection.

$dllPath = Join-Path $ENV:USERPROFILE "AppData\Local\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.2.0.0\runtimes\win\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll"
Add-Type -Path $dllPath

$con = New-Object Microsoft.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=xxxx;user=xxxxx;password=xxxxx"
$con.Open()

$con.State
$con.Close()

MethodInvocationException: 
Line |
   6 |  $con.Open()
     |  ~~~~~~~~~~~
     | Exception calling "Open" with "0" argument(s): "The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception."

Up to 1.0.19269.1 I was able to open a connection with such code, but since 2.0, the assembly required has changed?

[MartinHBA]

having same, try 1.1.3 , tested from my side and works

[MasayukiOzawa]

@MartinHBA Thank you for Reply.
It worked on 1.1.3 in my environment as well.

Using 2.0.0-preview4.20142.4 or later, I got the error "The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception."
(No exception occurred until 2.0.0-preview3.20122.2.)

[MartinHBA]

it's the Powershell, I am afraid it's not possible to load assembly with all dependencies automatically. During installation of package it's all downloaded, but Add-Type doesn't support loading assembly including all dependent assemblies. Just one single DLL, all others that might be required are missing. For example if you try windows auth it fails with required SNI. I think this is broader issue with PS loading needed assemblies, at the moment I see only loading it one by one and it doesn't work well. If you will be able to load assembly with all dependent automatically, let me know :)
As far as I know this will be addressed in PSGet v3.

[MasayukiOzawa]

Added Microsoft.Data.SqlClient.SNI.runtime from "2.0.0-preview4.20142.4" to the dependency.
I have placed Microsoft.Data.SqlClient.dll in the same directory as Microsoft.Data.SqlClient.dll and it works :)

The error occurred because you did not check for dependencies on the latest version.

@MartinHBA

Thank you for your advice!!
Thanks to you, I was able to make it work.

[cheenamalhotra]

@MasayukiOzawa

If you're installing all dependencies by yourself, please make sure you install all latest versions of required dependencies as well, as referenced by Microsoft.Data.SqlClient.