Activate SSL connection in MySQL

Secure connection? Today we will explore an interesting task about activating and using SSL connection in MySQL. We will provide all necessary scripts and commands to re-play all steps. So assume that we have CentOS 6.5 with OpenSSL and MySQL already installed:

[root@linuxsrv3 ~]# mysql --version 
mysql Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using EditLine wrapper
[root@linuxsrv3 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
[root@linuxsrv3 ~]# openssl version 
OpenSSL 1.0.1e-fips 11 Feb 2013
[root@linuxsrv3 ~]# rpm -qf `which openssl` 
openssl-1.0.1e-16.el6_5.7.x86_64 

Our test host is a Virtual Machine with static ip address: 192.168.1.77. The rest of contents of this article is quite straight just follow commands:

    ### Create Environment ### 
    [root@linuxsrv3 ~]# cd /etc/ 
    [root@linuxsrv3 etc]# mkdir mysql_ssl_certs 
    [root@linuxsrv3 etc]# ls -ld mysql_ssl_certs/ 
    drwxr-xr-x. 2 root root 4096 2014-05-27 17:33 mysql_ssl_certs/ 
    ### Create CA certificates ### 
    [root@linuxsrv3 etc]# cd mysql_ssl_certs/ 

    # 1 
    [root@linuxsrv3 mysql_ssl_certs]# openssl genrsa 2048 > ca-key.pem
    Generating RSA private key, 2048 bit long modulus ..............+++ .......................+++ e is 65537 (0x10001) 

    # 2 [root@linuxsrv3 mysql_ssl_certs]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem 
    You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. 
    ----- Country Name (2 letter code) [XX]:AZ 
    State or Province Name (full name) []:BAKU 
    Locality Name (eg, city) [Default City]:BAKU 
    Organization Name (eg, company) [Default Company Ltd]:Student
    Organizational Unit Name (eg, section) []:Student 
    Common Name (eg, your name or your server's hostname) []:shahriyar
    Email Address []:rzayev.sehriyar@gmail.com 

    # 3 ### Create server certificate # 

server-cert.pem = public key, server-key.pem = private key

    [root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem 
    Generating a 2048 bit RSA private key ...+++ ..........................+++ writing new private key to 'server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. 
    ----- Country Name (2 letter code) [XX]:AZ 
    State or Province Name (full name) []:BAKU 
    Locality Name (eg, city) [Default City]:BAKU 
    Organization Name (eg, company) [Default Company Ltd]:Student
    Organizational Unit Name (eg, section) []:Student 
    Common Name (eg, your name or your server's hostname) []:shahriyar
    Email Address []:rzayev.sehriyar@gmail.com 
    Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student 

    # 4 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key 

    # 5 [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
    Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key 

    # 6 ### Create client certificate ### 

client-cert.pem = public key, client-key.pem = private key

    [root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem 
    Generating a 2048 bit RSA private key .............................................................+++ ......................+++ writing new private key to 'client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. 
        ----- Country Name (2 letter code) [XX]:AZ 
        State or Province Name (full name) []:BAKU 
        Locality Name (eg, city) [Default City]:BAKU 
        Organization Name (eg, company) [Default Company Ltd]:Student
        Organizational Unit Name (eg, section) []:Student 
        Common Name (eg, your name or your server's hostname) []:shahriyar
        Email Address []:rzayev.sehriyar@gmail.com 
        Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student 

        # 7 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in client-key.pem -out client-key.pem 
        writing RSA key 

        # 8 
        [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem 
        Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key

The last step is to verifying certificates:

[root@linuxsrv3 mysql_ssl_certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK 

So let’s summurize that our certificates are stored in /etc/mysql_ssl_certs:

[root@linuxsrv3 mysql_ssl_certs]# ls -l /etc/mysql_ssl_certs/ 
total 32 
-rw-r--r--. 1 root root 1424 2014-05-27 17:37 ca-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:36 ca-key.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:46 client-cert.pem 
-rw-r--r--. 1 root root 1675 2014-05-27 17:45 client-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:45 client-req.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:41 server-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:41 server-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:40 server-req.pem

Going on we must modify MySQL Configuration file(my.cnf) to reflect our generated certificates. In my case my.cnf located in /etc But Before this check if MySQL installation supports SSL:

mysql> SHOW VARIABLES LIKE 'have_ssl'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| have_ssl | YES | 
+---------------+-------+ 
1 row in set (0,10 sec)

If you get “YES” open my.cnf and type under the [mysqld] catalog following:

[mysqld] 
# SSL 
ssl-ca=/etc/mysql_ssl_certs/ca-cert.pem
ssl-cert=/etc/mysql_ssl_certs/server-cert.pem
ssl-key=/etc/mysql_ssl_certs/server-key.pem 

Just NOTE that, this on server side (our virtual machine). Restart MySQL and create user and force it to only SSL connection:

create user 'ssluser'@'%' IDENTIFIED BY 'Pass@123#'; 
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'Pass@123#' REQUIRE SSL;

And let’s try to connect:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

Now, by logic we have a user that reuquires SSL, but our mysql client which we used to connect does not know about this. So we must again edit my.cnf but now under [client] catalog:

[client] 
ssl-cert=/etc/mysql_ssl_certs/client-cert.pem
ssl-key=/etc/mysql_ssl_certs/client-key.pem

Restart MySQL and test again:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
mysql> SHOW STATUS LIKE 'Ssl_cipher'; 
+---------------+--------------------+ 
| Variable_name | Value | 
+---------------+--------------------+ | 
Ssl_cipher | DHE-RSA-AES256-SHA | 
+---------------+--------------------+ 
1 row in set (0,08 sec)

That’s all. As you see our client Connection over ssluser is using SSL connection. In Final stage of our article o want to show some coding examples for developers. I use Python 2.7.6 with official mysql-connector package installed. To make article little i paste this codes on gist. So here is the first file is mysql_connection_without_ssl.py: mysql_connection_without_ssl.py
Note: There is an infinite loop inside code just for showing what is unsecured connection like.
Run this Python script(it will select from country table) and on another terminal run ngrep to read packages:

sh@sh-work:~$ sudo ngrep 
649.San Miguel.SLV.San Miguel.127696-....650.Nueva San Salvado r.SLV.La Libertad.98400!....651.Apopa.SLV.San Salvador.88800.....652.Asmara .ERI.Maekel.431000.....653.Madrid.ESP.Madrid.2879052$....654.Barcelona.ESP. Katalonia.1503451!....655.Valencia.ESP.Valencia.739412!....656.Sevilla.ESP. Andalusia.701927!....657.Zaragoza.ESP.Aragonia.603367!....658.M..laga.ESP.A ndalusia.530553.....659.Bilbao.ESP.Baskimaa.3575899....660.Las Palmas de Gr an Canaria.ESP.Canary Islands.354757.....661.Murcia.ESP.Murcia.353504)....6 62.Palma de Mallorca.ESP.Balears.326993-....663.Valladolid.ESP.Castilla and Le..n.319998"....664.C..rdoba.ESP.Andalusia.311708.....665.Vigo.ESP.Galici 

As you see we can see what is retrieved from our select statement. And know lets try The another script which uses SSL: mysql_connection_with_ssl.py Run This Python script. To do this you will have to copy certificates from remote server to local and ofcourse edit my.cnf on local machine to reflect to new certificates. So as you see from Python code we specify paths where certificates reside on our local machine.

root@sh-work:/home/sh/mysql-ssl# ls -l 
total 32 
-rw-r--r-- 1 root root 1424 May 27 18:39 ca-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 ca-key.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 client-cert.pem 
-rw-r--r-- 1 root root 1675 May 27 18:39 client-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 client-req.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 server-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 server-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 server-req.pem

Run Python script and on another terminal run ngrep again to read packages:

sh@sh-work:~$ sudo ngrep 
.OV.*z.S..~8j6.`.. W..j...f.X......0...S....#..d...E%.R.7..?3...<..d..C..T.....D...T.....=_......V..UI+.0s...- .....-.P...B.N,.&.....EF.t..E.e....im.P.....v.A.H...d.h..T..,.H..h.+..........(....F.....K.^.Y..).R.g.........Lh.Zq.0..........|S.......4z .-X.LWN.......q....N.D....^8r...p..x+si.e..`.,P.t..7...i.{c...>)...~...7..X..EC.E...,..W+..ik.58...%r.K..lwG8t'../..(A.A...u...'.VygP&..o ...(!..)..RH....B.Y.[..nD~S....~.*kUC.1....'.g.........k.......D.......^%4.^u....!U.gY.@.hGK&.-..Z8N."./..........1....5.g..N.........2t ..c.U.....W{.&g'...m...........<5d.x.=..$@..0x#.Y.%.......M.........!.....V,.k.2...,.r..:..1..CU.4.y.._.4#.z=.R..t....F......'z~....Lf.ks5 .tX..I...Z...]....)|Q.(.]$....v.P.U.bS..9..l...*].AY...K..o..* .C.%..q...}[.>..3.[..D....v.s.....$..q_.f...E...P8.Sg.Y..~)N..j#.$A..j.[Gm( ....A..`..._.]+.....S....&.k....v...}.f.....4...H.`..%.....$K.....=.V..^.A..ck......_>.s.[XE....:.O.!.S.k..J<..r....Y%..ZU....A...or.....D |l.^[.......H?.........._.NO.m..i.2*Kl

I think you see the difference 🙂 Thank you for reading if you have any questions feel free to ask.

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

2 thoughts on “Activate SSL connection in MySQL”

    1. Thank you for note about other available tools.
      Yes i have read about a new introduced tool for this purpose in MySQL 5.7.
      i have never heard about ‘mysslgen’, but definitely from know i will check it, and maybe you will see another article about this tool 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s