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

Question

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

Answer 1

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...