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

[rajivml]

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 [email protected];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,
#623
https://gist.github.com/MartinHBA/86c6014175758a07b09fa7bb76ba8e27

[Wraith2]

There are two problems that usually occur with powershell usage. 1) failure to restore the dependencies, 2) attempt to use the ref assembly. This sounds like 1 to me, the .cctor for that type uses the System.Configuration system and if you haven't restored that dependency it won't be able to find it and will fail. Check the dependencies on nuget.org and see if adding the ones relevant to your system helps.

[cheenamalhotra]

Hi @rajivml

Have you tried specifying Authentication = Active Directory Password in connection string? Since your user [email protected] is an active directory user, you need use authentication mode in connection string.

[rajivml]

@cheenamalhotra Hi, which library are you referring to System.Data.SqlClient or Microsoft.Data.SqlClient

with Microsoft.Data.SqlClient library, we are not even able to set connectionString property on connection object, if we see the Write-Verbose output, the connection string is empty, that's why con.Open() is throwing the below error

Is there a working example anywhere in the Official Microsoft documentation which we can refer to to get it working ?

Am looking for a linux specific example, found some github issues but they are all windows related

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 /> Write-Verbose $conn.connectionstring
PS />
PS /> $con.Open()
MethodInvocationException: Exception calling "Open" with "0" argument(s): "The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception."

[JRahnama]

@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 PowerShell/PowerShell#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.

[JRahnama]

@rajivml let me know if the problem has been solved.

Thanks

[rajivml]

HI @JRahnama ,

Thank you very much for looking into it, looks like some of the dependent dll's are not compiled for linux :(

[Wraith2]

Linux builds use the native SNI implementation and the nuget restore process understands that and provides the correct dlls, those native sni dlls aren't needed and the fact that you're trying to use them is a problem.

[rajivml]

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 [email protected];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
}

[ErikEJ]

Docs update/addition?

[Wraith2]

Docs update/addition?

Possible but that would make it look like a supported scenario where as far as I know it isn't, it can be made to work but any change to dependencies could break it and that's on the user to fix.

[ErikEJ]

Right - more like a community blog post then, I guess

[JRahnama]

@rajivml happy to hear that the issue is solved. @Wraith2 thanks for the support. Closing the issue as resolved.