search

microsoft / SQLServerPSModule

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

Invoke-SqlCmd raises System.AccessViolationException in PS 7.3.3 for SqlServer 22.0.59 #42

Closed ronascentes closed 1 year ago

ronascentes commented 1 year ago

Steps to reproduce the issue:

SqlServer 22.0.59 Windows PowerShell 5.1 Windows Server 2022 Standard (21H2)

Windows PowerShell
PS > Import-Module SqlServer
PS> Invoke-sqlcmd -Query "SELECT 1" -ServerInstance "servername.domain.com" -TrustServerCertificate -MultiSubnetFailover -Encrypt Mandatory

Column1
-------
      1

SqlServer 22.0.59 PowerShell 7.3.3 Windows Server 2022 Standard (21H2)

PowerShell 7.3.3
PS > Import-Module SqlServer
PS> Invoke-sqlcmd -Query "SELECT 1" -ServerInstance "servername.domain.com" -TrustServerCertificate -MultiSubnetFailover -Encrypt Mandatory
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)

Screenshot for reference:

image

This issue does not occur if using SqlServer module 21.1.18256

SqlServer 21.1.18256 PowerShell 7.3.3 Windows Server 2022 Standard (21H2)

PowerShell 7.3.3
PS > get-Module sqlserver -ListAvailable

    Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     21.1.18256            sqlserver                           Desk      {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenticationContext…}

PS > import-Module SqlServer
PS > invoke-sqlcmd -Query "SELECT 1" -ConnectionString "Data Source=server.domain.com;Initial Catalog=ServerInfo;Integrated Security=True;MultiSubnetFailover=True;Connection Timeout=120;Encrypt=True;TrustServerCertificate=True"

Column1
-------
      1
Matteo-T commented 1 year ago

@ronascentes - hard to tell without a repo (that I can run).

Is your repro minimal? Are all the paramters necessary to repro the issue? What happens if you remove one or more if -TrustServerCertificate -MultiSubnetFailover -Encrypt Mandatory?

This looks more like a bug in the Microsoft.Data.SqlClient driver to me, so we should try to isolate the problem.

Would you be able to write a simple .Net app (C# or PowerShell) that would just try to open a connection to your server and see what happens? If not, I can probably suggest one for you as a starting point...

Is your server on-prem or Azure (SQLDB, MI, etc)?

It is expected that the old v21 module and v22 module behaves differently because they use completely different drivers to connect to SQL.

Matteo-T commented 1 year ago

@ronascentes - few more questions:

  1. Could you provide the output of [System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object Location | Sort-Object -Property FullName | Select-Object -Property FullName, Location after the issue happens?

  2. Could you attach a debugger (e.g. Visual Studio) to pwsh.exe when the issue happens and see what version of Microsoft.Data.SqlClient.SNI is actually loaded and from what location?

  3. Is this still an issue in v22.1?

ronascentes commented 1 year ago

Hi @Matteo-T, this issue no longer persists in v22.1. I'm using on-prem btw. Do you still need any other info or can we close this issue?

Matteo-T commented 1 year ago

Hi @ronascentes thanks for letting me know. Yeah, I agree... let's go ahead and close it.

ronascentes commented 1 year ago

Thank you Matteo.