dataplat/dbatools.library

dbatools smo library builder

dbatools.library's Issues

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

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
}

Module not loading

Due to security policies I can't use Install-module to install dbatools nor dbatools.library. I downloaded the latest release of both and unzipped them into My Documents\WindowsPowershell\Modules directory. I removed the -master and -main from the folder names.
When I try to import the module dbatools I get and error from the dbatools.library module:

import-module : The specified module 'C:\Users\adm-l2a-deanderson\Documents\WindowsPowerShell\Modules\dbatools.library
desktop\lib\Microsoft.Data.SqlClient.dll' was not loaded because no valid module file was found in any module
directory.
At line:1 char:1

• import-module dbatools

•   + CategoryInfo          : ResourceUnavailable: (C:\Users\adm-l2...a.SqlClient.dll:String) [Import-Module], FileNot
   FoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
  

CVE-2024-0056 fixes

Hi friends,

Our InfoSec team has passed this vulnerability onto us and so I'm opening this here with you to see if this can be remediated as it relates to the dbatools.library module.

The library Microsoft.Data.SqlClient version 5.0.1 was detected in Dotnet library manager located at C:\Program Files\WindowsPowerShell\Modules\dbatools.library\2023.9.21\core\lib\mac\sqlpackage.deps.json and is vulnerable to CVE-2024-0056, which exists in versions >= 5.0.0, < 5.1.3.

The vulnerability was found in the Github Security Advisory with vendor severity: High.

The vulnerability can be remediated by updating the library to version 5.1.3 or higher, using dotnet add package Microsoft.Data.SqlClient.

I'm running the following versions:

2.1.8 dbatools PSGallery The community module that enables SQL Server Pro...
2023.9.21 dbatools.library PSGallery The library that powers dbatools, the community ...

Thanks!

Gary Hampson