An attempt was made to load a program with an incorrect format. (0x8007000B)" problem, but with more research

[ashdar]

Summarizing the other ticket
This is a continuation of the bug first reported over in the 'main' dbatools issues.
Originally, I thought this was a dbatools problem, but I now believe that it is isolated to dbatools.library.

The bug is that this error will be thrown when you try pretty much anything with dbatools in a 32 bit PowerShell 7 process:

"System.Exception: An attempt was made to load a program with an incorrect format. (0x8007000B)" problem. 

This error occurs when you try to run dbatools (or anything that relies on dbatools connectivity, such as PSFramework's logging facility, which is how I got into this) in x86 PowerShell 7. The problem does not occur in x64 PowerShell 7 or in PowerShell 5 (x86 or x64).

I believe that the bug occurs because one of the three files that is copied into the "win-sqlclient" folder by the build system is native x64 and not IL. The name of that file is "Microsoft.Data.SqlClient.SNI.dll" and it is sourced by dbatools.library build script from ".\nuget\Microsoft.Data.SqlClient.SNI.runtime.5.2.0\runtimes\win-x64\native". Code in in the dbatools.library.psm1 files loads these three DLLs regardless of whether the process ix x64 or x86. So, the native x64 file works OK in a x64 process but fails in a x86 process. Easy-peasy.

I believe we can fix this bug by doing the following

1. Adding a new win-sqlclient-x86 folder to the dbatools.library, right next to the existing win-sqlclient folder.
2. Copying the Microsoft.Data.SqlClient.dll and Microsoft.Identity.Client.dll files from the nupkg to win-sqlclient-x86, just as the build script has been doing, since they are both IL files.
3. Adding a copy of "Microsoft.Data.SqlClient.SNI.dll" BUT sourced from ".\nuget\Microsoft.Data.SqlClient.SNI.runtime.5.2.0\runtimes\win-x86\native"
4. Having the dbatools.library.psm1 file determine if we are running in a x64 or x86 process and then referencing the win-sqlclient or win-sqlclien-x86 folder, as required.

I've proven most of the pieces of this and I'm confident that this is a workable fix BUT I can't get the build scripts to give me what I think I want, which is a nupkg that I can deploy on my local test system. I'm not sure how to prove this works within the confines of GitHub's Workflow system, or if that even works on a forked project. (At this point, I only have experience with Azure DevOps's pipelines running on-prem. The last time I tried running build/test scripts for dbatools was when you guys were still using AppVeyor.)

For more details
dataplat/dbatools#9190

Shortest Repro Script Yet

try {
    if ([System.Environment]::Is64BitProcess) {
        $Message = "This is a x64 process"
    }
    else {
        $Message = "This is a x86 process"
    }
    Write-Verbose -Verbose $Message

    Import-Module dbatools.library -Verbose

    $SqlClientConnection = New-Object Microsoft.Data.SqlClient.SqlConnection
    $SqlClientConnection.ConnectionString = 'SERVER=(localDb)\MSSqlLocalDb;Integrated Security=True'

    <#
    This next line fails in x86 but works as expected in x64. The error message is:
    Exception calling "Open" with "0" argument(s): "An attempt was made to load a program with an incorrect format. (0x8007000B)
    #>
    $SqlClientConnection.Open()

    ## running sample query. We never get this far under x86, but works as expected in x64
    $query = 'select 1'
    $cmd = New-Object Microsoft.Data.SqlClient.SqlCommand($query, $SqlClientConnection)

    $ds = New-Object System.Data.DataSet
    (New-Object Microsoft.Data.SqlClient.SqlDataAdapter($cmd)).fill($ds) | Out-Null
    $ds.Tables[0]
}
catch {
    Throw
}

[potatoqualitee]

@ashdar thank you for the detailed writeup! I am a bit limited in this realm as it takes a great deal of time to figure out.

Often, I will run into issues similar to you, and end up in DLL Hell. I'm happy to accept a PR with the build scripts that fixes the issue but will likely not prioritize the issue for a while (others may but I tend to do most of the library)