Open-MySQLConnection fails on 2.x with -SSLMode Required

[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 `