Problems setting up SSL connections for MySQL on Ubuntu 14.04

Question

I've been banging my head for a few days on this and I'm really at the end of my rope...

I'm trying to set up SSL connections on MySQL 5.7.10 running on ubuntu 14.04 and no matter what I do, the user required to use SSL is always rejected with access denied when trying to connect.

I was able to set up SSL easily on Windows (our dev machines) but for the love of me cannot get it to work on Linux.

I tried to use the certificates provided when installing MySQL (located in the /var/lib/mysql directory). I also tried to generate new ones using this procedure. I even tried to import the certificates that I generated using MySQL Workbench on Windows (the ones that actually worked on windows) but nothing works.

When starting up MySQL, SSL seems to be okay, as I only get this in /var/log/mysql/err.log

2015-12-17T18:25:32.687582Z 0 [Warning] CA certificate /var/lib/mysql/ca.pem is self signed.

SSL is ON in MySQL

mysql> SHOW VARIABLES LIKE '%SSL%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+

I've put the paths to the server and client certificates in the /etc/mysql/my.cnf

[client]
# SSL Settings
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

[mysqld]
# SSL Settings
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

I even tried to disabled appArmor for mysql in case that would do it, bot I alwas get the sema result when trying to connect a test user requiring ssl as such:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
GRANT USAGE ON *.* TO 'test'@'localhost' REQUIRE ssl;
FLUSH PRIVILEGES;

When trying to connect:

> /usr/bin$ mysql -u test -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

Same thing when manually specifying the client certificates:

> mysql --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem --host=localhost --user=test --password
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

Does anybody have any idea? I fail to see why a setuyp that works fine on windows would give me such grief on linux.

Is there a way to debug this further?

Thansk in advance /Sebas

Try: $ mysql --ssl -u test -p, after: mysql> SHOW SESSION STATUS LIKE 'Ssl_version';. — wchiquito
– wchiquito, Commented Dec 18, 2015 at 5:57
I'm not sure I understand what you mean. I cannot connect using ssl users, therefore when I connect (using non-ssl users), the Ssl_version value is empty. — Sébastien Dubois
– Sébastien Dubois, Commented Dec 18, 2015 at 13:39
For what it's worth I'm using the MySQL community edition so it only supports yaSSL not Openssl — Sébastien Dubois
– Sébastien Dubois, Commented Dec 18, 2015 at 13:39

wchiquito · Accepted Answer · 2015-12-20 16:48:14Z

1

My test:

error.log:

[Warning] CA certificate ca.pem is self signed.

$ sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION(); -- MySQL Community Server
+-----------+
| VERSION() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

$ mysql -u test -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

$ mysql -u test -p --ssl
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> SHOW SESSION STATUS LIKE '%Ssl_version%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+
1 row in set (0.00 sec)

UPDATE: The test details.

mysqld.cnf:

[client]
...
# SSL Settings
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem

[mysqld]
...
# SSL Settings
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

Yes, certificates are auto-generated by MySQL. See 6.3.13 Creating SSL and RSA Certificates and Keys. Check the security permissions to access the certificates.

error.log:

[Warning] CA certificate /var/lib/mysql/ca.pem is self signed.

$ sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION(); -- MySQL Community Server
+-----------+
| VERSION() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+
9 rows in set (0,01 sec)

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

$ mysql -u test -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

$ mysql -u test -p --ssl
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> SHOW SESSION STATUS LIKE '%Ssl_version%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+
1 row in set (0.00 sec)

edited Dec 20, 2015 at 16:48

answered Dec 18, 2015 at 14:51

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

A l w a y s S u n n y Over a year ago

I've tried to setup same everything but my mysql is not restarting after changing the my.cnf with above client and server changes

wchiquito Over a year ago

@AlwaysSunny What error do you get in the MySQL logs? Do you have the correct permissions to access the certificates?

A l w a y s S u n n y Over a year ago

now restarting works but when I try mysql -u root -p it returns ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed, then I try to use the --ssl params but those are not showing any error but only some messages. see: pasteboard.co/cyUhQWlQdVMf.png

wchiquito Over a year ago

@AlwaysSunny Check the path of the certificates.

A l w a y s S u n n y Over a year ago

yes sir, the same file paths works fine while I am testing with MySQL workbench(without SSL not connecting and with SSL certificates it works). but not sure why not with the MySQL command line though. I just moved those to desktop,

mysql -u root -p -h 127.0.0.1 –-ssl-ca=/home/kamal/Desktop/mysql-certs/ca.pem –-ssl-cert=/home/kamal/Desktop/mysql-certs/client-cert.pem –-ssl-key=/home/kamal/Desktop/mysql-certs/client-key.pem

thanks for the reply.

|

Collectives™ on Stack Overflow

Problems setting up SSL connections for MySQL on Ubuntu 14.04

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related