Ensures that the latest PSGallery SqlServer PowerShell module is installed (Windows only) and loaded while excluding discovered legacy SQLPS locations from the current PowerShell session.
<#
.SYNOPSIS
Ensures that the latest PSGallery SqlServer PowerShell module is installed
and loaded while excluding discovered legacy SQLPS locations from the
current PowerShell session.
.DESCRIPTION
This script discovers legacy SQLPS modules before changing PSModulePath. It
checks all module locations currently visible to PowerShell and common SQL
Server installation locations under both Program Files trees.
Discovery includes:
- SQLPS modules returned by Get-Module -ListAvailable.
- SQLPS modules already loaded in the current session.
- SQLPS folders beneath every current PSModulePath entry.
- Versioned SQL Server shared-feature paths such as:
C:\Program Files\Microsoft SQL Server\<version>\Tools\PowerShell\Modules
C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules
- SQLPS module files found below the corresponding SQL Server PowerShell
trees, including layouts that differ from the usual versioned path.
Any PSModulePath entry that exposes a discovered SQLPS module is removed
from the CURRENT PROCESS only. Legacy files and directories are never
deleted, renamed, or modified.
The script queries PSGallery for the latest SqlServer version. If that exact
version is already installed in a non-excluded location, installation is
skipped. Otherwise, the exact latest version is installed with
-AllowClobber. The selected version is then imported by its full path.
The default installation scope is AllUsers. AllUsers requires an elevated
Windows session, and the script stops with an actionable error if it is not
running as Administrator. CurrentUser remains available for environments
where a per-user installation is preferred.
.NOTES
Filename: Install-LatestSqlServerModule.ps1
Prerequisite if Windows reports that the script is not digitally signed:
PowerShell can attach a downloaded-file security marker to this script.
If you trust the script, remove that marker before running it:
Unblock-File -LiteralPath '.\Install-LatestSqlServerModule.ps1'
To unblock and run it in one command from an elevated PowerShell session:
Unblock-File -LiteralPath '.\Install-LatestSqlServerModule.ps1'; & '.\Install-LatestSqlServerModule.ps1'
If the script is stored elsewhere, supply its full path instead. Example:
Unblock-File -LiteralPath 'U:\installers\Install-LatestSqlServerModule.ps1'
& 'U:\installers\Install-LatestSqlServerModule.ps1'
Unblock-File should be used only after reviewing and trusting the script.
It does not override an AllSigned policy enforced by organizational Group
Policy. In that case, a script signed by a trusted publisher is required.
Requirements:
- Windows PowerShell 5.1 or PowerShell 7+
- The script must be permitted by the effective execution policy
- PowerShellGet commands: Find-Module and Install-Module
- PSGallery access; the script attempts to restore its registration
- HTTPS access to www.powershellgallery.com and go.microsoft.com
- Administrator privileges when using the default AllUsers scope
Idempotency:
- The latest gallery version is installed only when that exact version is
not already available from a retained module path.
- Repeated runs still verify discovery, repository state, and import the
requested module, but do not reinstall an existing latest version.
Session impact:
- PSModulePath changes apply only to this PowerShell process.
- Loaded SQLPS and SqlServer modules are removed before SqlServer is
imported, because exported command names can overlap.
- No persistent environment variables are changed.
.PARAMETER Scope
Specifies the installation scope. The default is AllUsers.
AllUsers requires an elevated PowerShell session. CurrentUser normally does
not require elevation.
.EXAMPLE
.\Install-LatestSqlServerModule.ps1
Ensures the latest SqlServer module is installed for all users. Run from an
elevated 64-bit Windows PowerShell 5.1 or PowerShell 7 session.
.EXAMPLE
.\Install-LatestSqlServerModule.ps1 -Verbose
Performs the default AllUsers operation and displays detailed SQLPS path
discovery and exclusion information.
.EXAMPLE
.\Install-LatestSqlServerModule.ps1 -Scope CurrentUser
Ensures the latest SqlServer module is installed for the current user. This
mode normally does not require Administrator privileges.
.EXAMPLE
powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\Install-LatestSqlServerModule.ps1
Runs the script in Windows PowerShell without loading the user's profile.
Use an execution-policy override only when organizational policy permits it.
.LINK
https://learn.microsoft.com/powershell/sql-server/download-sql-server-ps-module
.LINK
https://learn.microsoft.com/powershell/module/microsoft.powershell.core/about/about_psmodulepath
#>
[CmdletBinding()]
param(
[ValidateSet('AllUsers', 'CurrentUser')]
[string]$Scope = 'AllUsers'
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
function Get-NormalizedPath {
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[string]$Path
)
try {
return [System.IO.Path]::GetFullPath($Path).TrimEnd(
[System.IO.Path]::DirectorySeparatorChar,
[System.IO.Path]::AltDirectorySeparatorChar
)
}
catch {
return $Path.Trim().TrimEnd('\', '/')
}
}
function Test-IsAdministrator {
[CmdletBinding()]
param()
# WindowsIdentity is available in both Windows PowerShell 5.1 and
# PowerShell 7 on Windows. This script targets Windows installations.
try {
$identity = [Security.Principal.WindowsIdentity]::GetCurrent()
}
catch {
return $false
}
$principal = [Security.Principal.WindowsPrincipal]::new($identity)
return $principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
}
function Get-SqlPsEvidence {
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[string[]]$ModulePathEntries
)
$evidence = [System.Collections.Generic.List[object]]::new()
$seen = [System.Collections.Generic.HashSet[string]]::new(
[System.StringComparer]::OrdinalIgnoreCase
)
function Add-Evidence {
param(
[string]$Path,
[string]$Source
)
if ([string]::IsNullOrWhiteSpace($Path)) {
return
}
$normalized = Get-NormalizedPath -Path $Path
if ($seen.Add($normalized)) {
$evidence.Add([pscustomobject]@{
Path = $normalized
Source = $Source
})
}
}
# Capture modules that are already loaded, including modules that may no
# longer be discoverable through the current PSModulePath.
foreach ($module in @(Get-Module -Name SQLPS -ErrorAction SilentlyContinue)) {
Add-Evidence -Path $module.Path -Source 'Loaded module'
}
# Ask PowerShell to discover SQLPS using all currently configured module
# roots. This covers standard user, all-user, and custom module locations.
foreach ($module in @(Get-Module -Name SQLPS -ListAvailable -ErrorAction SilentlyContinue)) {
Add-Evidence -Path $module.Path -Source 'PowerShell module discovery'
}
# Inspect each PSModulePath root directly. This also catches incomplete or
# malformed SQLPS installations that Get-Module may not enumerate.
foreach ($moduleRoot in $ModulePathEntries) {
if (-not (Test-Path -LiteralPath $moduleRoot -PathType Container)) {
continue
}
$sqlPsFolder = Join-Path -Path $moduleRoot -ChildPath 'SQLPS'
if (Test-Path -LiteralPath $sqlPsFolder -PathType Container) {
Add-Evidence -Path $sqlPsFolder -Source 'PSModulePath SQLPS folder'
}
}
# Search both native and x86 SQL Server shared-feature roots. Environment
# variables are used rather than hard-coding drive letters.
$sqlServerRoots = [System.Collections.Generic.HashSet[string]]::new(
[System.StringComparer]::OrdinalIgnoreCase
)
foreach ($programFilesRoot in @(
$env:ProgramFiles,
${env:ProgramFiles(x86)},
${env:ProgramW6432}
)) {
if (-not [string]::IsNullOrWhiteSpace($programFilesRoot)) {
[void]$sqlServerRoots.Add(
(Join-Path -Path $programFilesRoot -ChildPath 'Microsoft SQL Server')
)
}
}
foreach ($sqlServerRoot in $sqlServerRoots) {
if (-not (Test-Path -LiteralPath $sqlServerRoot -PathType Container)) {
continue
}
# Fast path for the conventional versioned shared-feature layout.
foreach ($versionFolder in @(
Get-ChildItem -LiteralPath $sqlServerRoot -Directory -ErrorAction SilentlyContinue |
Where-Object { $_.Name -match '^\d+$' }
)) {
$modulesFolder = Join-Path -Path $versionFolder.FullName -ChildPath 'Tools\PowerShell\Modules'
$sqlPsFolder = Join-Path -Path $modulesFolder -ChildPath 'SQLPS'
if (Test-Path -LiteralPath $sqlPsFolder -PathType Container) {
Add-Evidence -Path $sqlPsFolder -Source 'Versioned SQL Server tools path'
}
elseif (Test-Path -LiteralPath $modulesFolder -PathType Container) {
# Some legacy layouts place SQLPS.psd1 or SQLPS.psm1 directly
# in Modules instead of a dedicated SQLPS child directory.
foreach ($file in @(
Get-ChildItem -LiteralPath $modulesFolder -File -ErrorAction SilentlyContinue |
Where-Object { $_.Name -in @('SQLPS.psd1', 'SQLPS.psm1') }
)) {
Add-Evidence -Path $file.FullName -Source 'Versioned SQL Server tools path'
}
}
}
# Broader fallback for nonstandard SQL Server PowerShell layouts. Keep
# the search beneath PowerShell directories to avoid scanning the full
# SQL Server installation tree unnecessarily.
foreach ($powerShellFolder in @(
Get-ChildItem -LiteralPath $sqlServerRoot -Directory -Recurse -ErrorAction SilentlyContinue |
Where-Object { $_.Name -eq 'PowerShell' }
)) {
foreach ($sqlPsFile in @(
Get-ChildItem -LiteralPath $powerShellFolder.FullName -File -Recurse -ErrorAction SilentlyContinue |
Where-Object { $_.Name -in @('SQLPS.psd1', 'SQLPS.psm1') }
)) {
Add-Evidence -Path $sqlPsFile.FullName -Source 'SQL Server PowerShell tree'
}
}
}
return $evidence
}
function Initialize-PowerShellGallery {
[CmdletBinding()]
param(
[Parameter(Mandatory)]
[ValidateSet('AllUsers', 'CurrentUser')]
[string]$InstallationScope
)
# Windows PowerShell 5.1 commonly defaults to an obsolete TLS protocol.
# Enable TLS 1.2 before any PackageManagement or PowerShellGet operation.
if ($PSVersionTable.PSEdition -eq 'Desktop') {
[Net.ServicePointManager]::SecurityProtocol =
[Net.ServicePointManager]::SecurityProtocol -bor
[Net.SecurityProtocolType]::Tls12
}
# In domain environments, permit the process to use the signed-in user's
# default proxy credentials. This does not bypass an authenticated proxy or
# change machine proxy configuration.
try {
if ([Net.WebRequest]::DefaultWebProxy) {
[Net.WebRequest]::DefaultWebProxy.Credentials =
[Net.CredentialCache]::DefaultCredentials
}
}
catch {
Write-Verbose "Default proxy credentials could not be assigned: $($_.Exception.Message)"
}
foreach ($requiredCommand in @(
'Get-PackageProvider',
'Install-PackageProvider',
'Get-PSRepository',
'Register-PSRepository',
'Find-Module',
'Install-Module'
)) {
if (-not (Get-Command -Name $requiredCommand -ErrorAction SilentlyContinue)) {
throw "Required command '$requiredCommand' is unavailable. Install or update PackageManagement and PowerShellGet, then run this script again."
}
}
# PowerShellGet 1.x requires the NuGet package provider. Check the installed
# providers without triggering provider download prompts.
$nuGetProvider = Get-PackageProvider -Name NuGet -ListAvailable -ErrorAction SilentlyContinue |
Sort-Object Version -Descending |
Select-Object -First 1
if (-not $nuGetProvider -or $nuGetProvider.Version -lt [version]'2.8.5.201') {
Write-Host 'Installing the NuGet package provider required by PowerShellGet...'
try {
$providerParameters = @{
Name = 'NuGet'
MinimumVersion = '2.8.5.201'
Force = $true
ForceBootstrap = $true
ErrorAction = 'Stop'
}
# Older PackageManagement releases may not expose a Scope parameter.
if ((Get-Command Install-PackageProvider).Parameters.ContainsKey('Scope')) {
$providerParameters.Scope = $InstallationScope
}
Install-PackageProvider @providerParameters | Out-Null
}
catch {
throw @"
The NuGet package provider could not be downloaded. PowerShellGet attempted to
reach Microsoft's provider bootstrap endpoint but the HTTPS request failed.
Verify that this session can access these endpoints through your firewall/proxy:
https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409
https://www.powershellgallery.com/api/v2
If your organization performs TLS inspection, make sure its root certificate is
trusted by this computer. Also verify the WinHTTP proxy with:
netsh winhttp show proxy
Underlying error: $($_.Exception.Message)
"@
}
}
$gallery = Get-PSRepository -Name PSGallery -ErrorAction SilentlyContinue
if (-not $gallery) {
Write-Host 'PSGallery is not registered. Restoring the default registration...'
try {
Register-PSRepository -Default -ErrorAction Stop
$gallery = Get-PSRepository -Name PSGallery -ErrorAction Stop
}
catch {
throw @"
PSGallery could not be registered. Confirm that PowerShell can reach
https://www.powershellgallery.com/api/v2 through the configured proxy and that
PackageManagement/PowerShellGet are functional.
Useful diagnostics:
[Net.ServicePointManager]::SecurityProtocol
Get-PackageProvider -ListAvailable
Get-PSRepository
netsh winhttp show proxy
Underlying error: $($_.Exception.Message)
"@
}
}
return $gallery
}
try {
if ($Scope -eq 'AllUsers' -and -not (Test-IsAdministrator)) {
throw @'
The default AllUsers installation scope requires Administrator privileges.
Open 64-bit Windows PowerShell or PowerShell 7 by using "Run as administrator",
then run this script again. Alternatively, explicitly use -Scope CurrentUser.
'@
}
$pathSeparator = [System.IO.Path]::PathSeparator
$originalModulePathEntries = @(
$env:PSModulePath -split [regex]::Escape([string]$pathSeparator) |
Where-Object { -not [string]::IsNullOrWhiteSpace($_) }
)
Write-Host 'Discovering legacy SQLPS module locations...'
$sqlPsEvidence = @(Get-SqlPsEvidence -ModulePathEntries $originalModulePathEntries)
if ($sqlPsEvidence.Count -gt 0) {
Write-Host 'Detected SQLPS evidence:'
foreach ($item in $sqlPsEvidence) {
Write-Host " [$($item.Source)] $($item.Path)"
}
}
else {
Write-Host 'No legacy SQLPS module locations were detected.'
}
# Exclude each PSModulePath root that contains discovered SQLPS evidence.
# If evidence is outside PSModulePath, exclude its conventional Modules root
# when one can be derived from the path.
$excludedModuleRoots = [System.Collections.Generic.HashSet[string]]::new(
[System.StringComparer]::OrdinalIgnoreCase
)
foreach ($entry in $originalModulePathEntries) {
$normalizedEntry = Get-NormalizedPath -Path $entry
$entryPrefix = $normalizedEntry + [System.IO.Path]::DirectorySeparatorChar
foreach ($item in $sqlPsEvidence) {
if ($item.Path.Equals($normalizedEntry, [StringComparison]::OrdinalIgnoreCase) -or
$item.Path.StartsWith($entryPrefix, [StringComparison]::OrdinalIgnoreCase)) {
[void]$excludedModuleRoots.Add($normalizedEntry)
break
}
}
}
foreach ($item in $sqlPsEvidence) {
$match = [regex]::Match(
$item.Path,
'^(.*?[\\/]Modules)(?:[\\/]|$)',
[Text.RegularExpressions.RegexOptions]::IgnoreCase
)
if ($match.Success) {
[void]$excludedModuleRoots.Add((Get-NormalizedPath -Path $match.Groups[1].Value))
}
}
$seenRetainedPaths = [System.Collections.Generic.HashSet[string]]::new(
[System.StringComparer]::OrdinalIgnoreCase
)
$retainedModulePathEntries = @(
foreach ($entry in $originalModulePathEntries) {
$normalizedEntry = Get-NormalizedPath -Path $entry
if ($excludedModuleRoots.Contains($normalizedEntry)) {
Write-Verbose "Excluded from process PSModulePath: $entry"
continue
}
if ($seenRetainedPaths.Add($normalizedEntry)) {
$entry
}
}
)
$env:PSModulePath = $retainedModulePathEntries -join $pathSeparator
if ($excludedModuleRoots.Count -gt 0) {
Write-Host 'Excluded module roots from this process:'
foreach ($root in @($excludedModuleRoots | Sort-Object)) {
Write-Host " $root"
}
}
# Remove conflicting modules only after discovery has captured their paths.
Get-Module -Name SQLPS, SqlServer -ErrorAction SilentlyContinue |
Remove-Module -Force -ErrorAction Stop
# Prepare PackageManagement and PSGallery only after legacy paths have
# been removed. This includes TLS 1.2, NuGet provider bootstrap, proxy
# credentials, and restoration of missing default repository registration.
$null = Initialize-PowerShellGallery -InstallationScope $Scope
Write-Host 'Finding the latest SqlServer module in PSGallery...'
$galleryModule = Find-Module -Name SqlServer -Repository PSGallery -ErrorAction Stop
Write-Host "Latest available version: $($galleryModule.Version)"
# This lookup occurs after legacy path exclusion, so an installed module is
# considered reusable only when it remains available in the clean session.
$installedModule = Get-Module -Name SqlServer -ListAvailable |
Where-Object { $_.Version -eq $galleryModule.Version } |
Sort-Object Path |
Select-Object -First 1
if ($installedModule) {
Write-Host "SqlServer $($galleryModule.Version) is already installed. Installation skipped."
}
else {
Write-Host "Installing SqlServer $($galleryModule.Version) for scope '$Scope'..."
Install-Module `
-Name SqlServer `
-RequiredVersion $galleryModule.Version `
-Repository PSGallery `
-Scope $Scope `
-AllowClobber `
-ErrorAction Stop
$installedModule = Get-Module -Name SqlServer -ListAvailable |
Where-Object { $_.Version -eq $galleryModule.Version } |
Sort-Object Path |
Select-Object -First 1
if (-not $installedModule) {
throw "SqlServer $($galleryModule.Version) was not found after installation."
}
}
Import-Module -Name $installedModule.Path -Force -ErrorAction Stop
$loadedModule = Get-Module -Name SqlServer |
Where-Object { $_.Version -eq $galleryModule.Version } |
Select-Object -First 1
if (-not $loadedModule) {
throw 'The expected SqlServer module was not verified as loaded.'
}
Write-Host ''
Write-Host 'SqlServer module is ready.' -ForegroundColor Green
$loadedModule |
Select-Object Name, Version, Path |
Format-List
Write-Host 'Legacy SQLPS files and directories were not modified.'
Write-Host 'PSModulePath was changed only for this PowerShell process.'
}
catch {
Write-Error "SqlServer module setup failed: $($_.Exception.Message)"
exit 1
}