0

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

Databaas7894
  • 61
  • 8
  • I may be missing something, but how is this a "remote database", since your connection string points to 127.0.0.1? – Spyros P. Dec 05 '18 at 10:17
  • I am first connecting a remote server using ssh, and then trying to reach the database at 127.0.0.1, located at the server. Then it is a remote database, right? – Databaas7894 Dec 06 '18 at 07:31

1 Answers1

0

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).

Spyros P.
  • 296
  • 3
  • 14
  • I've already tried that. But that gives a different error: "An attempt was made to access a socket in a way forbidden by its access permissions". What exactly is the difference between the boundport and the remote port? – Databaas7894 Dec 07 '18 at 09:34
  • The bound port is the port on your local machine that will connect, through the SSH mechanism, to the remote machine. This is the port the connection string should point to. The remote port is the port on the remote machine where this connection will end. 3306 is the default MySQL port. The message you get probably means that port 3306 on your local machine is open by another process - most likely you have MySQL installed and running on your local machine. – Spyros P. Dec 07 '18 at 09:43
  • I have installed MySQL on my local machine indeed. But shouldn't then the first argument be the ipaddress of the remote server? Because i already have a ssh connection with the server. – Databaas7894 Dec 07 '18 at 09:55
  • 1
    The first argument is the local bound address, so, as far as I can tell, it's almost always 127.0.0.1 (in fact, there are overloads that omit it). You have started the SSH connection and then you forward boundHost:boundPort port to remoteHost:remotePort, but the remortHost is relative to your SSH connection endpoint (as indicated in the ipAddress paramenter of your PasswordConnectionInfo). So these "127.0.0.1" IPs do not refer to the same machine. Just use an unassigned port for the boundPort parameter of your `ForwardedPortLocal` and you should be good to go. – Spyros P. Dec 07 '18 at 10:15
  • Thank you very much for your explanations. It helped me a lot. I'm not sure if you saw the 2 questions in the edit of my main question, but do you know the answer to both? – Databaas7894 Dec 07 '18 at 10:47
  • That's great. Please accept the answer if helped you. As for the additional questions, the port number shouldn't matter, as long as you don't use one of the reserved ones (e.g. 80, 21 etc). Check this: https://stackoverflow.com/questions/2200199/how-do-you-decide-what-port-to-use. I'm not sure about the `ssl_mode` param; I believe the best practice is to set it to "Required". – Spyros P. Dec 07 '18 at 11:01