Create Certificates and Use SSL with MySQL and the ADOdb Database Abstraction Library for PHP

This post is mainly for my own records and is the combination of 3 tutorials from KrWiki at waterlovinghead, Carl’s Whine Rack and Mad Irish. All 3 are almost 100% what I was looking for.

I needed to set up a secure database connection between our live web server and database server, both running Ubuntu Server. The 2 machines are completely separate and have their own IP address. The procedure for setting up an SSL connection between the two is similar to setting up a standard SSL certificate in Apache. First you create the certificates and sign them, then you configure the client (web server 192.168.0.2) and server (database server 192.168.0.1) to use them, finally you create a special MySQL user that uses SSL and connect using ADOdb. You don’t need to use ADOdb but it makes things a lot easier (see my previous posts).

It’s very important that your MySQL server has SSL enabled (most do by default). You can test this by connecting to your server and typing the SQL command:

SHOW VARIABLES LIKE '%ssl%'

Which should show “have_ssl” as either “DISABLED” or “YES”. If it says “NO” you will need to enable SSL (which might mean recompiling MySQL).

Creating the certificates

You need to create certificates for both the server (our database server) and the client (our web server). There are always a few ways of doing this described in various tutorials but this way is the easiest.

First create a directory somewhere that you will store your certificates in, e.g. “/var/ssl_certificates/” then go into the directory and type the following:

openssl genrsa 2048 > ca-key.pem

Which will generate a 2048 bit RSA private key for the Certificate Authority (CA). Now you need to create a Certificate Authority (CA) certificate, which will be used later to sign our server and client certificates. It is possible to use an external CA but in this case we are self signing, which makes things easier.

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

You will be asked a few details: country name, state or province name, locality name, organisation name, organisational unit name, common name and email address. Set the common name to the name of your server e.g. 192.168.0.1. The ca-cert.pem file created is our CA certificate.

Now you can create the server certificate request:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

This will ask for the same details as the CA certificate but will also ask you to put in a challenge password and optional company name. In our case, we are not going to be using the challenge password as we would have to enter it every time the SQL server started up.

Now sign the request using our CA certificate and key and generate the server certificate:

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now we need to create a client certificate request. We need to create one for each client, but in this case we only need to create one for the web server:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

You will be asked the same questions as for the server certificate request. The difference this time is that you need to put the client hostname in e.g. 192.168.0.2. Now sign this request and generate the client certificate:

openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So now we should have several files in our directory. These are:

  • ca-key.pem – The certificate authority (CA) key
  • ca-cert.pem – The certificate authority (CA) certificate
  • server-key.pem – The server key
  • server-req.pem – The server certificate request
  • server-cert.pem – The server certificate
  • client-key.pem – The client key
  • client-req.pem – The client certificate request
  • client-cert.pem – The client certificate

So now we have the required certificates for both the server and the client we can set up both.

Setting up the MySQL server and client to allow SSL

All you need to do to set up the MySQL server to accept SSL requests is edit the MySQL my.cnf configuration file (usually in either /etc/my.cnf or /etc/mysql/my.cnf). Add the following lines in the [mysqld] section to point MySQL to the required files (in our case in /var/ssl_certificates/):

[mysqld]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/server-cert.pem
ssl-key=/var/ssl_certificates/server-key.pem

Now restart the MySQL service to check it works by typing:

/etc/rc.d/init.d/mysqld restart

If everything is working fine it should just start up like normal.

Now to set up the client (our web server) we need to copy the 3 required client files ca-cert.pem, client-cert.pem & client-key.pem to a directory on the client (e.g. /var/ssl_certificates/ again). Now you edit the my.cnf on the client (usually in either /etc/my.cnf or /etc/mysql/my.cnf) and add the following lines in the [client] section, which you may need to add yourself:

[client]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/client-cert.pem
ssl-key=/var/ssl_certificates/client-key.pem

So now the client and server are set up to use the SSL certificates you created for all SSL connections between the two. Now we set up a user for the database who is forced to use SSL.

Setting up a user to use SSL only

It’s easiest to set up the user on the command line using the “mysql” command on the database server. So log in as your root user (or equivalent) on the database server and create a test database “test_ssl”:

create database test_ssl;

Now create a test user “test_user” with password “testpass” with the correct client hostname “192.168.0.2″ and force them to use SSL when logging in:

grant all privileges on test_ssl.* to 'test_user'@'192.168.0.2' identified by 'testpass' require ssl;
flush privileges;

Now we can create a test table “test” and insert some data so we can query it to test everything is working from PHP:

create table test (test_id int auto_increment primary key, test_data varchar(255));
insert into test set test_data='foo';

Now we go back to ADOdb and PHP to test this all works.

Connect to the database over SSL using ADOdb

Simply create a .php file on your web server with the following code:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("SELECT * from test");
print_r($arr);
?>

This should connect to our database using the test_ssl user and using SSL and print out the following on your web browser:

Array ( [0] => Array ( [0] => 1 [test_id] => 1 [1] => foo [test_data] => foo ) )

Now the great thing about this is that by using ADOdb we don’t need to worry about any kind of special connection string. The only thing that is different between an SSL connection and a standard connection is that SSL ues the “mysqli” command in the data source name rather than “mysql”, the rest of the code stays the same.

NOTE: It’s worth double checking that everything is working by executing "show status like 'ssl_cipher'" as a SQL query in the PHP above:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("show status like 'ssl_cipher'");
print_r($arr->fields);
?>

which gives:

Array ( [0] => Ssl_cipher [Variable_name] => Ssl_cipher [1] => DHE-RSA-AES256-SHA [Value] => DHE-RSA-AES256-SHA )