search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.44k stars 796 forks source link

Invoke-DbaQuery error: An attempt was made to load a program with an incorrect format. (0x8007000B) #9190

Closed ashdar closed 4 months ago

ashdar commented 9 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

$error[0] | select *

PSMessageDetails      :
Exception             : System.Exception: An attempt was made to load a program with an incorrect format. (0x8007000B)
---> System.Exception: Error connecting to [vdevsql01]: An attempt was made to load a program with an incorrect
format. (0x8007000B)
---> System.Management.Automation.MethodInvocationException: Exception calling "ExecuteWithResults" with "1"
argument(s): "Failed to connect to server vdevsql01."
---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server vdevsql01.
---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (0x8007000B)
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted(IntPtr token, Boolean& isRestricted)
at Microsoft.Data.Win32NativeMethods.IsTokenRestrictedWrapper(IntPtr token)
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrentNative()
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrent()
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey
poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection,
DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,
TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection,
DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,
DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,
DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at CallSite.Target(Closure, CallSite, Object, String)
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception
exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
TargetObject          : vdevsql01
CategoryInfo          : ConnectionError: (vdevsql01:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaQuery,Stop-Function
ErrorDetails          : An attempt was made to load a program with an incorrect format. (0x8007000B)
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 97912
at Invoke-DbaQuery<Process>, <No file>: line 52405
at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference
# start a -noconsole PowerShell 7.4 x86 console
ipmo dbatools -PassThru
 $error #no errors yet
Invoke-DbaQuery -SqlInstance vdevsql01 -Query 'select @@servername WhereAmI, getdate() RightNow'

$error #now, we have errors
Write-Error:
 Line |
52405 |  …             Stop-Function -Message "Failure" -ErrorRecord $_ -Target  …
      |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      | An attempt was made to load a program with an incorrect format. (0x8007000B)
ConnectionError:
 Line |
97904 |          throw $records[0]
      |          ~~~~~~~~~~~~~~~~~
      | Error connecting to [vdevsql01]: An attempt was made to load a program with an incorrect format. (0x8007000B)
MethodInvocationException:
Line |
3355 |  …             $null = $server.ConnectionContext.ExecuteWithResults("SEL …
     |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "ExecuteWithResults" with "1" argument(s): "Failed to connect to server vdevsql01."

image

Please confirm that you are running the most recent version of dbatools

I am running 2.1.5 and I took particular care to remove any old versions of dbatools that were on my systems.

Other details or mentions

The problem does not occur with Windows PowerShell 5.1 x86 or 86, nor with PowerShell 7.4 x64. I am stuck using 32 bit consoles because my client has important data in an antique format with only 32 bit OLEDB drivers.

This problem keeps me from fully moving to PowerShell 7.4. I have to maintain twice as many PowerShell environments as I would otherwise need to do.

I use PSFramework logging with a SQL Server target, and this shows up there too because PSFramework uses dbatools for SQL access. IOW, I can't use PSFramework logging in a PowerShell 7.4 x86 process.

I can reproduce the same problem on two different workstations, one is Windows 10 and the other is Windows 11. I can reproduce the problem when connecting to any SQL Server, the version doesn't seem to matter because we never get to actually connecting.

A year ago, I opened the same bug: https://github.com/dataplat/dbatools/issues/8638 It seemed to be fixed for the then-unreleased version 2.0. So, that bug was closed and I started waiting for the release. One thing led to another and I didn't get a chance to update my third-party modules until now, the end of the year, and I seem to have the same problem.

As far as .NET versions go, do I need to have exactly 4.6.2 or is 4.8.09037 good enough?

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

PSVersion 7.4.0 PSEdition Core GitCommitId 7.4.0 OS Microsoft Windows 10.0.19045 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

I can reproduce the problem when connecting to any SQL Server, the version doesn't seem to matter because we never get to actually connecting.

.NET Framework Version

output from first method (looks at get_FrameworkDescription()

.NET 8.0.0

2nd method (looks at the Registry)

PSChildName Version

Client 4.8.09037 Full 4.8.09037 Client 4.0.0.0

potatoqualitee commented 9 months ago

I've seen this issue pop up and I don't know how to fix it :/ DLLs aren't my forte but i'm pretty sure this is an issue with SqlClient SNI as that has x86 in the filename.

potatoqualitee commented 9 months ago

looks like this provides some insight https://github.com/dotnet/SqlClient/issues/645

ashdar commented 9 months ago

I've spent a couple of hours digging through this, and I feel like I know less now than when I started. :-/

I believe that what I read was that the transition from System.Data.SqlClient->Microsoft.Data.SqlClient, SNI was moved from being a stand-alone DLL ("sni.dll") to being included within Sql client ("Microsoft.Data.SqlClient.SNI.dll") . Also, from the function names in the stack trace ("UnmanagedIsTotkenRestricted"), I get the feeling that we are calling out of .NET and into old-school Windows. If you look at the code for SNINativeWrapper, it's definitely an extern/DllImport. That seems to make things more confusing.

But, a few things: First thing is that, if I look at my "PowerShell\Modules\dbatools.library\2023.9.21\desktop\lib" folder, I see this: image

the "SNI.dll" and "SNI.X64.dll" files are exactly the same size, implying they are really two copies of the same file. If the x86 code is naively looking for "SNI.dll", expecting it to be x86, that's probably a problem. I did rename that SNI.dll file out of the way and retested, with no observable difference-it still throws in Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted().

Second thing is that I see that there is a "sni.dll" file in the PowerShell (7) files. When we looked at this issue last year, you didn't seem to see the problem in your 32 bit test environment. If that is so, is there some way I can compare what I have in my environment with the test environment? There has to be some kind of difference, maybe in the way or order in which DLLs are found on a Windows computer?

The third thing is that I now have a better repro script. It's built from the guts of Connect-DbaInstance, but it relies on the DLLs loaded by Import-Module dbatools. The new repro script has the same failure and the stack trace is the same.

# Here is a simpler repro script, taken from the guts of Connect-DbaInstance.
# The actual failure point is line 3355 in Connect-DbaInstance (dbatools version 2.1.5), in the "merged" "Script Listing" PS1 file.
import-module dbatools

$ServerName = "(localdb)\MSSqlLocalDb"

# $Query = "SELECT 'repro script is opening a new connection'"
$Query = "select @@servername WhereAmI, getdate() RightNow"
$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$sqlConnection = New-Object -TypeName Microsoft.Data.SqlClient.SqlConnection -ArgumentList $sqlConnectionInfo.connectionString
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sqlConnection
# this next line fails
$server.ConnectionContext.ExecuteWithResults($Query).Tables[0]
wsmelton commented 9 months ago

One thing to consider @ashdar if you want to fully convert to PowerShell 7.4 x64 is isolating your need for the x86 via Start-Process cmdlet. That would allow you to bring the data unique to the old library and architecture into a more performant x64 session for your PowerShell script. You would likely grab some performance improvement for your customer as well.

The library version we have from Microsoft is all Microsoft-owned code. The repo script you provided is what you can provide to their repository for issue tracking; it is an upstream issue in that regard. At this point all we can do is see if they want to fix it or not.

I do believe your best option is to move that data interaction required with those x86 drivers to an isolated process.

ashdar commented 9 months ago

I am not sure that going from 32 to 64 will help my process performance, but I am sure that going from PowerShell 5.1 to PowerShell 7 will help and I can't get to 7 without doing something about my 32 bit problem. Are you thinking of something like the PSSession scheme mentioned here? Or something else?

Can you point me to the correct repository to file an issue? Microsoft.SqlServer.Management or Microsoft.Data.SqlClient?

wsmelton commented 9 months ago

Same repo Chrissy linked to in her comment https://github.com/dataplat/dbatools/issues/9190#issuecomment-1867447451

ashdar commented 5 months ago

Hi. After a hiatus, I spent some time looking at this problem again over the weekend. I believe the issue revolves around these observations:

I believe that we can fix the problem described by this ticket with the following:

I have proven that, if I copy the Microsoft.Data.SqlClient.DLL, Microsoft.Identity.Client.dll and x64 version of the Microsoft.Data.SqlClient.SNI.DLL file into a directory, I can import-module 'Microsoft.Data.SqlClient.DLL' and everything works as I expect.

(FWIW, this combination of DLLS, with the x86 version of SNI.DLL and the IL versions of the other two DLLs, fails in a x64 PowerShell process.)

I have a first-pass at a fix of the dbatools.library code in my forked copy of the dbatools.library project. I can't test it properly because I'm having a lot of trouble getting the dbatools.library build scripts to work as I would expect on my local computer and I'm not familiar with how the Workflow stuff works, especially for a forked project, so I'm roadblocked..

So, two questions:

  1. Can someone help me with proving that this is a correct and good fix?
  2. Should I start an issue over in the dbatools.library project and link to it from here?
ashdar commented 5 months ago

Since this is pretty clearly a dbatools.library problem and not a more general dbatools problem, I've written up a new ticket with my research and suggtions. That ticket is here:

https://github.com/dataplat/dbatools.library/issues/10

I think that we can problem close this #9190 ticket and track this issue on the #10 ticket.

ashdar commented 4 months ago

Closing this ticket in favor of the ticket over in the dbatools.library project.

potatoqualitee commented 4 months ago

thank you! i'll take a look when my brain is working ..and update wh ile im at it.