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

[ashdar]

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."

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: #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]

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]

looks like this provides some insight dotnet/SqlClient#645

[ashdar]

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:

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]

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]

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]

Same repo Chrissy linked to in her comment #9190 (comment)

[ashdar]

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

• When the dbatools.library.PSM1 code runs for PowerShell 7 on Windows, it loads the DLLs out of win-sqlclient regardless of x86 or x64.
• In the win-sqlclient directory, the Microsoft.Data.Sqlclient.DLL file is managed/IL code. So x86 and x86 can load that file.
• The critical DLL is the Microsoft.Data.Sqlclient.SNI.DLL. This is native, unmanage code. It gets accessed via p/invoke when SqlClient.DLL needs it. Sqlclient.DLL only knows SqlClient.SNI.DLL by name and assumes it is in the same folder.
• The nupkg file that the build script sources the SNI DLL file from has two copies of SqlClient.SNI.DLL. The build script copies the 'native x64' version to win-sqlclient and ignores the 'native x86' version. So, the x64 version is the only thing that ships with the dbatools.library module.
• In a x86 process, when I (or dbatools or psframework) innocently create a connection to SQL call .Open(), SqlClient.DLL tries to p/invoke the x64 file at that moment. Since the Sqlclient.DLL (which is IL) is trying to p/invoke a 64 bit copy of the SqlClient.SNI.DLL in a x86 process, the loading goes bang and fails. Which is where I came in, some months ago.
• I'm not sure what the other SNI files under 'dbatools.library\2024.3.9\core\lib\win' are used for. They reside in that folder and have a (different) copy of the SqlClient.dll file, which I guess that it's smart enough to figure out what platform it's running on and choose which of the three SNI DLL files (arm64, x64 and x86) to use. I don't think they are used for the sort of processing I am doing.

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

• Leave the existing win-sqlclient directory alone because x64 works as expected.
• Create an additional directory win-sqlclient-x86, right next to the win-sqlclient directory.
• Copy the following files into win-sqlclient-x86: Microsoft.Data.SqlClient.DLL, Microsoft.Identity.Client.DLL and the x86 version of the Microsoft.Data.Sqlclient.SNI.DLL. That file is sourced from ".\nuget\Microsoft.Data.SqlClient.SNI.runtime.5.2.0\runtimes\win-x86\native\Microsoft.Data.SqlClient.SNI.dll"
• Modify the PSM1 file so it uses the win-sqlclient-x86 files when in a 32 bit session and otherwise continues to use win-sqlclient .

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]

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:

dataplat/dbatools.library#10

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

[ashdar]

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

[potatoqualitee]

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