My setup is running MySQL (MariaDB 5.5, an upgrade is in the queue but not within the next few months) on db-host and a Tomcat server on web-host. Some time ago we configured database connections to use a certificate bundle for the CA. TLS-encrypted connections worked, as best we can recall.
Recently the connections have been getting rejected. I logged on to db-host and attempted to connect:
db-host> mysql -h localhost -u dbuser -p --ssl-ca=/etc/pki/bundle-of-certs.crt --ssl-verify-server-cert
Enter password: (correct password entered)
ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure
When I connected to the database without ssl-verify-server-cert, the connection succeeded. But ssl_cipher was blank. I have confirmed that ssl_ca is set to this certificate file, and ssl_cert and ssl_key are set to the correct certificate and key files for db-host.
The file /etc/pki/bundle-of-certs.crt contains several CA certificates. This file hasn't changed in several months, and the file contains multiple valid (and some expired) certificates.
I have confirmed that the server certificate file is valid:
db-host> openssl verify -CAfile /etc/pki/bundle-of-certs.crt /etc/pki/db-host.crt
/etc/pki/db-server.crt: OK
I have also used openssl x509 .... to confirm that db-host.crt contains the expected CN and SANs for db-host.
What have I not checked, or what can I do to fix this?
(Side note: The original issue is a web app establishing a TLS encrypted connection fails with SSHHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate). I believe this is due to the certificate validation failure; if I'm wrong about this, at least I'll fix the certificate validation.)
Update (in reply to Georg Richter)
I connected with mysql -h db-host -u user --ssl-verify-server-cert ..... I again got SSL certificate validation failure.
Just to double check, I ran show variables like '%ssl%';
| Variable_name | Value
+---------------+--------------
| have_openssl | YES
| have_ssl | YES
| ssl_ca | /etc/pki/bundle-of-certs.crt
| ssl_capath | (blank)
| ssl_cert | /etc/pki/db-host.crt
| ssl_cipher | (blank)
| ssl_key | /etc/pki/db-host.key
Update 2
db-host> mysql -u user -h db-host -p --ssl
Enter password:
MariaDB [(none)]: \s
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
MariaDB> show variables like 'ssl_cipher'
| ssl_cipher | (blank)
Those query results do not agree........
I went back and connected with --ssl-ca --ssl-cert --ssl-key on the command line with their correct values. Just like this test, \s reported an RSA cipher in use but show variables didn't. Whiskey tequila flaming-moe??
(Update 2.1)
I double checked the server certificate. X509 Subject Alternative Name includes DNS: db-host.foo.bar.baz, DNS:db-host, DNS:localhost. I conclude that connecting with the full hostname, short hostname, and localhost are all equivalent from the certificate point of view.
Update 3
I found a useful article at https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/. It showed me that a status cipher can be present with ssl_cipher being blank. From that I ran:
> show status like 'ssl_version';
| Ssl_version | TLSv1.2
> show status like 'ssl_cipher';
| Ssl_cipher | DHE-RSA-AES256-GCM-SHA384
> show variables like 'ssl_cipher';
| Ssl_cipher | (blank)
I now suspect that my initial question was valid, but I've been searching in the wrong direction for the answer.
