SSH Tunnel Connect using SQLyog or MySQL Workbench fails

[Ray Dafflitto]

Have Debian 9 in this instance.

Trying to SSH tunnel to MariaDB server both using SQLyog and MySQL Workbench.  Error statement on SQLyog basically states Error Connection: timed out.  Error statement on MySQL Workbench basically states Authentication Error: Check username and password.

For username I've tried both root and a username with full admin privileges I placed in sudo group.  I gave root a password.  I generated an SSH key to use, but this key was associated with the username,  I did not generate an SSH key associated with root.

I have too complicated of databases to use PHPmyadmin since it has limited functionalities.

I read in one thread that GCE has security protocols regarding the use of root in a credential capacity; but rather, uses an SSH key.  I do not want to edit my config file, as noted in that thread, being a beginner I'm sure I'd f#*k that up and have more issues than i currently have. In addition, both SSH tunneling in SQLyog and MySQL Workbench require username, password as well as an SSH key.

Any assistance on this problem is greatly appreciated.

[Carlos (Cloud Platform Support)]

Hi Ray,

Something to keep in mind, the user defined to provide the tunnel does not necessarily have to be the same user defined to connect to the database.

i.e. You can connect to the database as “root” while using a different user in the SSH connection. In fact SSH access for root is not enabled by default. I believe that is true for any of the GCE public images.

My advice would be to test the tunnel and the general connections from the CLI. That can help you to isolate the issue. For example:

1) SSH in the server using the browser and test the connectivity to the DB server.

mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.2.12-MariaDB-10.2.12+maria~stretch-log mariadb.org binary distribution


2) From your remote location, use regular SSH to test you have the right user and private key to access the server.

Supposing you have setup “myuser” that works with the private key located at “~/.ssh/google_compute_engine” . To access the server with IP X.Y.W.Z  you can run:

ssh -i ~/.ssh/google_compute_engine myu...@X.Y.W.Z

Linux instance-3 4.9.0-5-amd64 #1 SMP Debian 4.9.65-3+deb9u2 (2018-01-04) x86_64


The SSH keys can be auto generated by using gcloud, but you can also manually add or remove keys by following this article. If this step works then you can test the tunnel

3) In one terminal window establish the SSH tunnel

ssh -i ~/.ssh/google_compute_engine -L 127.0.0.1:3306:127.0.0.1:3306 myu...@X.Y.W.Z -N

The connection should be established and them from another terminal window you could connect to the local port by using the mysql client

mysql -u root -p -P 3306 -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.5-10.2.12-MariaDB-10.2.12+maria~stretch-log mariadb.org binary distribution


If the previous steps work then you have a problem in the Workbench configuration. Something else to keep in mind is that you do not have a firewall or anti virus software that is locally avoiding the connections to be established. 

[Ray Dafflitto]

Carlos,  

Thanks for getting back to me with very detailed instructions.

I started following your debug and got the following at step 1:

$ mysql -u root -p

Enter password: 

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

In other words, in my  SSH in the server using the browser I entered $ mysql -u root -p put in the password for MariaDB root and got the ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I think that's a big problem. But I'm a beginner so trying to understand all this.

I also attempted to connect using the SSH key method in SQLyog and I think I got into the server but things went wrong trying to have MariaDB root log into MariaDB since the following error message occurred:

So, any assistance in getting MariaDB root to log in would be greatly appreciated.  Thanks in advance.

[Ray Dafflitto]

Carlos,

Quick update.

I used option 1 in this Stack Overflow thread ERROR 1698 (28000): Access denied for user 'root'@'localhost'  and that got me into MariaDB using SQLyog.  I'll spin-up another instance and test Option 2 since it is the recommended approach.  Please let me know if you see any problems or not best practices with using this Stack Overflow thread.

Thanks for all the assistance.

On Sunday, February 4, 2018 at 3:35:03 PM UTC-6, Carlos (Cloud Platform Support) wrote:

[Carlos (Cloud Platform Support)]

Hi Ray,

I am glad you have reduced the problem to the “root” user at the DB server. I am not an expert on DB servers, but the Stackoverflow discussion you are following seems to be a great guide. 

The Stackoverflow community is actually a great source of specialized knowledge.

[Ray Dafflitto]

Thanks Carlos for the feedback and that Stack Overflow is a good resource.  Being a beginner, I need all the good resource links I can accumulate.  Thanks again.