Can OpenSSL be used to debug an SSL connection to a MySQL server?

I want my webserver to speak to the MySQL database server over an SSL connection. The Webserver runs CentOS5, the Database Server runs FreeBSD. The certificates are provided by a intermediate CA DigiCert.

MySQL should be using ssl, according to my.cnf:

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
ssl
ssl-capath = /opt/mysql/pki/CA
ssl-cert = /opt/mysql/pki/server-cert.pem
ssl-key = /opt/mysql/pki/server-key.pem

When I start MySQL, the daemon starts without errors. This suggests that the certificate files are all readable.

But when I try to connect from the webserver to the database server, I get an error:

[root@webserver ~]# mysql -h mysql.example.org -u user -p
ERROR 2026 (HY000): SSL connection error

And if I try to debug further with openssl:

[root@webserver ~]# openssl s_client -connect mysql.example.org:3306 0>/dev/null
CONNECTED(00000003)
15706:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:588:

Is this a valid way to test the SSL connection to a MySQL database server? The SSL23_GET_SERVER_HELLO:unknown protocol message is strange since this typically what you would see if you were speaking SSL on a port intended for non-SSL traffic.

This same openssl command seems to work fine with LDAP & HTTP servers:

$ openssl s_client -connect ldap.example.org:636  0>/dev/null
CONNECTED(00000003)
depth=2 /C=US/O=The Go Daddy Group, Inc./OU=Go Daddy Class 2 Certification Authority
...
$ openssl s_client -connect www.example.org:443  0>/dev/null
CONNECTED(00000003)
depth=0 /DC=org/DC=example/OU=Services/CN=www.example.org

Solution 1:

OpenSSL version 1.1.1 (released on 11 Sep 2018) added support for -starttls mysql in commit a2d9cfbac5d87b03496d62079aef01c601193b58. Unfortunately I cannot find the reference to this new feature in the OpenSSL changelog.

If your distribution does not have this version yet, there is a statically compiled openssl binary at https://testssl.sh/openssl-1.0.2k-dev-chacha.pm.ipv6.Linux+FreeBSD.tar.gz which does support -starttls mysql. I found the reference to it in http://www.danneman.org/presentations/Automating_TLS_Configuration_Verification.pdf.

For Windows the OpenSSL 1.1.1 binaries can be found at https://wiki.openssl.org/index.php/Binaries

I generated the SSL certificates as described in https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html, tried, and it works:

$ echo | bin/openssl.Linux.x86_64.static s_client -starttls mysql -connect spx-bionic.censored.com:3306 -CAfile /tmp/ca.pem
CONNECTED(00000003)
depth=1 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = mysql test CA
verify return:1
depth=0 C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = spx-bionic.censored.com
verify return:1
---
Certificate chain
 0 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=spx-bionic.censored.com
   i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
 1 s:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
   i:/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
---
Server certificate
-----BEGIN CERTIFICATE-----
CENSORED
-----END CERTIFICATE-----
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=spx-bionic.censored.com
issuer=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=mysql test CA
---
No client certificate CA names sent
Client Certificate Types: RSA sign, DSA sign, ECDSA sign
Requested Signature Algorithms: RSA+SHA512:DSA+SHA512:ECDSA+SHA512:RSA+SHA384:DSA+SHA384:ECDSA+SHA384:RSA+SHA256:DSA+SHA256:ECDSA+SHA256:RSA+SHA224:DSA+SHA224:ECDSA+SHA224:RSA+SHA1:DSA+SHA1:ECDSA+SHA1
Shared Requested Signature Algorithms: RSA+SHA512:DSA+SHA512:ECDSA+SHA512:RSA+SHA384:DSA+SHA384:ECDSA+SHA384:RSA+SHA256:DSA+SHA256:ECDSA+SHA256:RSA+SHA224:DSA+SHA224:ECDSA+SHA224:RSA+SHA1:DSA+SHA1:ECDSA+SHA1
Peer signing digest: SHA512
Server Temp Key: ECDH, P-521, 521 bits
---
SSL handshake has read 2599 bytes and written 632 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : ECDHE-RSA-AES256-GCM-SHA384
    Session-ID: AD25B7C3018E4715F262188D982AAE141A232712316E0A3292B0C14178E0F505
    Session-ID-ctx: 
    Master-Key: C121967E8FAEC4D0E0157419000660434D415251B0281CCBFC6D7A2AE8B0CC63AEFE22B332E91D31424C1BF03E5AF319
    Key-Arg   : None
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
    0000 - 82 db 03 0f c0 ce f2 26-62 bd 1b 18 71 03 88 db   .......&b...q...
    0010 - a6 66 7c 71 94 0c d5 ec-96 30 46 53 4a e6 cd 76   .f|q.....0FSJ..v
    0020 - 66 b3 22 86 7d 9f 7e 2c-14 1d 66 f2 46 8f d2 d3   f.".}.~,..f.F...
    0030 - f7 0a 0b f5 9e 05 97 e1-2b b3 ba 79 78 16 b8 59   ........+..yx..Y
    0040 - dc c5 0d a8 de 0b 3a df-4b ec f9 73 3f 4c c3 f1   ......:.K..s?L..
    0050 - 86 b6 f7 aa a7 92 84 77-9f 09 b2 cc 5d dd 35 41   .......w....].5A
    0060 - 23 5d 77 74 e1 96 91 ac-28 81 aa 83 fe fc d2 3c   #]wt....(......<
    0070 - f9 23 09 6d 00 e0 da ef-48 69 92 48 54 61 69 e8   .#.m....Hi.HTai.
    0080 - 30 0e 1f 49 7d 08 63 9e-91 70 fc 00 9f cd fe 51   0..I}.c..p.....Q
    0090 - 66 33 61 24 42 8f c2 16-57 54 48 ec 6a 87 dc 50   f3a$B...WTH.j..P

    Start Time: 1537350458
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
DONE

There is also -starttls support for postgres and ldap too in OpenSSL 1.1.1. See https://github.com/openssl/openssl/blob/OpenSSL_1_1_1-stable/apps/s_client.c#L815-L831 for the full list.

Solution 2:

Answering my own question. If you have a better answer with good, authoritative sources please post an answer.

Short answer; No, OpenSSL cannot be used to debug MySQL SSL connections. This is because MySQL starts the session using plaintext and switches over to SSL afterwards.

In reading https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html, MySQL starts off with a plaintext connection, and then the actual SSL is initiated afterwards. This explains how MySQL is able to listen on one port (port 3306) for both plaintext and encrypted connections. Compare this to a HTTP or LDAP server, where one port is used for plaintext connections and a second port is used for encrypted connections.

It starts with the client connect()ing to the server which may send a ERR packet and finish the handshake or send a Initial Handshake Packet which the client answers with a Handshake Response Packet. At this stage client can request SSL connection, in which case an SSL communication channel is established before client sends its authentication response

Solution 3:

A common cause of this problem is that the Common Name (CN) of the CA certificate is the same as the server and/or client certificate.

See here: https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-certs.html

Try re-create your CA & certificate(s) and ensure to use of a unique CN in all cases.

Solution 4:

I am running into a similiar issue with MacOS X client communicating with an Ubuntu server.

Can you check if the connection will work if you leave out the client side certificate and the client key, just having the CA for the server certificate? Are you able to establish an encrypted connection ? This will normally require the ANY setting for the associated user's ssl_type column.

Can OpenSSL be used to debug an SSL connection to a MySQL server?

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Related

Recent Posts