mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled

Question

I'm attempting to connect to my MySQL Database using the Connection Method "Standard TCP/IP over SSH". I filled out all the SSH and MySQL connection details correctly, however I get the message: "Authentication error." because I'm protecting SSH with Two Form Factor Authenticator (libpam-google-authenticator). Is there another way to connect and resolve this issue?

Answer 1

You can still use Google Authenticator with mysql workbench, but you will need to change how SSH and Google Authenticator work together. When you login via SSH, you probably enter your password and then you are prompted for your Two-Factor pin. You want to change this so that you enter your password and pin, one directly after the other, on one line as the password:

[my-password][two-factor-pin]

This will work with MySQL Workbench TCP/IP over SSH. I modify /etc/pam.d/sshd to use the forward_pass option for the Google Authenticator module. You will need to put a line like this at the top of /etc/pam.d/sshd:

auth required pam_google_authenticator.so nullok forward_pass

Google Authenticator will authenticate the pin, strip it from the password, and pass the password along to the other PAM modules.

Answer 2

Google Authenticator can be used with Workbench for two-factor authentication, but you will have to specify how SSH and Google Authenticator should handle the password and the pin.

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