search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
51 stars 1 forks source link

Invoke-SqlCmd fails with security error when attempting to connect to a database that does not encrypt connections #46

Closed Pxtl closed 1 year ago

Pxtl commented 1 year ago

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

I know you've launched new encryption features, but this has broken basic development workflow. The error message is not informative because it made us assume the server was returning an untrusted self-signed certificate somehow.

We ran into this onboarding a new student developer. He and one of our developers spent the whole day trying to figure out how his development server was configured wrong, assuming there was a problem in their SQL server, not realizing that we'd simply installed the latest version of the module onto this new machine.

Matteo-T commented 1 year ago

Hi @Pxtl, There's not much I can (or want :-)) to do about it. Security has a cost... and sometimes that means making things a little harder for users. I acknowledge that.

But the documentation should and the message should be informative enough: if you trust the server you are connecting to, just add a simple -TrustServerCertificate and you'll be fine.

Pxtl commented 1 year ago

@Matteo-T could we at least maybe get a clearer error-message for cases where the target SQL server is not using an encrypted connection?

"The certificate chain was issued by an authority that is not trusted." is not correct at all. There is no certificate chain. The server's connection is unencrypted. The error message is misleading and costs time in debugging.

Matteo-T commented 1 year ago

@Pxtl - the error message comes straight from the Microsoft.Data.SqlClient, so I'm not sure how easy/hard it would be to try and catch it and massage it to something else.

The message is actually accurate, in the sense that your server is simply giving back a self-signed certificate that your client (the Invoke-Sqlcmd cmdlet, in this case) is not trusting. If you put that certificate into the right location in your certificate store (if you know what you are doing), the message will go away.

You can log a new Issue and I'll see what I can do about it.

Pxtl commented 1 year ago

Ah, thanks for the correction. I didn't realize SQL server uses a self-signed cert if it's not configured with a shared cert. Yes, I'll create a new issue.