Version 22.4.5.1 does not work with Azure Deployment Scripts

[avendel]

I am using the SQL Server PS module in an Azure Deployment Script. It imports the module with Install-Module and then invokes an SQL command with Invoke-SqlCmd.

This has previously worked well, but stopped working after version 22.4.5.1 was pushed to the PS gallery. It now fails with System.IO.FileNotFoundException: Could not load file or assembly 'System.Runtime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.

Here is a simple reproduction that you can run locally:

$ docker run -it mcr.microsoft.com/azuredeploymentscripts-powershell:az10.0
PS /> Install-Module -Name SqlServer -Force
PS /> Import-Module SqlServer

You will be presented with the following error:

Import-Module: Could not load file or assembly 'System.Runtime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.

InvalidOperation: The variable '$binaryModule' cannot be retrieved because it has not been set.

Import-Module: Could not load file or assembly 'System.Runtime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.

Import-Module: Could not load file or assembly 'System.Management.Automation, Version=7.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. The system cannot find the file specified.

If you use the previous version of the SQL Server PS module, things will work fine. Try the reproduction but with Install-Module -Name SqlServer -Force -RequiredVersion 22.3.0 instead, and you won't get any errors.

This could of course be a combination of issues in the SQL Server PS module and the azuredeploymentscripts-powershell image, but since things work with the previous version of the PS module, I believe there must be a breaking change of it.

Here is another mention of the issue from someone else: https://stackoverflow.com/questions/79670136/has-there-been-a-recent-change-to-invoke-sqlcmd-that-break-it-and-if-so-whats

[DennisBergemann]

Issue is mostly related to the used powershell version. As an Example Powershell 7.2 is .net 6.0, because of that you have no access to .net 8 dlls.

You can use higher ps version or lower sqlserver version