Installing SQL Server PowerShell module when offline (.nupkg)

3 minute read time.

With Sage X3 2022 R4, a new prerequisite was introduced for the Sage X3 console, the requirement to install PowerShell v7.2+ and, subsequently, the SqlServer module for PowerShell that allows database scripts to be executed when configuring the application server.

More information can be found in the following blog post.

(+) 2022R4 introduces support for Azure Elastic Pool Database - installation requires PowerShell 7.2 - Sage X3 UK Support & Insights - Sage X3 UK - Sage City Community

I recently came across an upgrade of a UAT environment to 2022R4 with no internet connection possibilities. This meant that after the installation of PowerShell 7, it was not possible to use the command mentioned in the article to connect to the PowerShell gallery API (https://www.powershellgallery.com/api/v2) and download the SqlServer module.

When executing the PowerShell install command

pwsh -Command "Install-Module -Name SqlServer -Scope AllUsers -force" 

The following message was returned.

Internet connectivity can be limited for various reasons, so I decided to write this blog to help you install the module if this is the case. I will walk you through how to download and install the module manually. The installation of PowerShell was straightforward. You can download the .msi package from Microsoft, copy the installation to your server, and install it.

https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.2#msi

1. Downloading the SqlServer module installation package for PowerShell (sqlserver.21.1.18256.nupkg)

The first step is to download the SqlServer module installation package. You will need to do this on a client that has internet access. Navigate to the PowerShell gallery at https://www.powershellgallery.com, search for 'SqlServer', and select the appropriate package version required.

When writing this blog post, the Sage X3 console (version 2.56.0.19) requires version 21.1.18256, as mentioned in the blog post.

(33) Installing SqlServer Module 21.1.18256 (because v22.0.59 breaks stuff) - Sage X3 Support - Sage X3 - Sage City Community.

The download will give you a file with an extension. nupkg (sqlserver.21.1.18256.nupkg). This is known as a NuGet package. If unzipped, it will contain compiled code (DLLs), other files related to that code, and a descriptive manifest that includes information like the package's version number.

2. Create the trusted local repository.

Now that we have the required installation package, the next step is to create a local trusted repository where we can install the SqlServer module from

Create a folder C:\nupkg and copy the file we downloaded in Step 1 to this location.

Using PowerShell v7, execute the following command to register the repository

Register-PSRepository -Name LocalPackages -SourceLocation C:\NuPkg -InstallationPolicy Trusted

When you execute the command get-PSRepository, you will see that your local repository is trusted and available.

3. Installing the module from the local repository

Now we can complete the final step of installing the SQL Server module for PowerShell from our local repository, which we set up.

Install-module SQLServer -Scope AllUsers -Force -RequiredVersion 21.1.18256

Once the installation is complete, use

Get-Module SqlServer -ListAvailable

To confirm the installation was successful.

Note: all the steps need to be completed using PowerShell 7, which you downloaded and not the native PowerShell included with Windows Server operating systems.

And that's how you can fulfil the prerequisite of installing the SQL server scripting module for PowerShell if there is no internet connection or other restraints not allowing you to connect to the PowerShell repository.

Here are some Useful links to help you along

2022R4 introduces support for Azure Elastic Pool Database - installation requires PowerShell 7.2

PowerShell Gallery 

Installing SqlServer Module 21.2.18256

Error: "The certificate chain was issued by an authority that is not trusted" when trying to configure Runtime 95 for the first time

I hope you find this helpful.

  • Please note that from 2023R2 the prerequisite for the SQLServer Module for Powershell has now been updated to version 22.1.1

    You can use the following command to install the new version 

    pwsh -Command "Install-Module -Name SqlServer -Scope AllUsers -Force -RequiredVersion 22.1.1"

    If upgrading from an earlier version of Sage X3 existing versions of the module would need to be uninstalled or you can use ' -AllowClobber ' command