Jun 11 2007

Mysql Traffic Encryption with OpenSSL

Published by at 12:00 am under Freeradius,Mysql

This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. Freeradius is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from the laboratory Infogard has realised a very good analyse available on the web at http://www.untruth.org/~josh/security/radius/. This document relates possible attacks while sending login and password for authentication.
We won’t detail Radius protocol issues, but try to avoid some security problems when using a database system as a backend. Access to the Radius database must be secure of course, but the data transport from the Freeradius server also. This is really convenient when both servers are distant, which can be frequent with networks of big size. We propose to encrypt Mysql traffic with OpenSSL, the wildly used SSL engine, in these conditions.

OpenSSL Installation

OpenSSL can be downloaded from the official site. This test has been realised with the last stable version available today, 0.9.8e. It can be installed as follow:

./config –prefix=/usr/local/openssl shared zlib
make test
make install

“zlib” activates support for compression/decompression and “shared” for shared libraries. It’s important to note compiling Mysql will fail on a Linux plateform without the “shared” option.

New libraries must be included in the path then. Add path /usr/local/openssl/lib (or any other where you’ve installed OpenSSL) in the /etc/ld.so.conf file under Linux. Run command line ldconfig to make the new path active.

OpenSSL activation on the server

Mysql libraries are usually provided with YaSSL, to replace OpenSSL, limited by its license. (Re)compilation is then necessary:

./configure –enable-openssl=/usr/local/openssl
make install

To check wether Mysql server supports SSL after service restart, we need to examine the value of the system variable have_openssl like this:

mysql> SHOW VARIABLES LIKE 'have_openssl';
| Variable_name | Value    |
| have_openssl  | DISABLED |

If the value is “DISABLED”, the server supports SSL connections but wasn’t started with the correct options. Certificates needed for encryption must be created.

cd /usr/local/mysql
mkdir openssl && cd openssl
mkdir certs && cd certs

CA certificate generation
openssl genrsa -out ca-key.pem 2048
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem

Answer questions with appropriate data.
Openssl commands generate a 2048 bit key and a certificate valid for a thousand day period.

Server certificate generation
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Client certificate generation
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem -out client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Certificates related data must be given as parameters while launching the service. They can also be specified in Mysql configuration file /etc/my.cnf in section [mysqld].


Having restarted Mysql, SSL encryption should be available:

mysql> SHOW VARIABLES LIKE 'have_openssl';
| Variable_name | Value |
| have_openssl  | YES   |

As for now, the server accepts secure connections. It’s still possible to connect in clear mode as the choice is made on a per connection basis. It is possible to force a user to connect only in secure mode. This option resides in the user’s parameters and can be changed with the GRANT command.

OpenSSL activation on the client

There are several ways to connect to the server in SSL mode. Certificates are either given in option while launching Mysql client, or can be read from the configuration file /etc/my.cnf (or any other as we can modify the default file like this: mysql –defaults-file=my.cnf). Consequently, add these lines in the [client] section:


After being connected, the cipher used to encrypt data can be displayed:

SHOW STATUS LIKE 'Ssl_cipher';
| Variable_name | Value              |
| Ssl_cipher    | DHE-RSA-AES256-SHA |

In the case where Freeradius is the client, the number of SSL connections should rise with the same number of connections created in the pool, as defined in file sql.conf.

show status like 'Ssl_accepts';
| Variable_name | Value |
| Ssl_accepts   | 52    |

Performance Impact

Data encryption has of course an impact on performance as the operation is greedy in CPU and network usage. Here is a graphical visualisation of the number of transactions processed per second with and without encryption

Impact SSL sur les performances Mysql

These tests have been realised with a machine on which were running both Radius and Mysql services. They give however a good appreciation of encryption impact on performance that is around 30% slower.

No responses yet

Comments RSS

Leave a Reply