SSH Tunneling doesn't work

[ldub]

When using a postgres database with an ssh tunnel, beekeeper shows that the database has no tables.

[rathboma]

interesting. Let me investigate.

[XzAeRo]

I just tried with a MariaDB server and happens the same. After switching between several databases, they tables did show up... Maybe a loading/latency issue?

[bradennapier]

I can't even get tunneling to work at all? Just says it cant connect. Same config works flawless for SequelPro and SQLPro.. This is for MySQL connecting to Amazon RDS through a SSH tunnel.

[pdepip]

If I had to guess, I'd say a lot of these issues are stemming from things like not supporting jump hosts, which could be resolved by taking the users ssh config file into consideration?

[ssuess]

Same issue here, trying to connect to MySQL. It connects (always on SECOND try btw, not first) and then shows no tables or dbs

[rathboma]

Yeah the tunneling code is a little flaky right now. Running queries through the tunnel isn't great.

I haven't spent much time on that code, but I'm prioritizing the connection bugs.

[hxhlb]

me too. @ssuess

[dufferzafar]

For me, the error is (SSH) Channel open failure: Connection refused while I'm able to connect using DBeaver just fine.

[rathboma]

My next milestone is to revamp the SSH and SSL libraries. Sorry this isn't working right :-(

[rathboma]

Can you all test this build? https://github.com/beekeeper-studio/beekeeper-studio/actions/runs/106301681

You should be able to download the assets for your OS and install as usual

[ssuess]

This build seems to fix my connection and db listing problems. Thanks!

[chrisdlangton]

Using MySQL getting;

Error while signing data with privateKey: error:06000066:public key routines:OPENSSL_internal:DECODE_ERROR

Works flawlessly without SSH tunnel. Also I can SSH fine outside beekeeper (including mysql-workbench)

[rathboma]

What platform, key type are you using?

Eg windows, pem

[chrisdlangton]

considering pem (a public key format) is never going to work... I am using a private key generated via defaults of ssh-keygen RSA2048SHA256

Ubuntu 20.04 uname -a;

Linux alteran 5.4.0-29-generic #33-Ubuntu SMP Wed Apr 29 14:32:27 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Installed clean from instructions after the issue #40 I raised with snap and you closed as being due to a snap restriction and i solved it by not using snap, so not really resolving the issue but closed anyway..

My conclusion is Beekeeper for Linux is a toy/alpha tool used for localhost and not ready for enterprise/production use - maybe other platforms, perhaps more familiar to the devs, work in real world scenarios.

[rathboma]

Hey @chrisdlangton,

So sorry you're frustrated with the app :-(.

Beekeeper Studio is definitely early -- we only announce it on April 20th. SSH tunneling is something we adapted from an earlier library, and yes it is the most broken part of the product right now. I have some servers that I use for testing SSH, but not all combos of dbs and configs are covered. It's a bit of a cat and mouse game.

I'm trying to determine what folks are trying to use for their SSH connection configs. For example - private keys generated from putty have been causing issues, and because I don't have docs for SSH tunnels yet I've had to field several informal questions about private/public keys, hence me dropping PEM as a suggestion :-).

Follow up Questions

• What version of MySQL are you using? (8?)
• Are you also enabling SSL for your connection?
• When you are tunneling, are you keeping the hostname the same, or changing it to locahost? eg:

host: localhost, port: 3306
sshhost: myserver.com

# OR:
host: myserver.com, port; 3306
sshhost: myserver.com

Thanks for your help

[chrisdlangton]

@rathboma
Didn't mean the critical feedback to appear as frustration, text is.. not the best.

To give the project benefit of doubt the key pair was generated fresh, and the public key was added to authorized_keys of a digital ocean docker droplet. My production use case is EC2 Amazon Linux 2 (centos) as a temporary/breakglass bastion to access RDS/Aurora.

I tried various databases so versions, but the isolated test on the droplet was a container image mysql:5.7@sha256:ba2eda1bf1249bd7e7160fa6c446d03b3261f75d3de91bc2125967d39db1525c specifically docker-compose.yaml;

version: '3.6'
x-defaults: &defaults
  restart: unless-stopped
  logging:
    options:
      max-size: "10m"
      max-file: "3"

  mysql:
    <<: *defaults
    image: mysql:5.7@sha256:ba2eda1bf1249bd7e7160fa6c446d03b3261f75d3de91bc2125967d39db1525c
    container_name: mysql
    volumes:
      - /mnt/volume_sgp1_02/mysql_data:/var/lib/mysql
    command: '--max_allowed_packet=65011712 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=1024 --skip-show-database --skip-grant-tables'
    expose:
      - 3306
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - MYSQL_DATABASE=${MYSQL_DATABASE}
      - MYSQL_USER=${MYSQL_USER}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD}

and the tunnel uses ssh config for simplicity;

Host do-docker
  HostName www.example.com
  User root
  IdentityFile /home/<user>/.ssh/id_rsa
  Compression yes
  ConnectionAttempts 3
  ConnectTimeout 5
  IdentitiesOnly yes

Replace host and user. Not using SSL for the isolated test (yet), but RDS/Aurora does.

The mysql IP address is usually a 172. in docker, you can find it via docker inspect <image name> for this isolated test, but with RDS/Aurora you use a DNS record in the tunnel connection configuration not an IP

[rathboma]

Ok, so just to be clear, looks like your set-up is:

1. SSH tunnel to a droplet in digital ocean
2. Accessing MySQL 5.7 running on the digital ocean droplet, accessing with the docker IP and port 3306

Quick Question:

You're using expose for the port. Can you double check that you can connect to the container using the mysql command line on your droplet?

I'd thought expose only exposes that port to other containers, and you had to map it to a host port to actually access it.

[chrisdlangton]

Yes, happy to explain what expose does.
I had commented earlier that everything is working fine outside beekeeper, that includes the usual commands typed out as well as mysql workbench ssh tunnel connection (i'm looking at beekeeper studio because workbench is terribly buggy).

The expose directive is simply telling docker to allow communication on port 3306 from another container in the same docker network, meaning the port 3306 will not have a bind to the host interface (i.e. not a bind to 0.0.0.0:3306) and can only be accessed inside the host - thus the ssh tunnel. While it is designed to not bind to the host interface and only be exposed to other containers, there is a leak in a sense that if you know the correct docker network ip assigned to the container (usually a 172.x.x.x address on the host) then you can actually connect from inside the host, outside the docker network, but not from the host wan interface.

flow;
ssh > do.dns.record.co:22
docker inspect < ip address is 172.x.x.x
mysql -h 172.x.x.x -P 3306

Besides this Digital Ocean droplet isolated test, there is the EC2 > RDS which is technically a totally different setup but in terms of an ssh tunnel it is almost identical, just replace 172.x.x.x with the RDS dns record (after ensuring the EC2 host you ssh to can resolve that RDS dns record)

[rathboma]

Thanks, I'll test this today

[rathboma]

This is working in my latest build. I used the same docker-compose config as you, sshing onto a GCP box and then connecting to mysql on 172.x.x.x. \0/.

I have a few things to tidy up but my goal is to push out a new release this week with the fixes.

[chrisdlangton]

@rathboma amazing!
I'll do some testing when merged and deb built.
If you could tag the commit here when it is merged, I can debug anything i find in testing and maybe PR for you if i find anything, i noticed the other day it is mostly javascript so I should be trying to be directly useful on this project considering i know that language almost as well as i know English..