MySQL Workbench equivalent with possibility of two-factor authentication

Question

I'm trying to access a remote database using MySQL Workbench, but the server has a two-factor authentication and, apparently, Workbench does not have this feature.

So, I'm looking for a GUI MySQL client to connect to my remote database using the Connection Method "Standard TCP/IP over SSH" but with a two-factor authentication (TOTP).

My requirements are really basic. I just need to do some SELECTS with results in a table format and some CREATE/DELETE work.

OBS: I can do the job accessing the server via SSH and using the command-line, but I'm looking for something visual.

My configuration:

• Ubuntu 14.04;

Server configuration:

• CentOS 7.2;
• MariaDB 5.5.44;

Answer 1

I am actually looking at this same issue myself, per my boss wants GUI access to our databases for himself and the developers at my company. I have found that Jetbrain's Datagrip works for 2FA (key and Google Auth, in my case), albeit it was initially a little bit finicky to actually get the auth codes through and connected.

As an aside, the only other viable solution I have found for having 2FA compliance would be moving my Google auth off of the server and into a remote VPN solution.

Answer 2

Annoying that MySQL Workbench still lacks this in 2021:

But, luckily, SSH tunnels can help. Simply SSH to the server using whatever client, then create the necessary tunnel. Since Ubuntu 20.04 LTS is my daily driver, my example below is for this environment:

remotessh=mysqlserver.example.com
localport=3307 (I already have local MySQL using up port 3306)
remoteport=3306

ssh -L${localport}:localhost:${remoteport} ${remotessh}

Once you are connected to remote mysqlserver.example.com, You can now create the MySQL Workbench connection:

Clicking the Test Connection button should show you a success message:

When testing the connection, you may receive the following warning (which you can disregard with the caveat that some features might not be available. This warning comes about because my remote MySQL Server is running MariaDB Server v10 which is clearly not one of the ones mentioned in the warning):

--Enjoy!

Answer 3

Google Authenticator can be used with Workbench for two-factor authentication, but you will have to change how SSH and Google Authenticator work together.

When MFA using libpam-google-authenticator is enabled on Ubuntu user login over SSH most likely follows this sequence:

1. The user is prompted to enter their password
2. The user is prompted to enter their two-factor pin

Because Mysql Workbench only prompts once for SSH credentials the above sequence will not work, thus any attempt to login via Workbench will fail. This can be fixed by having the user enter their password and pin back to back on a single line like this:

[password][two-factor-pin]

Install Google Authenticator

sudo apt-get install libpam-google-authenticator

Create a custom PAM config file called “mfa-auth” using common-auth as a base

sudo cp /etc/pam.d/common-auth /etc/pam.d/mfa-auth
sudo nano /etc/pam.d/mfa-auth

Add and/or modify mfa-auth to include these lines

auth [success=1 default=ignore] pam_unix.so nullok_secure try_first_pass
auth required pam_permit.so try_first_pass

The try_first_pass option: “Before prompting the user for their password, the module first tries the previous stacked mofule’s password in case that satisfies the module as well”. From https://linux.die.net/man/8/pam_unix

Edit /etc/pam.d/sshd and add Google Authenticator

sudo nano /etc/pam.d/sshd

Add these lines to the top of the file

auth required pam_google_authenticator.so nullok forward_pass
@include mfa-auth

Comment out line

@include common-auth

As we want to use mfa-auth to authenticate SSH connections now instead of common-auth.

Edit /etc/ssh/sshd_config to ensure SSH is using is using two-factor authentication

sudo nano /etc/ssh/sshd_config

Set ChallengeResponseAuthentication to yes

ChallengeResponseAuthentication yes

Restart SSH

sudo systemctl restart ssh

Set up Google Authenticator pin for each user with your desired settings

sudo su username
google-authenticator

Now when Workbench prompts for the SSH password users will need to enter their password and two-factor pin back to back with no spaces separating the password and the pin.

[password][two-factor-pin]

This new format will apply to any SSH connection.

See this post for further details and an example mfa-auth file: https://github.com/Katie-Shay/MFA-with-MySQL-Workbench