Error message for new "trusted" connections feature is confusing

[Pxtl]

Fresh, default install of SQL server 2019 developer edition.

Updated to latest release version of SqlServer .

Executed

Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world'"

fails with error

Invoke-SqlCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL
Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
At line:1 char:1
+ Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world' ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Powershell version 5.1.19041.2673 SqlServer module 22.0.59

This is confusing for the end-user because the default install of the SqlServer PS module is now incompatible with the default install of SqlServer, and the message does not clearly explain what needs to be done to correct the issue. Notifying the user that they must either install a trusted certificate into the server, add the self-signed cert into the client certificate store, or use the trusting mode for Invoke-SqlCmd would help.

[admiraljohndoe]

We also noticed this behavior on the SqlServer module of an older version which was not updated/upgraded to the latest version (it basically was unchanged).

May I ask the question, could it be possible, that this behavior is brought in by a dependency being updated required for running this PowerShell module "SqlServer" or by a new Windows security setting

[Delamater]

What's also confusing (and I realize this is an issue with the ODBC driver, not the SqlServer module) is that you can bypass the certificate chain error in two ways, that seem functionally identical. That is, you can:

1. Pass the Encrypt switch the Optional value, or set it the Encrypt value to No within a connection string parameter of Invoke-SqlCmd. or
2. You can pass the TrustServerCertificate switch.

If I read Microsoft's instructions on this correctly, both approaches yield the following result on ODBC 17 and ODBC 18:

• Server certificate isn't checked.
• Data sent between client and server isn't encrypted.

Two approaches to do exactly the same thing makes me think I missed something important.

p.s. @Pxtl : I get that same error in SSMS if I use the options button during connection time and specify the encrypt without putting certificates in place.

[Pxtl]

I'm aware that other tools can also trigger this behavior but AFAIK Invoke-SqlCmd is distinct in that it's now the default behavior. A naive user of SSMS will not trip over this case, whereas whereas this is effectively a breaking change for SqlServer PS 22, even in the default use-case. A justifiable breaking change, but a breaking change.