Native libraries not loaded in C# interactive

[mrmonday]

Using the following project (SqlClientInteractive.csproj):

<?xml version="1.0" encoding="utf-8"?>
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net48</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" />
  </ItemGroup>
</Project>

Code (Conn.cs):

using System;
using Microsoft.Data.SqlClient;
//using System.Data.SqlClient;

namespace SqlClientInteractive
{
    public class Conn
    {
        public static void Open()
        {
            try
            {
                var conn = new SqlConnection("Data Source=zzzzzzzzzzzz; Connection Timeout=1");
                conn.Open();
            }
            catch (SqlException)
            {
            }
        }
    }
}

In Visual Studio (tested with 2019, v16.6.5), right click the project, then "Initialize Interactive with Project". Run Conn.Open().

Using System.Data.SqlClient, this works correctly.

When using Microsoft.Data.SqlClient, this gives the following exception:

> Conn.Open()
System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.
  + Microsoft.Data.SqlClient.TdsParser..ctor(bool, bool)
  + Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Microsoft.Data.SqlClient.ServerInfo, string, System.Security.SecureString, bool, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, Microsoft.Data.ProviderBase.TimeoutTimer)
  + Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(Microsoft.Data.ProviderBase.TimeoutTimer, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, string, System.Security.SecureString, bool)
  + Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(Microsoft.Data.ProviderBase.DbConnectionPoolIdentity, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, object, string, System.Security.SecureString, bool, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SessionData, Microsoft.Data.SqlClient.ServerCertificateValidationCallback, Microsoft.Data.SqlClient.ClientCertificateRetrievalCallback, Microsoft.Data.ProviderBase.DbConnectionPool, string, Microsoft.Data.SqlClient.SqlClientOriginalNetworkAddressInfo, bool)
  + Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, object, Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions)
  + Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, Microsoft.Data.Common.DbConnectionOptions)
  + Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
  + Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
  + Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, uint, bool, bool, Microsoft.Data.Common.DbConnectionOptions, out Microsoft.Data.ProviderBase.DbConnectionInternal)
  + Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, TaskCompletionSource<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, out Microsoft.Data.ProviderBase.DbConnectionInternal)
  + Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection, TaskCompletionSource<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal, out Microsoft.Data.ProviderBase.DbConnectionInternal)
  + Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection, Microsoft.Data.ProviderBase.DbConnectionFactory, TaskCompletionSource<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions)
  + Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource<Microsoft.Data.ProviderBase.DbConnectionInternal>)
  + Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.SqlClient.SqlConnectionOverrides)
  + Microsoft.Data.SqlClient.SqlConnection.Open(Microsoft.Data.SqlClient.SqlConnectionOverrides)
  + SqlClientInteractive.Conn.Open()

Debugging gives the following:

Inner Exception 1:
TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception.

Inner Exception 2:
DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

As part of a larger test case, I am able to work around this with the following snippet - unfortunately this does not seem to work for the reduced test case as given here:

var assembly = System.Reflection.Assembly.GetAssembly(typeof(Microsoft.Data.SqlClient.SqlConnection));
var sqlClientDir = Path.GetDirectoryName(new Uri(assembly.Location).LocalPath);
var userProfile = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
var nativeName = IntPtr.Size == 8 ? "Microsoft.Data.SqlClient.SNI.x64.dll" : "Microsoft.Data.SqlClient.SNI.x86.dll";
var nativeLocation = Path.Combine(userProfile, @".nuget\packages\microsoft.data.sqlclient.sni\2.0.0\build\net46\", nativeName);
File.Copy(nativeLocation, Path.Combine(sqlClientDir, nativeName));

The native library seems to be loaded correctly in web and console applications, but not in an interactive shell.

[karinazhou]

Hi @mrmonday ,

I tried with your csproj file and is able to reproduce it locally.

For System.Data.SqlClient, it's part of System.Data.dll which is included with .NET Framework. And it doesn't depend on SNI.dll explicitly. For Microfost.Data.SqlClient, it's a separate nuget package and depends on another Microsoft.Data.SqlClient.SNI nuget package.

We have similar issues loading native SNI.dll when using Microsoft.Data.SqlClient with F#:
#645
#292

For web and console app, the dependency is handled by nuget package manager and the required SNI.dll is copied along with driver in the bin folder. If you execute the binary from bin, it works without error. However, this does not work with the interactive window in Visual Studio which cannot load SNI.dll even though it's already in the bin folder.

I tried to add Microsoft.Data.SqlClient.SNI as package reference to the csproj but that doesn't work either.

---

In addition, I tried to add SNI.dll with #r command but it doesn't work with native assembly:

#r "C:\Users\karinaz\.nuget\packages\microsoft.data.sqlclient.sni\2.0.0\build\net46\Microsoft.Data.SqlClient.SNI.x64.dll"
(1,1): error CS0009: Metadata file 'C:\Users\karinaz\.nuget\packages\microsoft.data.sqlclient.sni\2.0.0\build\net46\Microsoft.Data.SqlClient.SNI.x64.dll' could not be opened -- PE image doesn't contain managed metadata.

After I manually copied the SNI.dll to the Directory.GetCurrentDirectory(), the c# interactive shell can find the native dll properly.

[isaacabraham]

@karinazhou Have a look here - it's for F# scripts but this solution might work here as well.

[karinazhou]

@isaacabraham Thank you for sharing your blog.
To explain a bit, the Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows is to let the driver use managed SNI instead of loading native SNI.dll and this is why it can eliminate the "SNI.dll not found" exception. This workaround is applicable for .net core application only.

[Adebeer]

We have similar issue in Microsoft Service Fabric - after upgrading to version 2.0.0/1, our microservices are throwing this exception:

Path: D:\SvcFab\_App\ANGen.AnglePlusType_App825\BootstrapperPkg.Code.1.0.0.dev.20200825.2\Bootstrapper.exe
                     Message: Error:
                       An assembly specified in the application dependencies manifest (Bootstrapper.deps.json) was not found:
                         package: 'Microsoft.Data.SqlClient.SNI.runtime', version: '2.0.0'
                         path: 'runtimes/win-x64/native/Microsoft.Data.SqlClient.SNI.pdb'

These are all using .NET Core 3.1

[cheenamalhotra]

Hi @Adebeer

As discussed in #644 we will be removing these symbols from SNI runtime NuGet package in future.
Please follow that issue for further updates.

[cheenamalhotra]

Closing this issue as native SNI support for C# interactive cannot be configured with SNI targets.
The possible solutions are:

• Configuring own targets for C# interactive to change SNI copy location.
• Or, Enable Managed SNI on windows with app context switch Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows

Regarding following discussion, pdbs have been removed from Microsoft.Data.SqlClient.SNI.runtime v2.1.1 to be picked by Microsoft.Data.SqlClient v2.1.0-preview2 onwards.

[hietshkumar10]

crosoft.Data.SqlClient.UseManagedNetworkingOnWindow

But this is not recommended by Microsoft