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
