20

After creating a self-signed SSL certificate, I have configured my remote MySQL server to use them (and SSL is enabled)

I ssh into my remote server, and try connecting to its own mysqld using SSL (MySQL server is 5.5.25)..

mysql -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert
Enter password: 
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Ok, I remember reading theres some problem with connecting to the same server via SSL. So I download the client keys down to my local box, and test from there...

mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert 
Enter password: 
ERROR 2026 (HY000): SSL connection error

Its unclear what this "SSL connection error" error refers to, but if I omit the -ssl-ca, then I am able to connect using SSL..

mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.25 MySQL Community Server (GPL)

However, I believe that this is only encrypting the connection, and not actually verifying the validity of the cert (meaning I would be potentially vulnerable to man-in-middle attack)

The SSL certs are valid (albeit self signed), and do not have a passphrase on them. So my question is, what am I doing wrong? How can I connect via SSL, using a self signed certificate?

MySQL Server version is 5.5.25 and the server and clients are CentOS 5.

Thanks for any advice

Edit: Note that in all cases, the command is being issued from the same directory where the ssl keys reside (hence no absolute path)

Edit (in response to mgorven): ca.cert is the Certificate Authority certificate, which is supposed to tell mysql that my certificate authority is trusted.

The config from my.cnf is

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

I also tried adding ssl-cipher=DHE-RSA-AES256-SHA but have since removed it as it didn't help.

3
  • 2
    What is ca.cert? Is it the server's self signed certificate? Are you using client certificates for authentication? Please provide the SSL related configuration on the server.
    – mgorven
    Jun 17, 2012 at 3:41
  • Thanks, Ive updated my question with response and ssl config from server. The certificates being passed on commandline when trying to connect are indeed client certificates.
    – carpii
    Jun 17, 2012 at 4:09
  • Infact its a good question. In hindsight Im not sure it makes sense for client to be specifying the servers ssl-ca. But then without Im under the imrepssion the encrypted connection is not stricly authenticated
    – carpii
    Jun 17, 2012 at 4:13

3 Answers 3

14

Yes, you are correct that if you don't specify --ssl-ca then the client does not check the server certificate at all. Since it works without that option the most likely reason for the failure is that the client doesn't trust the server certificate.

If you are using self-signed client and server certificates then the ca.cert file should include both these files. That way the client will trust the server certificate and the server will trust the client certificate.

For example:
Generate the server key and certificate:

$ openssl req -x509 -newkey rsa:1024 \
         -keyout server-key-enc.pem -out server-cert.pem \
         -subj '/DC=com/DC=example/CN=server' -passout pass:qwerty

$ openssl rsa -in server-key-enc.pem -out server-key.pem \
         -passin pass:qwerty -passout pass:

Generate the client key and certificate:

$ openssl req -x509 -newkey rsa:1024 \
         -keyout client-key-enc.pem -out client-cert.pem \
         -subj '/DC=com/DC=example/CN=client' -passout pass:qwerty

$ openssl rsa -in client-key-enc.pem -out client-key.pem \
         -passin pass:qwerty -passout pass:

Combine the client and server certificates into the CA certificates file:

$ cat server-cert.pem client-cert.pem > ca.pem
3
  • Thankyou so much! The missing step is that I was not concatenating server and client certs into a ca.pem. I was instead passing the ca.cert which was generated initially (and then passed as --CA-key when generating client and server certs)
    – carpii
    Jun 17, 2012 at 23:58
  • Strange, but it always worked for me with just one CA cert - the same on client and server. Jun 18, 2012 at 3:23
  • Yes, as long as there are no special requirements for the DN - for example the CN being a specific value - then you can use the same key and self-signed certificate on the client and the server. Jun 18, 2012 at 20:55
5

To use one way ssl, you should try with:

mysql -u <user> -p --ssl=1 --ssl-ca=ca.cert --ssl-verify-server-cert

The --ssl-cert and --ssl-key on the mysql client are used for 2 way SSL. This means certificate based authentication. The subject of the client certificate should be the username.

1
  • 2
    Also bear in mind that when making a socket connection and using --ssl-verify-server-cert the CN of the server certificate must be the same as the host you specify for the -h command-line option. Jun 18, 2012 at 20:59
3

By any chance, have not you entered the same Common Name for server and client certs? If yes, replace one of them so that Common Names are different.

2
  • 1
    For me, Dmitry Leskov's solution worked. Per MySQL's SSL documentation: (dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html) > Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. A typical error in this case is: ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
    – gmas
    Apr 9, 2015 at 23:38
  • Just ran into this. Thanks for sharing.
    – waltmagic
    Feb 24, 2022 at 17:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .