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?
mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled
1I think we clarified that yesterday in the #workbench IRC channel: MySQL Workbench does not work with two-factor authentication.– Mike LischkeFeb 25, 2015 at 8:51
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:
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.
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:
- The user is prompted to enter their password
- 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:
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
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
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.
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