[SOLVED] remote mysql connection with vb.net over ssl

Issue

I am using MySQL workbench for accessing a remote database. This is currently working: MySQL workbench screenshot

Now i've tried to access the same database with visual studio 2017, vb.net. This is what i have so far:

Dim connectionInfo As PasswordConnectionInfo
Dim client As SshClient
Dim portFwld As ForwardedPortLocal
connectionInfo = New PasswordConnectionInfo("ipaddress", 22, "me", "password1") With {
        .Timeout = TimeSpan.FromSeconds(30)
    }
    client = New SshClient(connectionInfo)
    client.Connect()
    If client.IsConnected Then
        MsgBox("SSH")
    Else
        MsgBox("Wrong")
    End If

    portFwld = New ForwardedPortLocal("127.0.0.1", 0, "127.0.0.1", 3306)

    client.AddForwardedPort(portFwld)

    portFwld.Start()

    MsgBox("Started")

    conn = New MySqlConnection("server = 127.0.0.1; port = 3306; uid = 'me'; password = 'password'; database='xxx'")

    MsgBox(conn.ConnectionString)


    conn.Open()

Unfortunately, i get the following error on the line conn.Open():

: 'Authentication to host '127.0.0.1' for user 'me' using method 'caching_sha2_password' failed with message: Access denied for user 'me'@'localhost' (using password: YES)'

I suppose it has something to do with 'caching_sha2_password', because in mysql workbench --> Users and Privileges --> me, Authentication type is set to Standard.

Edit

I have changed these lines, and now its working :)

portFwld = New ForwardedPortLocal("127.0.0.1", 3305, "127.0.0.1", 3306)
(...)
conn = New MySqlConnection("server = 127.0.0.1; port = 3305; uid = 'me'; password = 'password'; database='xxx'; ssl mode = none;")

Two small additional questions:

1. Does it matter which port number i use? I've chosen for 3305, but can I chose random numbers?
2. I had to put 'ssl mode = none', is that a security risk?

Thanks

Solution

Your connection string points to port 3306, but you pass the value '0' to the ForwardedPortLocal constructor; this allows the system to select a random port.

I believe you should change your constructor to:

portFwld = New ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306)

i.e. set explicitly the boundPort parameter (the second one) the same port value you pass in your connection string.

Of course, if the 3306 local port is used (for example if you have MySQL running locally on the default port), this won't work; you should select another port, and use the same value in the porrFwld constructor and the connection string. The remote port should be 3306 (for a standard MySQL installation).

Answered By - Spyros P.

Answer Checked By - David Marino (WPSolving Volunteer)