0

I cannot access MySQL 8.0 on a Centos 8 server remotely with an error:

ERROR 2026 (HY000): SSL connection error: unknown error number  

I can see port 3306 when doing a port scan remotely using nmap and I can also log into the mysql user from the server command line.

Where do I start to look for this error? The connection when connecting from Mac mysql client on the terminal with the following command:

mysql -h <server-ip> -u remote_user --ssl-ca=~/.ssl/ca.pem --ssl-cert=~/.ssl/client-cert.pem --ssl-key=~/.ssl/client-key.pem -p

I have followed the following instructions:

nano /etc/my.cnf add line:

bind-address = 0.0.0.0

iptables -A OUTPUT -p tcp --sport 3306 -j ACCEPT
iptables -I INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
sudo firewall-cmd --zone=public --permanent --add-service=mysql
sudo systemctl restart firewalld

Disable SELinux

setenforce 0

Step 1 - Generate New Self-Signed Certificate Files

Create a new directory for the SSL certificate files '/etc/certs/' and go to that directory.

mkdir -p /etc/certs
cd /etc/certs

Generate new CA certificate ca.pem file.

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

Next, we have to create the server-side certificates. Create new server certificate server-cert.pem and server-key.pem files. Generate new certificate files, remove the passphrase and sign them with the CA certificate.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Now generate certificate files for the client. The MySQL server will only accept remote connection from the client who has these certificate files. Generate new client certificate files, remove the passphrase and sign them using the CA certificate.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

The CA certificate info must be different from the client and server info. All certificates that we require for this setup have been generated. Now we can to verify certificate files with the command below.

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

Make sure there is no error.

All Certificate files have been verified. Change the owner of the certs directory to the 'mysql' user and change permissions of all key files.

chown -R mysql:mysql /etc/certs/
chmod 600 client-key.pem server-key.pem ca-key.pem

Step 2 - Enable SSL for MySQL

mkdir -p ~/mysql-certs/

Go to the '/var/lib/mysql' directory and move all default certificates to the backup directory.

cd /var/lib/mysql/
mv ca.pem ca-key.pem ~/mysql-certs/
mv server-key.pem server-cert.pem ~/mysql-certs/
mv client-key.pem client-cert.pem ~/mysql-certs/
mv private_key.pem public_key.pem ~/mysql-certs/

Now restart the MySQL service.

systemctl restart mysqld

Next, check SSL from the MySQL shell. Login to mysql with the new password:

mysql -u root -p

TYPE NEW PASSWORD

Run the query below so we can see the SSL status for MySQL.

SHOW GLOBAL VARIABLES LIKE '%ssl%';
STATUS;

You will see SSL status is 'DISABLED', and the root user has been connected without SSL. Next, we will enable the SSL connection for MySQL. We have to edit the MySQL configuration file 'my.cnf' file with nano.

nano /etc/my.cnf

In the '[mysqld]' section, paste the configuration below. Type your own certificates directory

[mysqld]
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem
ssl-key=/etc/certs/server-key.pem

Save the file and exit nano, the restart MySQL service.

systemctl restart mysqld

Now login to the MySQL again and check the SSL.

mysql -u root -p
TYPE NEW PASSWORD

Run query below to make sure SSL section value is 'YES'.

SHOW VARIABLES LIKE '%ssl%';
STATUS;

nano /etc/my.cnf Paste configuration below to the end of the line.

[client]
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/client-cert.pem
ssl-key=/etc/certs/client-key.pem

Save and exit, then restart MySQL.

systemctl restart mysqld

Connect to the MySQL again and check the connection and SSL enabled.

mysql -u root -p
TYPE NEW PASSWORD

SHOW VARIABLES LIKE '%ssl%';
STATUS;

Step 3 - Enable Remote Connections

In the steps above, we've already enabled SSL for the MySQL server, and local connections are forced to use SSL. In this step, we will enable remote connections for MySQL, but we allow only clients have certificate files signed by our CA to connect to the MySQL server.

Edit MySQL configuration file with nano.

nano /etc/my.cnf

Paste this configuration below at the end of the '[mysqld]' section.

bind-address = *
require_secure_transport = ON

Save the file and exit the editor, the restart MySQL.

systemctl restart mysqld

Next, create a new user for the remote connection. I will create a new user named 'hakase' with password 'Hakase-labs123@' and grant all privileges to the 'hakase' user. 'hakase' user will only be able to connect with the Certificate key.

mysql -u root -p
TYPE NEW PASSWORD

CREATE USER ‘remote_user'@'%' identified by ‘PASSWORD' REQUIRE X509;
GRANT ALL ON uspto.* TO ‘remote_user'@'%’;
FLUSH PRIVILEGES;

Step 4 - Testing

In the final section of this tutorial, we will test the if the new user named 'hakase' is able to connect to the MySQL server remotely with certificate files. Three certificates for the client must be downloaded.

CA certificate - ca.pem
Client certificate - client-cert.pem
Client key - client-key.pem

I've logged into another Linux system and installed the mysql-client packages there. Then I've downloaded all client certificate files with SCP. Now I will connect to the MySQL server with the certificate files.

mysql -h <server-ip> -u remote_user \
--ssl-ca=ca.pem \
--ssl-cert=client-cert.pem \
--ssl-key=client-key.pem -p
5
  • did the tutorial don't mention open the firewall for port 443 as well?
    – nbk
    Jun 18 '20 at 20:05
  • No, the tutorial did not mention to open 443. 443 is not used in MySQL SSL connection. The SSL connection happens over client and server ports 3306. Jun 18 '20 at 20:07
  • Also, I am able to get this connection working very quickly in Centos 7.6 and MariaDB instead of using Centos 8 and MySQL 8. I suspect it may because of the way that MySQL 8 handles the password authentication but, not sure. Infact, it's also running through SELinux enabled in Centos 7.6 Jun 18 '20 at 20:08
  • yes you are rught port 3306 as to be open, and mysql 8 has to use the natove password plugin see dev.mysql.com/doc/refman/8.0/en/…
    – nbk
    Jun 18 '20 at 20:11
  • Yes, it's using that password plugin as default in the config file. Jun 18 '20 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.