Verify Connection to MySQL is Encrypted

It's time to get outdoors and Explore

How to Verify Connections to the MySQL Server is Encryptd

If you are using Azure MySQL , AWS MySQL or AWS Aurora its absolutely important to enforce the SSL. By default Azure and AWS Cloud Providers enable SSL and force the traffic to through SSL , which prevents men-in-the-middle attacks. These are some simple steps to verify is SSL is enabled.

Verify through mysql STATUS
Simple approach is to connect to mySQL and run status command. If the encryption is enabled , you will notice SSL Cipher , otherwise you will see SSL not in use.
Encryption is enabled

You will notice **SSL: Cipher in use is ECDHE-RSA-AES256-SHA384**



mysql> STATUS
--------------
mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

Connection id:      65246
Current database:   
Current user:       
SSL:            Cipher in use is ECDHE-RSA-AES256-SHA384 
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.6.39.0 MySQL Community Server (GPL)
Protocol version:   10
Connection:     mysqlhost.mysql.database.azure.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         8 days 4 hours 16 min 11 sec

Threads: 15  Questions: 992386  Slow queries: 0  Opens: 2599  Flush tables: 1  Open tables: 493  Queries per second avg: 1.404

Encryption is not enabled

Connect to MySQL Server

$mysql -h 192.168.1.125 -u "root" -p

mysql> STATUS
--------------
mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

Connection id:      6008
Current database:   
Current user:       root@Macbook.satx.rr.com
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.22-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     192.168.1.125 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         7 days 5 hours 1 min 41 sec

Threads: 3  Questions: 63671  Slow queries: 0  Opens: 543  Flush tables: 1  Open tables: 307  Queries per second avg: 0.102
--------------


Verify through mysql Ssl_cipher Variable Status

SHOW STATUS LIKE 'Ssl_cipher';

If SSL Enabled, you will see the ssl_cipher value 
# Variable_name, Value
'Ssl_cipher', 'AES256-SHA'

If SSL is not Enabled, you will see the ssl_cipher being empty 

# Variable_name, Value
'Ssl_cipher', ''

SELECT  sbt.variable_value AS tls_version,  t2.variable_value AS cipher, 
        processlist_user AS user, processlist_host AS host 
       FROM performance_schema.status_by_thread  AS sbt 
       JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id 
       JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id 
      WHERE sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher' ORDER BY tls_version;

# tls_version, cipher, user, host
'TLSv1', 'AES256-SHA', 'azure_superuser', '127.0.0.1'
'TLSv1', 'AES256-SHA', 'azure_superuser', '127.0.0.1'
'TLSv1', 'AES256-SHA', 'azure_superuser', '127.0.0.1'
'TLSv1', 'AES256-SHA', 'azure_superuser', '127.0.0.1'


Verify through Wireshark

If you have wireshare installed, you can track the packets over the wire and see the packets are flowing as encrypted. If SSL is not availae, you will see the clear text data being transferred over the wire.
Wire Shark Packet Capture of MySQL Data WireShark Capture

References :
Azure MySQL - Configure SSL
https://aws.amazon.com/blogs/aws/amazon-rds-support-for-ssl-connections/
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html#MySQL.Concepts.SSLSupport

Posted in AWS, Cloud Computing, MySQL on Jul 15, 2018


Comments

Please sign in to comment!