Test Connect Sql Server dotnet6 'SNIAddProvider' exception

[OzBob]

Describe the bug

local SQL Server DB, using grate.exe (64bit self contained) throws

To Reproduce 1 Download, unblock, unzip: grate-win-x64-self-contained-1.4.0 zip 2 run local example with winget commented out, and using local grate.exe

Expected behavior SNIAddProvider exception

Screenshots console out:

Unhandled exception: System.TypeLoadException
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIAddProvider(SNIHandle pConn, ProviderEnum ProvNum, AuthProviderInfo& pInfo)
   at Microsoft.Data.SqlClient.TdsParserStateObjectNative.EnableSsl(UInt32& info, Boolean tlsFirst)
   at Microsoft.Data.SqlClient.TdsParser.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.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, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at grate.Migration.AnsiSqlDatabase.Open(DbConnection conn)
   at grate.Migration.AnsiSqlDatabase.OpenNewActiveConnection()
   at grate.Migration.GrateMigrator.CreateGrateStructure(IDbMigrator dbMigrator)
   at grate.Migration.GrateMigrator.Migrate()
   at grate.Commands.MigrateCommand.<>c__DisplayClass0_0.<<-ctor>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
   at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<<UseParseErrorReporting>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__18_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseParseDirective>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__5_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<<UseExceptionHandler>b__0>d.MoveNext()

Desktop (please complete the following information):

• OS: Windows10
• win64 version of grate.exe

Additional context 1 dotnet tool install grate, installs 1.4.0, if reference that version of grate, then no errors.

2 Not sure if this is related and it is my local system causing problems, but I tried building the source code locally but with a global.json specifiying to use my SDK of 6.0.400, the buildtime exception for

dotnet publish -c Release -r win-x64 -p:PublishReadyToRun=true --self-contained

is:

grate\Commands\MigrateCommand.cs(41,23): error CS0103: The name 'CommandHandler' does not exist in the current context

[wokket]

Are you really downloading the linux package and running on Windows 10?

I just ran both the local example as committed and it worked fine.

I then winget uninstall erikbra.grate, downloaded the winx64 self-contained package, extracted and unblocked the exe to the example folder, updated the ps1 to remove the winget install, and changed grate to .\grate to appease the PS gods, and that worked fine too.

If I've missed a step or something feel free to zip up a repro folder and I can try and run it on other machines?

[OzBob]

@wokket I did actually download the 'grate-win-x64-self-contained-1.4.0' zip. I copied the link in the issue incorrectly.

I think you also need to change the grate to .\grate.exe to really appease the PS gods. Maybe uninstall winget version and dotnet tools version so you can be sure it's not 'falling back' to them.

[wokket]

I had uninstalled the other versions mate. I have no doubt there's an issue of some sort there, but if I can't repro it there's not much I can do :(

[OzBob]

perfectly understandable. I note that the grate.exe for the grate-win-x64-self-contained-1.4.0 zip is only 21MB is the build doing tree trimming to make it so small. Could I be missing local DLL's that it's dependant on?

Maybe it is related to my inability to build the source code locally too.

What are the last two lines on your build PC, for: dotnet --list-sdks and dotnet --list-runtimes

I've added a new global.json at the root of the repo folder on my local win10 PC running VS2022, as I have dotnet 7 sdk installed, '7.0.100-preview.7.22377.5.

I've forked the grate repo, copied the global.json to use 6.400, did a dotnet restore and dotnet build using: dotnet publish -c Release -r win-x64 -p:PublishReadyToRun=true --self-contained

which produces 133MB of dll files. using this grate.exe works. Substiuting in the v1.4.0 grate.exe does not but at least gives a different error message of:

    Unhandled exception: System.TypeLoadException: Method 'get_IsAuthenticated' in type 'System.Security.Claims.ClaimsIdentity' from assembly 'System.Security.Claims, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' does not have an implementation.
   at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrentNative()
   at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrent()111

[erikbra]

Hi, @OzBob , it's not good to know if this is a problem with your computer or grate. As you say, we do trim the assemblies, and there might be corner cases where the trimming trims a bit too much.

If you want to build it properly "self-contained" yourself, you should also set the -p:SelfContained=true too, as in:

https://github.com/erikbra/grate/blob/main/.github/workflows/build.yml#L93

there are a couple of extra properties that need to be set, in addition to the --self-contained command-line flag, and I've handled this with conditional properties in the .csproj, using the SelfContained msbuild property:

https://github.com/erikbra/grate/blob/main/grate/grate.csproj#L47

On what form is your connection string when you try to run it? Are you using any special features? Could it be that you are using some form of authentication, etc, that could make it fail with the SNIAddProvider exception?

[erikbra]

There are some SNI related troubleshooting tips here, I don't know if they are applicable, but at least is looks like SNI (Server Name Indication, I only just learnt that) is dependent on some native DLLs:

https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver16#issues-in-net-core-applications

[rmegal]

I am not using grate but received the same error when using "Trim unused code" in my build. I think @erikbra is on to something. The error disappeared when I quit using "Trim unused code", which is apparently too aggressive, at least in this case.

[OzBob]

build without -p:PublishReadyToRun=true --self-contained and the grate.exe does not have the TypeloadException. SNINativeMethodWrapper.SNIAddProvider error

[PagesplitPeter]

I found this project today and was looking to compare it to DBUP. I installed it with winget and I recive the same error as OzBob. I thought I had the connectionstring wrong.

Will uninstall it with winget anddownload the selfcontained package so that I can test this project

[TCN-CW]

I am having the same issue when using v1.4.0 (even with the selfcontained package). I have reverted to using v1.2.0 for now until this is resolved.

[erikbra]

Fixed by #326