Invoke-Sqlcmd doesn't allow TrustServerCertificate

Question

I'm one of two SQL Server DBAs trying to use a PowerShell script and have run into a problem. The code I'm trying to run is:

$DSQLServer = 'VM-SQL119'
$Results = Invoke-SqlCmd -ServerInstance $DSQLServer -Database master -Query "SELECT @@ServerName" -TrustServerCertificate

When I run it on my desktop, it works. When the other DBA runs it, he gets:

Invoke-Sqlcmd : A parameter cannot be found that matches parameter name 'TrustServerCertificate'

Both our machines are running the same version of PowerShell:

$PSVersionTable.PSVersion
Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      19041  3570

I Googled how to find which module a cmdlet is coming from and, again, both our desktops return the same information:

(Get-Command -Name Invoke-sqlcmd).ModuleName
SqlServer

Can anyone shed some light on how the same command, in the same version of PowerShell, from the same module, can work on one machine and fail on the other?

Thanks in advance.

Ken

Answer 1

Fundamentally - unfortunately - there are two - technically distinct - Invoke-SqlCmd cmdlets:

• The obsolete Invoke-SqlCmd command from the obsolete SQLPS module.

• The current Invoke-SqlCmd command from the successor module, SQLServer

See SQL Server PowerShell for background information.

---

However, since you're both apparently using the modern SQLServer module, the difference comes down to what version of that module you're using:

• From the description of the -TrustServerCertificate parameter (emphasis and direct link added):

This parameter is new in v22 of the module. For more details, see Strict Connection Encryption under Related Links.

---

To diagnose the problem:

In a pristine session, run:

(Get-Command Invoke-SqlCmd).Module

which prints both the name and the version number of the (effective) module from which the Invoke-SqlCmd command originates, which the Get-Command call implicitly auto-loads in the process.

Note that both the obsolete SqlPs and the modern SqlServer module may be present, and for each there may be multiple versions.

That is why it is important to run the Get-Command call in a pristine session, i.e. one in which no third-party modules have been imported yet, given that code running in the session could explicitly import the wrong module or an older version, after which Invoke-SqlCommand then refers to that module's command.

Get-Module -ListAvailable SqlServer, SqlPs lists all versions of these two modules that are discoverable via auto-loading (i.e. via the directories listed in $env:PSModulePath, in order). For a given module, the version listed first (if there are multiple) is the effective one, i.e. the one that will be auto-loaded.
With two modules in the picture, whichever('s first version) is listed first is the effective one.

It's best to remove all obsolete versions - note that you may have to run with elevation (as administrator) if the modules to be removed were installed with -Scope AllUsers:

• Remove all versions of the obsolete SqlPs module.

  # See below if that fails.
  Uninstall-Module -AllVersions SqlPs
  

• Remove all but the latest version of the SqlServer module.

  # E.g., to uninstall v21.0.17099
  # See below if that fails.
  Uninstall-Module -RequiredVersion 21.0.17099 SqlServer
  

If the use of Uninstall-Module fails, despite running with elevation - usually because the target module wasn't installed with Install-Module to begin with - remove the underlying directories manually - which again may require elevation:

• List the candidate set of module directories:

  (Get-Module -ListAvailable SqlPs, SqlServer).Path | Split-Path -Parent
  

• Identify those to delete and pass them to Remove-Item -Recurse -Force

  • Note: If a given directory path ends in a version number (e.g. ...\SqlServer\22.0.59) and its parent directory (...\SqlServer) ends up empty after removal, you can remove the latter too.

---

The latest version of the SqlServer module can be downloaded from the PowerShell Gallery, i.e. can be installed with, e.g., Install-Module -Force SqlServer.