Open-MySQLConnection fails on 2.x with -SSLMode Required (when using Powershell ISE)

[gregesposito]

I have been using SimplySQL to connect to a MySQL database for at least 2+ years.

Upon setting a new system up, I was unable to connect, as I was grabbing the latest 2.x version.

I triaged myself from 2.0.3 down to 2.0.2 (due to the note about the -SSLMode breaking change) but it was still failing. I also had tried putting Required in single or double quotes, to no avail.

I do: Open-MySQLConnection -Server #### -Database #### -Credential $credentials -SSLMode Required

$credentials comes from either a Get-Credential or from an Import-CliXml encoded credentials.

The exception is:

Exception : MySqlConnector.MySqlException (0x80004005): Couldn't connect to server ---> System.ArgumentException: The specified value is not valid in the 'SslProtocolType' enumeration. Parameter name: sslProtocolType at System.Net.Security.SslState.ValidateCreateContext(Boolean isServer, String targetHost, SslProtocols enabledSslProtocols, X509Certificate serverCertificate, X509CertificateCollection clientCertificates, Boolean remoteCertRequired, Boolean checkCertRevocationStatus, Boolean checkCertName) at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation) at MySqlConnector.Core.ServerSession.<InitSslAsync>d__114.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.Core.ServerSession.<ConnectAsync>d__98.MoveNext() at MySqlConnector.Core.ServerSession.<ConnectAsync>d__98.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.Core.ConnectionPool.<ConnectSessionAsync>d__21.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at MySqlConnector.Core.ConnectionPool.<ConnectSessionAsync>d__21.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__9.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__9.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.MySqlConnection.<CreateSessionAsync>d__131.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.MySqlConnection.<OpenAsync>d__28.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at MySqlConnector.MySqlConnection.Open() at SimplySql.Engine.Logic.OpenAndAddConnection(baseConnectionDetail connDetail) at SimplySql.Cmdlets.OpenMySqlConnection.ProcessRecord() TargetObject : default CategoryInfo : OpenError: (default:String) [Open-MySqlConnection], MySqlException FullyQualifiedErrorId : OpenMySQLConnection.Error,SimplySql.Cmdlets.OpenMySqlConnection ErrorDetails : InvocationInfo : System.Management.Automation.InvocationInfo

I was able to resolve this by dropping back down to 1.9.1 via:

Install-Module -Name SimplySql -RequiredVersion 1.9.1 -Scope AllUsers -Force -ErrorAction Stop -Confirm:$false -Verbose:$false

Import-Module SimplySQL -RequiredVersion 1.9.1

This allows my new machine to connect.

[mithrandyr]

@gregesposito -- did you try using 2.0.4 version of SimplySql?

[gregesposito]

Yes, this occurs on 2.0.2.70, the 2.0.3 version, and just had it occur with 2.0.4.75.

I tried various SSLMode options, but all of the correct values (Preferred, etc.) say the same and non-valid ones like "test" throw the "cannot validate argument on parameter 'SSLMode'. The argument "test" does not belong to the set..."

Dropping to 1.9.1 continues to work, it appears I have been using the module since 1.6.2 without issue.

When the SSLMode is set to Disabled (or None on 1.9.1), it does work, as the server in question does not require SSL, but it has been the default to use it. With MySQL Workbench, it connects using TLS_AES_128_GCM_SHA256 when set to Use SSL -> Require and an SSL CA file is provided to the SSL parameters. Server in question being a vendor appliance running MariaDB 10.6.16 currently.

[mithrandyr]

@gregesposito -- Can you provide the powershell you used to connect with version 2.0.4 of SimplySql installed, along with the full error stack. (please omit any sensitive details in the connection parameters).

Because my local MySQL server doesn't have SSL enabled -- I couldn't fully reproduce your issue. However, I just created a MySQL server in Azure and every single -SSLMode option I use works or throws an appropriate error (i.e. if I use "Disabled" then I get a SSL required error message).

Also, are you connecting using a certificate to authenticate or just using username/password to authenticate? SimplySql 2.0 changed providers from MySql (from Oracle) to MySqlConnector, moreover, I targeted .NET Standard 2.0 in order to be handle both PS 5.1 and PS 7+ and be cross-platform. However, there is a known issue with MySqlConnector on .Net Standard 2.0 -- it doesn't support supplying a certificate for authentication -- so I need to double check how you are authenticating as well.

[TKEli]

I have the same issue with version 2.0.4 not working to connect to MySQL in Powershell ISE (5.1) It does however work in a regular PS consol(5.1). Rolling back to version 1.9.1 for now as we do alot of testing in ISE.

[gregesposito]

Wow, I too can confirm that moving from PowerShell ISE (ran as admin) to just Windows PowerShell allows 2.0.4 (and I assume the other versions) to run. I would never have considered PowerShell ISE to be a factor in this!

The error log I posted was the result of dumping $error[0] | Select *. Is there some other error stack you'd like?

My target is a KACE SMA Hyper-V VM with the MySQL access enabled, which is a MariaDB backend. You maybe can get a trial license here https://www.quest.com/register/74480/.

I am using a username and password for the Credential. My understanding is the SSLMode changes this communication from unencrypted to encrypted, but I've not actually done any testing or w/e to validate that. The cert on the appliance is a GoDaddy standard cert - not sure if anything there is useful to know.

The output of $PSVersionTable is on Windows 11:

`Name Value

PSVersion 5.1.22621.3880
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22621.3880
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1 `

[mithrandyr]

@gregesposito and @TKEli -- I haven't done any testing with ISE (haven't used it in years, since MS made the recommendation to go to VSCode). I'm not even sure how to invoke the ISE from the commandline to run the tests... Sorry that this isn't working for you. If you haven't tried VSCode, would highly recommend it!

[TilKenneth]

Sorry to come just out of nowhere, I notice Windows powershell is used, have the TLS been ruled out? https://www.reddit.com/r/PowerShell/comments/ozr6ye/psa_enabling_tls12_and_you/ ?

Citing OP in full, from 3yr ago.

---

Annoyingly Windows Powershell does not enable TLS 1.2 by default and so I have seen a few posted scripts recently using the following line to enable it for Powershell:

   [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12

This does what is advertised and enables TLS 1.2. What it also does that is often not mentioned, is disable all other TLS versions including newer protocols. This means if an admin or user has enabled TLS 1.3 or new protocols, your script will downgrade the protections for those web calls.

At some point in the future TLS 1.2 will be deprecated and turned off. If your script is still running (nothing more permanent that a temporary solution,) and it is downgrading the TLS version you might find it stops working, or worse opens up a security issue.

Instead you want to enable TLS 1.2 without affecting the status of other protocols. Since the Value is actually a bitmask, it's easy to only enable using bitwise or. So I suggest that instead you want to use the following code:

[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [System.Net.SecurityProtocolType]::Tls12

I don't think it will affect anyone now, but maybe in a few years you might have avoided an outage or failed process.

I just wanted to awareness of an easily miss-able change in what their code might be doing.