Not able to get Microsoft.Data.SqlClient working on Powershell under Linux

HI,

Right now we are using System.Data.SqlClient to validate sql connection strings via powershell but we realized this library doesn’t support this additional connection param i.e. Column Encryption Setting=enabled

with this we are always running into ,

We even tried “SqlServer” library, but the error message is still the same

On debugging why it’s the case, we found this article which state Microsoft.Data.SqlClient is the way to go, https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

Now problem with Microsoft.Data.SqlClient is , from last 2 days two of us are trying to figure out how to even get this library working on powershell running inside a RHEL container

We installed library like this,

Register-PackageSource -provider NuGet -name nugetRepository -location https://www.nuget.org/api/v2
Install-Package Microsoft.Data.SqlClient -Provider nuget
Install-Package Microsoft.Data.SqlClient.SNI -Provider nuget

and then we are trying to validate connection string like this and irrespective of whatever DLL path we pass, we are always running into the same error i.e,

MethodInvocationException: Exception calling “Open” with “0” argument(s): “The type initializer for ‘Microsoft.Data.SqlClient.SqlAuthenticationProviderManager’ threw an exception.”

PS /> Add-Type -AssemblyName System.Data
PS /> Add-Type -Path ('./usr/local/share/PackageManagement/NuGet/Packages/Microsoft.Data.SqlClient.3.0.1/runtimes/win/lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll') -ReferencedAssemblies Microsoft.Data.SqlClient.SNI
PS />
PS /> Add-Type -Path ('/usr/local/share/PackageManagement/NuGet/Packages/Microsoft.Data.SqlClient.3.0.1/runtimes/win/lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll') -ReferencedAssemblies Microsoft.Data.SqlClient.SNI
PS />
PS /> $conn = new-object Microsoft.Data.SqlClient.SqlConnection
PS />  $conn.connectionstring = "Server=tcp:xxx-xxxx-sql.database.windows.net,1433;Initial Catalog=Automation_Platform;Persist Security Info=False;User Id=testadmin@xxxxxx-sql.database.windows.net;Password=xxx%3k@xx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;Column Encryption Setting=enabled;"
PS />
PS /> Write-Verbose $conn.connectionstring
PS />

PS /> Add-Type -path "./usr/local/share/PackageManagement/NuGet/Packages/Microsoft.Data.SqlClient.3.0.1/runtimes/win/lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll"
PS />
PS /> $con = New-Object Microsoft.Data.SqlClient.SqlConnection
PS /> $con.ConnectionString = "Server=xxxx;user=xxxxx;password=xxxxx"
PS />
PS /> $con.Open()
MethodInvocationException: Exception calling "Open" with "0" argument(s): "The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception."

Can you please help us understand how do we fix this or share us an working example which is powershell and linux based

we tried all this already and nothing works, https://github.com/dotnet/SqlClient/issues/623 https://gist.github.com/MartinHBA/86c6014175758a07b09fa7bb76ba8e27

Issue Analytics

State:
Created 2 years ago
Reactions:2
Comments:12 (9 by maintainers)

Top GitHub Comments

4reactions

JRahnamacommented, Oct 4, 2021

@rajivml I have spent sometime and I figured out the issue. Keep in mind that we, developers of SqlClient, are not PowerShell experts/developers.

The issue seems to come from the fact that PowerShell is not handling Nugets packages easily, look at https://github.com/PowerShell/PowerShell/issues/6724 Previous issues were addressed at issue #161, but non had the same issue as yours. Wrapping the command in try/catch block , as shown below, helped me to get to the source of the issue and was able to open a connection successfully

try{
$conn.Open()
}catch{
$_.Exception
}

Microsoft.Identity.Client Version 4.22.0 needs to be loaded.

Remember System.Data.SqlClient did not have these issues as it was shipped along with the framework and it had all needed libraries available to itself, whereas Microsoft.Data.SqlClient does not have that privilege and some libraries need to be loaded before hand for PowerShell to work correctly. To get a better understanding run:

[System.AppDomain]::CurrentDomain.GetAssemblies()

and you will get a list of loaded libraries.

3reactions

rajivmlcommented, Oct 8, 2021

Thanks a lot guys @Wraith2 @JRahnama and @cheenamalhotra. This worked, you can resolve the ticket

Register-PackageSource -provider NuGet -name nugetRepository -location https://www.nuget.org/api/v2 -Trusted
Install-Package Microsoft.Data.SqlClient -Provider nuget -SkipDependencies -Destination /opt/microsoft/powershell/7/ -Force
Install-Package Microsoft.Identity.Client -Provider nuget -SkipDependencies -Destination /opt/microsoft/powershell/7/ -Force

Add-Type -AssemblyName System.Data
Add-Type -Path('/opt/microsoft/powershell/7/Microsoft.Identity.Client.4.36.2/lib/net461/Microsoft.Identity.Client.dll')
Add-Type -Path ('/opt/microsoft/powershell/7/Microsoft.Data.SqlClient.3.0.1/runtimes/unix/lib/netcoreapp3.1/Microsoft.Data.SqlClient.dll')
#[System.AppDomain]::CurrentDomain.GetAssemblies()

$conn = New-Object Microsoft.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=tcp:xyz-sql.database.windows.net,1433;Initial Catalog=AS_SH;Persist Security Info=False;User Id=testadmin@xyz-sql.database.windows.net;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;Column Encryption Setting=Enabled"


try{
    $conn.open()
    $query = "select * from sys.sysfiles"
    $cmd = New-object Microsoft.Data.SqlClient.SqlCommand($query,$conn)
    $ds = New-Object system.Data.DataSet
    (New-Object Microsoft.Data.SqlClient.SqlDataAdapter($cmd)).fill($ds) | out-null
    $ds.Tables[0]
    $conn.Close()
}catch{
$_.Exception
}

Top Results From Across the Web

Manage SQL Server on Linux with PowerShell Core

On Windows, use Win + R , and type pwsh to launch a new PowerShell Core session. SQL Server provides a PowerShell module...

SqlClient troubleshooting guide - ADO.NET Provider for ...

Recommended Solution: Ensure client application references minimum v2.1.0 version of Microsoft.Data.SqlClient package. When using EF Core, add a ...

Could not load file or assembly System.Data.SqlClient

1. Make sure that the 'System.Data.SqlClient' assembly is present in your project's output directory or in a location where it can be discovered ......

Microsoft.Data.SqlClient is not supported on this platform

I have a project named Site.Dal that compiles using netstandard2.0. csproj is : <Project Sdk="Microsoft.

Introduction to Microsoft.Data.SqlClient namespace

Learn about the Microsoft.Data.SqlClient namespace and how it's the preferred way to connect to SQL for .NET applications.