Open-SQLConnection in 2.x (without TrustServerCertificate=true;) fails: "The certificate chain was issued by an authority that is not trusted" - 1.9.x always works

[kwein123]

When I connect to our SQL Server via SimplySQL 1.9.0 or 1.9.1, it works fine, regardless of my connection string. When I run the exact same script but specify the use of SimplySQL version 2.0.2.70 or 2.0.4.75, if I don't add "TrustServerCertificate=true;", I get:

Open-SQLConnection: V:\KWeinrich\Storage\Create-AllServerCountReport.ps1:485
Line |
 485 |      Open-SqlConnection -ConnectionName 'SS' -ConnectionString $conn # …
     |      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | 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.)

Here is my simple connection string:

SS connection open Server=XYZ;Database=StorageReporting;Integrated Security=SSPI;

Pwsh.exe version 7.4.3

PS V:\KWeinrich\KW-CheckAllServers> get-installedmodule simplysql -allversions

Version              Name                                Repository           Description
-------              ----                                ----------           -----------
1.9.1                SimplySql                           PSGallery            Querying SQL (SQL Server, Oracle, Postgr…
2.0.2.70             SimplySql                           PSGallery            Querying SQL (SQL Server, Oracle, Postgr…
2.0.4.75             SimplySql                           PSGallery            Querying SQL (SQL Server, Oracle, Postgr…

But, if I add TrustServerCertificate=true; to the connection string, yielding:

Server=V26267NCPK609;Database=StorageReporting;Integrated Security=SSPI;TrustServerCertificate=true;

Then it works regardless of SimplySQL version. But I would rather not specify TrustServerCertificate = true - I'd rather it check and verify the cert.

[mithrandyr]

@kwein123 -- so based upon the error provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) it looks like there cert your SQL Server is using is not protected by a trusted authority. I can say that SimplySql 2.0+ changed the provider for SQL Server, I migrated to Microsoft.Data.SqlClient from System.Data.SqlClient and that this newer library from Microsoft has enhanced security. I would begin with checking the certificate your sql server is using and verify how it was signed and whether the client computer trusts that signing authority.

[mithrandyr]

@kwein123 -- so, the next version (2.1.0), which is coming soon, should fix this. I changed the way the application is published, targeting NET6.0 and not just .NET Standard 2.0. This should pull in a different version of the MySqlConnector that will work properly. I'll notify you when its ready and you can test!

[mithrandyr]

@kwein123 -- please try version 2.1 -- as this is hopefully fixed.

[kwein123]

You are most kind, sir. But I have retired, and am no longer pursuing this. The gentleman who took my place is doing his work in SQL Server nowadays.

Thanks for all your kind help and attentiveness!

-Kevin Weinrich

On Thu, Dec 12, 2024 at 4:46 PM Mithrandyr @.***> wrote:

@kwein123 https://github.com/kwein123 -- please try version 2.1 -- as this is hopefully fixed.

— Reply to this email directly, view it on GitHub https://github.com/mithrandyr/SimplySql/issues/160#issuecomment-2540075882, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLYKFVVM3XGLH5RWKTGLCD2FH72XAVCNFSM6AAAAABLDNWUD6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNBQGA3TKOBYGI . You are receiving this because you were mentioned.Message ID: @.***>