Home / server / Questions / 523808
Accepted
Stefan Lasiewski
Asked: 2013-07-17 11:16:51 +0800 CST

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

  • 772

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
mysql

6 Answers

  1. Best Answer

    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.

    • 20

  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

    • 12

  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.

    • 1

  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.

    • 1

  5. Just wanted to note for posterity that if you want to use a load balancer (such as an F5 or HAProxy) in between MySQL and the client, you will want to import your SSL certificate from the load balancer into the MySQL server. This is due to the STARTTLS-like connection startup.

    • 0

  6. I found another way of doing this instead of openssl s_client -starttls mysql, with the help of sslscan --starttls-mysql:

    $ sslscan --starttls-mysql mysql.example.com:3306
Version: 2.0.7
OpenSSL 1.1.1j  16 Feb 2021

Connected to 10.20.30.40

Testing SSL server mysql.example.com on port 3306 using SNI name mysql.example.com

  SSL/TLS Protocols:
SSLv2     disabled
SSLv3     disabled
TLSv1.0   enabled
TLSv1.1   enabled
TLSv1.2   enabled
TLSv1.3   disabled

  TLS Fallback SCSV:
Server supports TLS Fallback SCSV

  TLS renegotiation:
Secure session renegotiation supported

  TLS Compression:
OpenSSL version does not support compression
Rebuild with zlib1g-dev package for zlib support

  Heartbleed:
TLSv1.2 not vulnerable to heartbleed
TLSv1.1 not vulnerable to heartbleed
TLSv1.0 not vulnerable to heartbleed

  Supported Server Cipher(s):
Preferred TLSv1.2  256 bits  ECDHE-RSA-AES256-GCM-SHA384   Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-GCM-SHA384     DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-GCM-SHA256   Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-GCM-SHA256     DHE 2048 bits
Accepted  TLSv1.2  256 bits  ECDHE-RSA-AES256-SHA384       Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-SHA256         DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-SHA256       Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-SHA256         DHE 2048 bits
Accepted  TLSv1.2  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.2  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.2  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.2  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.2  256 bits  AES256-GCM-SHA384            
Accepted  TLSv1.2  128 bits  AES128-GCM-SHA256            
Accepted  TLSv1.2  256 bits  AES256-SHA256                
Accepted  TLSv1.2  128 bits  AES128-SHA256                
Accepted  TLSv1.2  256 bits  AES256-SHA                   
Accepted  TLSv1.2  128 bits  AES128-SHA                   
Accepted  TLSv1.2  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA
Preferred TLSv1.1  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.1  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.1  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.1  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.1  256 bits  AES256-SHA                   
Accepted  TLSv1.1  128 bits  AES128-SHA                   
Accepted  TLSv1.1  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA
Preferred TLSv1.0  256 bits  ECDHE-RSA-AES256-SHA          Curve P-256 DHE 256
Accepted  TLSv1.0  256 bits  DHE-RSA-AES256-SHA            DHE 2048 bits
Accepted  TLSv1.0  128 bits  ECDHE-RSA-AES128-SHA          Curve P-256 DHE 256
Accepted  TLSv1.0  128 bits  DHE-RSA-AES128-SHA            DHE 2048 bits
Accepted  TLSv1.0  256 bits  AES256-SHA                   
Accepted  TLSv1.0  128 bits  AES128-SHA                   
Accepted  TLSv1.0  112 bits  TLS_RSA_WITH_3DES_EDE_CBC_SHA

  Server Key Exchange Group(s):
TLSv1.2  141 bits  sect283k1
TLSv1.2  141 bits  sect283r1
TLSv1.2  204 bits  sect409k1
TLSv1.2  204 bits  sect409r1
TLSv1.2  285 bits  sect571k1
TLSv1.2  285 bits  sect571r1
TLSv1.2  128 bits  secp256k1
TLSv1.2  128 bits  secp256r1 (NIST P-256)
TLSv1.2  192 bits  secp384r1 (NIST P-384)
TLSv1.2  260 bits  secp521r1 (NIST P-521)
TLSv1.2  128 bits  brainpoolP256r1
TLSv1.2  192 bits  brainpoolP384r1
TLSv1.2  256 bits  brainpoolP512r1

  SSL Certificate:
Signature Algorithm: sha256WithRSAEncryption
RSA Key Strength:    2048

Subject:  mysql.example.com
Altnames: DNS:mysql.example.com, DNS:mysql-masters.example.com
Issuer:   R3

Not valid before: Oct  2 13:37:45 2021 GMT
Not valid after:  Dec 31 13:37:44 2021 GMT
    • 0