Versions of included sqlserver DLLs both newer and older than sqlserver module.

[ArnoldLieberman-CoT]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Importing dbatools and sqlserver modules fails regardless of the order they are imported in. This is due to dbatools having some DLLs newer than the versions in sqlserver and some that are older. To workaround the issue I have had to replace the DLLs in the dbatools folder with ones from sqlserver. Microsoft.Identity.Client.dll Microsoft.Data.SqlClient.dll

There may be others with the same issue.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference
if (get-module -name dbatools -listavailable) {uninstall-module dbatools -AllVersions -Force}
if (get-module -name dbatools.library -listavailable) {uninstall-module dbatools.library -AllVersions -Force}
install-module -Name dbatools -Force -confirm:$false -AllowClobber -Scope CurrentUser -Repository PSGallery
if (get-module -name sqlserver -listavailable) {uninstall-module sqlserver -AllVersions -Force}install-module -Name sqlserver -Force -confirm:$false -AllowClobber -Scope CurrentUser -Repository PSGallery

import-module dbatools
import-module sqlserver

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

2.1.10

Other details or mentions

importing sqlserver first appears to work in the ps prompt but generates "Assembly with same name is already loaded" if done as part of a module psd1 file.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.4.1 PSEdition Core GitCommitId 7.4.1 OS Microsoft Windows 10.0.22631 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

Microsoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) Jan 30 2024 17:02:22 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET 8.0.1

[SQLCanuck]

Ran into the same issue as well.

"Couldn't import /root/.local/share/powershell/Modules/dbatools.library/2024.4.12/core/lib/Microsoft.Data.SqlClient.dll | Assembly with same name is already loaded."

[SQLCanuck]

For now I was able to work around by using one of the following methods. https://learn.microsoft.com/en-us/powershell/scripting/dev-cross-plat/resolving-dependency-conflicts?view=powershell-7.4#use-the-dependency-out-of-process

Though hopefully someone will be able to implement one of the more permanent and robust solutions also offered in the linked document.

[mattcargile]

Relevant Issue. https://github.com/dataplat/dbatools/issues/9334

[ArnoldLieberman-CoT]

This is the code that I have used, which has worked until Tuesday this week.

It seems that new versions of AZ, SQLServer and dbatools have all conspired to break again.

function Update-NaughtFile {
    param (
        [string]
        $FileName
        ,[string]
        $SourceRootPath
        ,[string]
        $DestinationRootPath
    )
    $SourceFile = (get-childitem $SourceRootPath -include $FileName -recurse -ErrorAction SilentlyContinue | Where-Object {$_.directoryname -notlike '*\unix\*'}).fullname
    if ((Test-Path -Path $SourceFile)) {
        $SourceFileVersion = (Get-Command ($SourceFile)).fileversioninfo.fileversion
        write-verbose "Source file $SourceFile, version $SourceFileVersion."
        $DestinationFile = (get-childitem $DestinationRootPath -include $FileName -recurse -ErrorAction SilentlyContinue | Where-Object {$_.directoryname -like '*win-sqlclient*'}).fullname
        if ($DestinationFile) {
            $DestinationFileVersion = (Get-Command ($DestinationFile)).fileversioninfo.fileversion
            write-verbose "Destination file $DestinationFile, version $DestinationFileVersion."
            if ($SourceFileVersion -ne $DestinationFileVersion) {
                write-verbose "About to replace $FileName."
                Copy-Item -path $SourceFile -Destination $DestinationFile -Force
                write-verbose "Done."
            }
            else {
                write-verbose "Files are the same."
            }
        }
        else {
            Write-Verbose "Destination $FileName has not been replaced as it does not exist."
        }
    }
    else {
        write-verbose "Source '$SourceFile' cannot be found."
    }
}

# Bits of the below are borrowed from Script module for module 'SqlServer'
Set-StrictMode -Version Latest

# Locate the latest source and destination modules to patch.
$sqlserver = (get-module -name sqlserver -listavailable) | Sort-Object -Property version -Descending | Select-Object -first 1
$SourcebinaryModuleRoot = Join-Path -Path ($sqlserver.ModuleBase) -ChildPath 'coreclr'
write-verbose "Source modules located under $SourcebinaryModuleRoot\"
$dbatoolslibrary = (get-module -name dbatools.library -ListAvailable) | Sort-Object -Property version -Descending | Select-Object -first 1
$DestinationBinaryModuleRoot = Join-Path -Path ($dbatoolslibrary.ModuleBase) -ChildPath 'core'

Write-Verbose "Destination module located in $(split-path $dbatoolslibrary.path)\"
Update-NaughtFile -FileName 'Microsoft.Data.SqlClient.dll' -SourceRootPath $SourcebinaryModuleRoot -DestinationRootPath $DestinationBinaryModuleRoot
Update-NaughtFile -FileName 'Microsoft.Identity.Client.dll' -SourceRootPath $SourcebinaryModuleRoot -DestinationRootPath $DestinationBinaryModuleRoot

[ArnoldLieberman-CoT]

The above code continues to work as it was another part of the pipeline that broke.

[mattcargile]

Yeah it is Dependency hell. You might have to spin off separate pwsh or powershell processes so the underlying dlls don't collide.