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 )

Make a SSL Certificate Request and Install the Certificate Using Apache on Linux

I used the guides at Verisign and a forum post at Tech Arena.

It’s actually quite easy, you just need to know what commands to type. First you need to set up a virtual host in Apache and put in your content etc. The commands listed below do require some input so just fill in the details correctly.

Generate the private key:

openssl genrsa -des3 -out www.sitename.com.key 1024

Generate the certificate signing request from the private key:

openssl req -new -key www.sitename.com.key -out www.sitename.com.csr

Now send this certificate signing request to your certificate authority with your details (to Verisign/GoDaddy etc). They will send you back a .crt file, which is the certificate you need to install.

If you want to start Apache automatically, without having to enter the passphrase for the private key each time, you will need to do a couple more commands to create an unencrypted key. You can do this earlier but it’s good to have both versions of the key:

mv www.sitename.com.key www.sitename.com.key.has-passphrase
openssl rsa -in www.sitename.com.key.has-passphrase -out www.sitename.com.key

Now put the .crt and .key in a folder and point to them in the apache .conf file of the virtual host e.g.

<VirtualHost ip.add.re.ss:443>
… some config like DocumentRoot , etc..
SSLEngine on
SSLCertificateFile /etc/httpd/conf/ssl.crt/www.sitename.com.crt
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/www.sitename.com.key
</VirtualHost>

Transfer SSL Certificates from Microsoft IIS to Linux Apache

Thanks to a few different guides online (mainly Pete Frietag’s) I got an SSL certificate transferred from IIS on Server 2003 to Apache on Ubuntu Server 10.04. There was an extra step I needed to do that seems obvious now.

First Export your IIS certificate into a pfx file (this is something you should do anyways for backup)

  • Run mmc.exe
  • Click the ‘Console’ menu and then click ‘Add/Remove Snap-in’.
  • Click the ‘Add’ button and then choose the ‘certificates’ snap-in and click on ‘Add’.
  • Select ‘Computer Account’ then click ‘Next’.
  • Select ‘Local Computer’ and then click ‘OK’.
  • Click ‘Close’ and then click ‘OK’.
  • Expand the menu for ‘Certificates’ and click on the ‘Personal’ folder.
  • Right click on the certificate that you want to export and select ‘All tasks’ -> ‘Export’.
  • A wizard will appear. Make sure you check the box to include the private key and continue through with this wizard until you have a .PFX file.

Next run openssl to extract the private key, and the cert file.

# Export the private key file from the pfx file

openssl pkcs12 -in filename.pfx -nocerts -out key.pem

# Export the certificate file from the pfx file

openssl pkcs12 -in filename.pfx -clcerts -nokeys -out cert.pem

# This removes the passphrase from the private key so Apache won't
# prompt you for your passphase when it starts

openssl rsa -in key.pem -out server.key

Now to make this work (the extra step) you need to go to your apache virtual host configuration file (or default if you dont have any virtual hosts) located at “/etc/apache2/sites-available/virtualhostname.conf” and make sure the following lines are present and correct:

SSLEngine on
SSLOptions +StrictRequire
SSLCertificateFile /path/to/certificate/cert.pem
SSLCertificateKeyFile /patch/to/key/server.key

Then restart apache with the standard command

sudo /etc/init.d/apache2 restart