You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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
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:
Pass the Encrypt switch the Optional value, or set it the Encrypt value to No within a connection string parameter of Invoke-SqlCmd.
or
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.
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.
Fresh, default install of SQL server 2019 developer edition.
Updated to latest release version of
SqlServer
.Executed
fails with error
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.The text was updated successfully, but these errors were encountered: