Error on connecting MySQL Server with PSCredential object error: Can not provide password

[alive-one]

Hello. I have copyed example script from WIKI https://github.com/mithrandyr/SimplySql/wiki/Open-MySqlConnection
And changed it a bit so it is even simpler. It must just open connection, execure siple query and close connection.

Import-Module -Name SimplySQL

$password = ConvertTo-SecureString "mypass" -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ("sqlwriter", $password)

Open-MySqlConnection -server '192.168.0.70' -connectionname 'localhost' -database 'pcinfo' -Credential Cred
$testconnection = Test-SqlConnection -ConnectionName 'localhost'

if ($testconnection) {
$InsertQuery = "INSERT INTO table1 (value1, value2) VALUES (@label1, @label2)"
Close-SqlConnection -connectionname 'localhost'
}

But unfirtunately, I can not initiate a connection. I recieve PSCredential error, yet it is written according to all MS recomendations.
Here is an error:
Open-MySqlConnection: Cannot bind parameter "Credential". Cannot convert value "Cred" of type "System.String" to type "System.Management.Automation.PSCredential".

I doublechecked and I can connect to database with given credentials via MySQL Workbench. $Cred is also a PSCredential Object and password has "secure string" type

[mithrandyr]

@alive-one There is, I think, a small typo in your script that I believe is the source of the problem:

# your original line, where you are opening the connection
Open-MySqlConnection -server '192.168.0.70' -connectionname 'localhost' -database 'pcinfo' -Credential Cred

# what the line should be, notice that instead of 'Cred', we now have '$Cred'
# in your line, you were passing the string value "Cred" to the -Credential Parameter
Open-MySqlConnection -server '192.168.0.70' -connectionname 'localhost' -database 'pcinfo' -Credential $Cred

That should address the error you are receiving.

[mithrandyr]

@alive-one can you provide the full error that you are receiving? with the full stack trace?
also, provide the results of $PSVersionTable if you can.

Also, please try running this test

Open-MySqlConnection -Server "192.168.0.70" -Credential (Get-Credential)
Close-SqlConnection

I'm seeing a different error the second time around than the first -- but not the full error details, so its hard to determine what might be going on. I do know that SimplySql does work with providing credentials this way for MySql... but i'm testing against a 5.5.5-10.3.6-MariaDB instance of MySql -- so you might want to also provide which version of MySql you are trying to connect against.

[alive-one]

Sure! Thank you for answering.
Here is $PSVersionTable
PS C:\Windows\system32> $PSVersionTable

Name Value

---

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

Here is full error log:
PS C:\Windows\system32> D:!PS-Autoren\default-from-wiki.ps1

Exception : System.Management.Automation.ParameterBindingException: Cannot bind parameter "Credential". Cannot convert value "-System.Management.Automation.PSCredential" of type "System.String" to type "System.Management.Automation.PSCredential". ---> System.Management.Automation.PSInvalidCastException: Cannot convert value "-System.Management.Automation.PSCredential" of type "System.String" to type "System.Management.Automation.PSCredential".
at System.Management.Automation.LanguagePrimitives.ThrowInvalidCastException(Object valueToConvert, Type resultType)
at System.Management.Automation.LanguagePrimitives.ConvertNoConversion(Object valueToConvert, Type resultType, Boolean recurse, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable)
at System.Management.Automation.LanguagePrimitives.ConversionData1.Invoke(Object valueToConvert, Type resultType, Boolean recurse, PSObject originalValueToConvert, IFormatProvider formatProvider, TypeTable backupTable) at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursion, IFormatProvider formatProvider, TypeTable backupTypeTable) at System.Management.Automation.ParameterBinderBase.CoerceTypeAsNeeded(CommandParameterInternal argument, String parameterName, Type toType, ParameterCollectionTypeInformation collectionTypeInfo, Object currentValue) --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception) at System.Management.Automation.Interpreter.ActionCallInstruction2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
TargetObject :
CategoryInfo : InvalidArgument: (:) [Open-MySqlConnection], ParameterBindingException
FullyQualifiedErrorId : CannotConvertArgumentNoMessage,SimplySql.Cmdlets.OpenMySqlConnection
ErrorDetails :
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at , D:!PS-Autoren\default-from-wiki.ps1: line 9
PipelineIterationInfo : {}
PSMessageDetails :

Code to test connection

Import-Module -Name SimplySQL
Open-MySqlConnection -Server "192.168.0.70" -Credential (Get-Credential)
Close-SqlConnection

raise next error:
Open-MySqlConnection : Couldn't connect to server
D:!PS-Autoren\SimplySQL-test.ps1:4 знак:1

• Open-MySqlConnection -Server "192.168.0.70" -Credential (Get-Credenti ...

•   + CategoryInfo          : OpenError: (default:String) [Open-MySqlConnection], MySqlException
    + FullyQualifiedErrorId : OpenMySQLConnection.Error,SimplySql.Cmdlets.OpenMySqlConnection
  
  

Close-SqlConnection : Connection 'default' does not exist.
D:!PS-Autoren\SimplySQL-test.ps1:5 знак:1

• Close-SqlConnection

•   + CategoryInfo          : ObjectNotFound: (default:String) [Close-SqlConnection], ConnectionNotFound
    + FullyQualifiedErrorId : Close-SqlConnection,SimplySql.Cmdlets.CloseSqlConnection
  
  

I am using MySQL version 8:
mysql -V
mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

[mithrandyr]

@alive-one Ok, so in your script there is something off about how you are creating the PSCredential object.

 Cannot convert value "-System.Management.Automation.PSCredential" of type "System.String" to type "System.Management.Automation.PSCredential".

So, instead of using the syntax New-Object -- just use this syntax [pscredential]::new("sqlwriter", $password) to create the PScredential object and you can see if that corrects the error above.

That said, lets try running this to see if we can get exactly what is going wrong.

$error.Clear() #clear any existing errors from the session
Open-MySqlConnection -Server "192.168.0.70" -Credential (Get-Credential) 
$error[0] | Select * #this will show us the entire error details.

[alive-one]

Thanks. Here is code:
Import-Module -Name SimplySQL
$password = $password = ConvertTo-SecureString "password11" -AsPlainText -Force
$Credential = [pscredential]::new("sqlwriter", $password)
$error.Clear() #clear any existing errors from the session
Open-MySqlConnection -Server "192.168.0.70" -Credential $Credential
$error[0] | Select * #this will show us the entire error details.

Here is en error:
PS C:\Windows\system32> D:!PS-Autoren\SimplySQL-test.ps1
Open-MySqlConnection : Couldn't connect to server
D:!PS-Autoren\SimplySQL-test.ps1:11 character:1

• Open-MySqlConnection -Server "192.168.0.70" -Credential $Credential

•   + CategoryInfo          : OpenError: (default:String) [Open-MySqlConnection], MySqlException
    + FullyQualifiedErrorId : OpenMySQLConnection.Error,SimplySql.Cmdlets.OpenMySqlConnection
  
  
  
  

writeErrorStream : True
PSMessageDetails :
Exception : MySqlConnector.MySqlException (0x80004005): Couldn't connect to server ---> System.ArgumentException: The specified value is invalid in the 'SslProtocolType' enumeration.
Parameter name: sslProtocolType
at System.Net.Security.SslState.ValidateCreateContext(Boolean isServer, String targetHost, SslProtocols enabledSslProtocols, X509Certificate serverCertificate, X509CertificateCollection clientCertificates, Boolean remoteCertRequire
d, Boolean checkCertRevocationStatus, Boolean checkCertName)
at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
at MySqlConnector.Core.ServerSession.d__121.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.d__103.MoveNext()
at MySqlConnector.Core.ServerSession.d__103.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.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.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.d__132.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.d__29.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
ScriptStackTrace : at , D:!PS-Autoren\SimplySQL-test.ps1: line 11
PipelineIterationInfo : {0, 1}

The error message indicates that the script "SimplySQL-test.ps1" failed to establish a connection to the MySQL server at IP address 192.168.0.70. The error is a MySqlException caused by an invalid SSL protocol type.

I tend to believe that PSCredential object can not be properly created beacause of some problem with SSL encription. Could it be because of Windows 10 version I use, which is Windows 10 for educational purposes?

[mithrandyr]

@alive-one Apologies for the long delay -- I think the PSCredential object is a red-herring. Looking closely at the error message I see:

System.ArgumentException: The specified value is invalid in the 'SslProtocolType' enumeration.
Parameter name: sslProtocolType

This leads me to think that something else is going on. Doing some searching I came across this issue Error with SSL.... I would double check which SSL settings your MySql instance requires and validate that you have updated TLS protocols on both your mySql server and your client (Windows 10 is probably up to date).