How to know whether mysql connection is using SSL or TLS connection?

Question

I want to verify whether mysql remote connection is using tls/ssl connection for security purposes.
I ran status command to check initially:

mysql> status
--------------
mysql  Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)

Connection id:      47
Current database:   
Current user:       user@127.219.252.250
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.5-10.3.27-MariaDB-0+deb10u1 Debian 10
Protocol version:   10
Connection:     31.224.603.2 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Binary data as:     Hexadecimal
Uptime:         35 min 15 sec

Threads: 9  Questions: 222  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 27  Queries per second avg: 0.104
--------------

Here the ssl row has mentioned a cipher but according to some people this doesn't guarantee that the connection is actually ssl encrypted.
To be 100% sure, I installed wireshark but I have never used this software before. I just started capture with the destination set to the ip of db server. Can this be used to make sure the connection is ssl/tls encrypted? Any help is appreciated.

I see ClientHello but not the server's response, I think your wireshark filter only shows packets from client to server but not the packets in the other direction, which are also important. Look for Mysql or MariaDB's documentation for how the certificate verification works (what is checked in the certificate and whether the certificate or its CA need to be trusted and how to configure which certificates or CAs are trusted). — Z.T., Feb 12 '21 at 23:01

score 1 · Answer 1 · answered Mar 07 '22 at 10:45

If you have sufficient privileges to run the following query on the server, it will tell you how it considers the connection is established:

select t.THREAD_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.CONNECTION_TYPE,
    sbt.VARIABLE_VALUE AS cipher
FROM performance_schema.threads t
LEFT JOIN performance_schema.status_by_thread sbt
    ON (t.THREAD_ID = sbt.THREAD_ID AND sbt.VARIABLE_NAME = 'Ssl_cipher')
WHERE t.PROCESSLIST_USER IS NOT NULL;

If you see something like SSL/TLS TLS_AES_256_GCM_SHA384 in the last two columns, you're good.

score 0 · Answer 2 · answered Feb 12 '21 at 22:39

It would be very, very odd to see "SSL: Cipher in use..." and then have the connection not be encrypted. (If you know of any case where this happens, please include a link.) However, it's possible that it would be less than fully secure for some reason, such as using (and trusting) a self-signed or otherwise potentially-untrustworthy certificate, or using an anonymous cipher suite, or using very weak encryption, or similar. Unfortunately, that's difficult to verify from the information given (aside from the strength of the encryption, which is the very strong AES256 cipher).

As the Wireshark screenshot shows, you're using TLS v1.1. That's not the latest, or even second-latest version - everything should be using TLS 1.2 or 1.3 these days - but at least it's not (to my knowledge) completely broken the way some older protocols are. You could look at the certificate that the server sent by viewing the relevant line in Wireshark, but that wouldn't necessarily tell you clearly whether or not the cert is trustworthy.

The simplest option might just be to look at the client command line (and options file) flags. If you used the ssl-mode=REQUIRED flag (or, for old versions, the -ssl=1 or --enable-ssl flags), then the use of SSL/TLS is forced on. Various other flags control things like what CA certificates are trusted, though it should use a reasonable list by default.

When i add the ssl mode flag, I am unable to connect to server from the client. On the client side I have not added any certificates. Do we need to add some on the client side as well? — ANWESH MOHAPATRA, Feb 12 '21 at 22:45
What is the error? Use flags to add verbosity if needed. Simply saying "unable to connect" doesn't tell us anything about the problem. As for the certs, no, you almost certainly don't need a client certificate. Those are rare and you shouldn't be able to connect at all if one is expected. — CBHacking, Feb 13 '21 at 22:24

How to know whether mysql connection is using SSL or TLS connection?

2 Answers2