Powersehell command invoke-sqlcmd command is looking for an additional parameter TrustServerCertificate=True on Windows 2022 server

Suresh 20 Reputation points
2024-10-07T16:10:36.2666667+00:00

Hi there,

We heavily use PowerShell scripts to query Microsoft SQL Server databases on Windows 2016 machines using a command
invoke-sqlcmd -ServerInstance <servername> -Database <databasename> -Query <Query statement>;

While testing the same script on Windows 2022, the above call fails with an error
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
I understand that adding a parameter like TrustServerCertificate=True to the command will resolve the issue. Please note that I have hundreds of scripts making multiple such calls. It's not an easy solution to update all the scripts. Is there a solution other than modifying the existing PowerShell scripts to make the scripts working with no issues?
Thanks in advance
Suresh

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,947 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
13,198 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,546 questions
0 comments
Accepted answer
  1. Erland Sommarskog 112.6K Reputation points MVP
    2024-10-07T21:30:44.2366667+00:00

    Yes, there is another solution. Although, necessarily not one that is easier... But if you for all instances set up proper certificates and then make sure that the certificates are trusted on the machine(s) where you run the PowerShell scripts. They would be trusted if they come from an external provider like VeriSign. You can also use self-signed certificates - cheaper! - but then you need to copy these to the local certificate store on the client machines.

    This is a lot better than using Trust Server Certificate, since with the you accept to be victim to a man-in-the-middle attack.

    Then again, if you have these PowerShell scripts well assembled, it should be too much work with a good text editor to run a find/replace to add the argument for TrustServerCertificate...

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.