search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

Invoke-SqlCmd HttpProvider error or Fatal error. System.AccessViolationException #53

Closed sicollins closed 4 days ago

sicollins commented 12 months ago

In it's most basic form, if I try running the following in Windows Terminal (latest Powershell 7.3.5):

Invoke-SqlCmd -Query "select 1" -TrustServerCertificate

It can cause either of the following errors:

Invoke-Sqlcmd: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - )
Invoke-Sqlcmd: Incorrect syntax was encountered while parsing ''.

or

Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Repeat 2 times:
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO ByRef, IntPtr ByRef)
...

Windows: 11 (22H2) Powershell: 7.3.5 SqlServer powershell module: 22.1.1 SQL Develop Edition: 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64) .NET SDKs installed:

Shared Memory and TCP/IP are both enabled in SQL Server Configuration Manager and the local SQL instance is configured to allow remote connections and I can easily connect to it In SSMS and Azure Data Studio without any issues using "localhost", "(local)" or using ip "127.0.0.1,1433".

Have tried downgrading to older versions of the SqlServer powershell module - last time I had it all working a few weeks ago I was on v 21.1.18256 but even this now fails.

Anyone got any ideas what's going on here?

zbrkic commented 7 months ago 
PowerShell 7.3.9

❯ Get-Module SqlServer -ListAvailable

    Directory: C:\Users\zbrkic\Documents\PowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     22.1.1                SqlServer                           Core,Desk {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListe…
❯ Invoke-SqlCmd -Query "select 1"
Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Repeat 2 times:
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO ByRef, IntPtr ByRef)
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncEx(ConsumerInfo, System.String, IntPtr ByRef, Byte[], Byte[], Boolean, Boolean, Int32, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, System.String)
   at Microsoft.Data.SqlClient.SNIHandle..ctor(ConsumerInfo, System.String, Byte[], Boolean, Int32, Byte[] ByRef, Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, Boolean, System.String)
   at Microsoft.Data.SqlClient.TdsParserStateObjectNative.CreatePhysicalSNIHandle(System.String, Boolean, Int64, Byte[] ByRef, Byte[][] ByRef, Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, System.String, Microsoft.Data.SqlClient.SQLDNSInfo ByRef, System.String, Boolean, Boolean, System.String)
   at Microsoft.Data.SqlClient.TdsParser.Connect(Microsoft.Data.SqlClient.ServerInfo, Microsoft.Data.SqlClient.SqlInternalConnectionTds, Boolean, Int64, Microsoft.Data.SqlClient.SqlConnectionString, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.ProviderBase.TimeoutTimer, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, Microsoft.Data.ProviderBase.TimeoutTimer)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(Microsoft.Data.ProviderBase.TimeoutTimer, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, System.String, System.Security.SecureString, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(Microsoft.Data.ProviderBase.DbConnectionPoolIdentity, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, System.Object, System.String, System.Security.SecureString, Boolean, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SessionData, Boolean, System.String, Microsoft.Data.ProviderBase.DbConnectionPool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, System.Object, Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions)
   at 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)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, UInt32, Boolean, Boolean, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection, Microsoft.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection, Microsoft.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.SqlClient.SqlConnectionOverrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(Microsoft.Data.SqlClient.SqlConnectionOverrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(System.String)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(System.String, Int32)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.Go(Microsoft.SqlTools.ServiceLayer.BatchParser.TextBlock, Int32, Microsoft.SqlTools.ServiceLayer.BatchParser.SqlCmdCommand)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ExecuteBatch(Int32)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ParseLines()
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.Parse()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(System.String)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
   at System.Management.Automation.CommandProcessor.ProcessRecord()
   at System.Management.Automation.CommandProcessorBase.DoExecute()
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(System.Object)
   at System.Management.Automation.PipelineOps.InvokePipeline(System.Object, Boolean, System.Management.Automation.CommandParameterInternal[][], System.Management.Automation.Language.CommandBaseAst[], System.Management.Automation.CommandRedirection[][], System.Management.Automation.Language.FunctionContext)
   at System.Management.Automation.Interpreter.ActionCallInstruction`6[[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Boolean, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]].Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.Interpreter.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.LightLambda.RunVoid1[[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]](System.__Canon)
   at System.Management.Automation.DlrScriptCommandProcessor.RunClause(System.Action`1<System.Management.Automation.Language.FunctionContext>, System.Object, System.Object)
   at System.Management.Automation.DlrScriptCommandProcessor.Complete()
   at System.Management.Automation.CommandProcessorBase.DoComplete()
   at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(System.Management.Automation.CommandProcessorBase)
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(System.Object)
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeHelper()
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()
   at System.Management.Automation.Runspaces.PipelineThread.WorkerProc()
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)

[process exited with code 3221225477 (0xc0000005)]
You can now close this terminal with Ctrl+D, or press Enter to restart.

Related issue: https://github.com/microsoft/SQLServerPSModule/issues/42#issue-1666823368

The same error with SqlServer 22.1.1 Had to downgrade to SqlServer 21.1.18256

Is this some dependencies issue? Can you fix those problems instead of just ignoring/closing such bugs?

zbrkic commented 7 months ago

After a bit more troubleshooting:

Microsoft.Data.SqlClient.dll, Version 5.00.0.0 (VS Modules Window): C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll

Microsoft.Data.SqlClient.SNI.dll not found where expected, and loaded from some random folder traversing paths:

image

Obviously incompatible version is loaded.

Folder C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1 contains two folders: win-x64 and win-x86, both containing Microsoft.Data.SqlClient.SNI.dll.dll (double .dll).

Issue fixed by manually copying Microsoft.Data.SqlClient.SNI.dll.dll from win-x64 to C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1 and renaming to Microsoft.Data.SqlClient.SNI.dll

After that, the following command works: Invoke-SqlCmd -Query "select 1" -TrustServerCertificate

Matteo-T commented 6 months ago

Hi @zbrkic - I hear you. I've always been puzzled by the fact I had to change the extension (double ".dll") and the location) of PWSH on my machines. I even tried to follow up with the PowerShell guys directly, but I did not go very far.

This is what I see right now on my machine when I attach a debugger:

Microsoft.Data.SqlClient.SNI.dll.dll    Microsoft.Data.SqlClient.SNI.dll.dll    C:\Users\matteot\Documents\PowerShell\Modules\SqlServer\22.1.11208\coreclr\runtimes\win\lib\netcoreapp3.1\win-x64\Microsoft.Data.SqlClient.SNI.dll.dll  N/A Yes Automatic symbol loading is disabled.       194 5.00.1.0    10/3/2022 2:42 PM   00007FFEBD850000-00007FFEBD8D5000   [21004] pwsh.exe

I agree that what you had to do should be the correct way Windows/PWSH should be looking for the DLL when the Microsoft.Data.SqlClient is trying to load it... What I find it incredibly disturbing is that you are the only user that is complaning (for the right reason - don't get me wrong) about it.... while the odd "double .dll" works for all the other users out there.

I'm not exactly sure what to do... other than ship 2 other SNI DLLs (single ".dll" and different folder) to mitigate the issue, in case you are not alone out there...

zbrkic commented 6 months ago

@Matteo-T Thanks for checking this. I would not say that "double .dll" works for other users. I think they are lucky enough that random version of DLL found in some other folder (after not found in the one where it should be) is compatible with the version needed. Some also downgrade and then random version of DLL found also works.

Matteo-T commented 6 months ago

Well, while I confirm that the "double .dll" is odd... I would not call the location it is getting (the right) DLL "random": it's the intended location where the DLL is.

The module must packages native DLLs which happen to have the same name (Microsoft.Data.SqlClient.SNI) in different folders, so that the same module can work on different architetures. In other words, I have 2 (soon 3) DLLs with the same name that PWSH.EXE needs to dynamically load based on its process architecture.

Do you see any other folder being probed on your ProcMon that happens to be architecture-specific? The picture you attached is too partial, so I cannot see what may be happening above or below it.

On my machine I always see the .dll.dll being loaded from the "win-x64", as expected (well, as I said, I did not expect the double ".dll" - but the 'win-x64' seems reasonable when I'm on an x64 machine.

I've reashed the conversation with both the Microsoft.Data.SqlClient team and the PowerShell Team to see if I can get to the bottom of this. On all my machines, even the simplest PowerShell module that uses Microsoft.Data.SqlClient behaves like that...

zbrkic commented 6 months ago

Hi, sorry if it was not clear what I meant. Let me explain what I meant by random. When Microsoft.Data.SqlClient.SNI.dll is not found in C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1, it is searched in other folders defined by Windows Path ($Env:path). In my case it is found here C:\Program Files\Seq\Microsoft.Data.SqlClient.SNI.dll and it is not compatible with Microsoft.Data.SqlClient.dll in C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1 .

Maybe if I had newer version of Seq installed, it would be compatible. For some other user it can be found in some other "random" folder, depending on programs installed and Path. Found version may be compatible or not. If not compatible, user will have a problem.

When I remove C:\Program Files\Seq\Microsoft.Data.SqlClient.SNI.dll, I cannot see in Process Monitor that Microsoft.Data.SqlClient.SNI.dll is found anywhere, but I can see in Process Explorer that Microsoft.Data.SqlClient.SNI.dll.dll is loaded from C:\Users\zbrkic\Documents\PowerShell\Modules\SqlServer\22.1.1\coreclr\runtimes\win\lib\netcoreapp3.1\win-x64.

So, if user has Microsoft.Data.SqlClient.SNI.dll somewhere in the path, it will try to load it first, and not try to load ".dll.dll". That Microsoft.Data.SqlClient.SNI.dll could be in some random folder.

Mike-E-angelo commented 5 months ago

WOW! What an incredibly complex problem. Thank you so much @zbrkic (edit: I didn't have the right tag! 🤦‍♂️) for taking the time to document your thoughts. This has been a headscratcher for me for about a day now, and landed on this thread after some careful searching. I ran into the exact problem and installing the latest Seq Server fixed the problem for me. What an ordeal. 💥

Matteo-T commented 4 days ago

I'm not sure there's much I can do. I suspect it's the logic in the Microsoft.Data.SqlClient.dll that loads the SNI dll... so I'm not sure I have a way to disallow that.

I'm closing this issue for now.

Matteo-T commented 4 days ago

Really closing as By Design.