System.Data.SqlClient is not supported on this platform.

I am trying to execute "Invoke-SqlCMD" PSCmdlet (v21.1.18256) via a C# project (targeting .NET 7.0), however it is throwing an error:

C#:

using System.Management.Automation;

using var ps = PowerShell.Create(RunspaceMode.NewRunspace);
ps.AddScript(
"""
Import-Module SQLServer

$query = "select 123";
$CONNECTION_STRING = "Server={SERVER};Database={DB};User Id={USER};Password={PWD};";

$params = @{
    OutputSqlErrors = $true
    IncludeSqlUserErrors = $true
    Verbose = $true
    Variable = $vars
    ConnectionString = $CONNECTION_STRING
    Query = $query
}

Invoke-SqlCMD @params
""");

var output = ps.Invoke();

Console.WriteLine(output.Count());
Console.WriteLine(ps.Streams.Error.FirstOrDefault()?.Exception);

Output:

0
System.PlatformNotSupportedException: System.Data.SqlClient is not supported on this platform.
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.CreateSqlConnection()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor..ctor(GetScriptCommand sqlCmdCmdLet)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()

If I navigate to the folder that the "SqlServer" PS module was installed in %userprofile%\Documents\PowerShell\Modules\SqlServer\21.1.18256\, and I inspect coreclr\System.Data.SqlClient.dll I see that there are PlatformNotSupportedException's hardcoded in the SqlConnection constructor. Looks like the assembly version of this DLL is 4.6.27618.01.

The strange thing is if I execute the same PS script from within a PS core (v7.3.3) terminal directly (on the same machine), then it executes w/o issue. So I'm assuming there are some binding redirects going on behind the scenes, but the same binding redirects are not happening when using System.Management.Automation.PowerShell from my .NET project.

I did end up trying to download the 4.6.1 System.Data.SqlClient package from Nuget, which had the same assembly version as the one mentioned above, and then I override the one in the coreclr folder with the one from the nuget package in the runtimes\win\lib\netstandard2.0 directory. This got me quite a bit further, but then ran across a different error:

System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.
 ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SNILoadHandle' threw an exception.
 ---> System.DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()
   at System.Data.SqlClient.SNILoadHandle..ctor()
   at System.Data.SqlClient.SNILoadHandle..cctor()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.TdsParserStateObjectFactory.get_EncryptionOptions()
   at System.Data.SqlClient.TdsParser..cctor()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(String batch)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(String str, Int32 num)
Error: : Microsoft.SqlTools.ServiceLayer.BatchParser.BatchParserException: Incorrect syntax was encountered while parsing ''.
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.RaiseError(ErrorCode errorCode, Token token, String message)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.RaiseError(ErrorCode errorCode, String message)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ExecuteBatch(Int32 repeatCount)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ParseLines()
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.Parse()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)

So as you can see, it got a lot further, but still having some issues. I ended up adding a project reference to System.Data.SqlClient v4.6.1, and then it started working w/o any exceptions. Out of curiosity, I reverted the DLL I changed in the coreclr folder back to the old one, and ran the project again, and it worked which probably means the binding redirects in place now (just a guess).

I guess my main point here is why does the PS Module ship with a System.Data.SqlClient DLL that throws the PlatformNotSupportedException, when it should just be loading the correct .NET Standard 2.0 lib? I know my use case is probably a little special, but I really had to dig and trail-and-error to figure out what needed to be done. Overall, I'd expect that this same powershell script that I used in PS Core also works from C# PS Automation, especially since they're both executing on the same machine.

microsoft / SQLServerPSModule

System.Data.SqlClient is not supported on this platform. #30