Securing MySQL and Connecting WSO2 Servers

Settingup MYSQL

Generating the Keys and Signing them

Execute following commands to generate necessary keys and to sign them.

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Now open my.cnf and add the following configurations. Its located at /etc/mysql/my.cnf in Ubuntu.

[mysqld]
ssl-ca=/etc/mysql/ca.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

An sample my.cnf would look like following.

Now restart mysql server.  You can use the following command to do this.

sudo service mysql restart

Now to check whether SSL certificates are properly set. Login to MySQL and execute the following query.

SHOW VARIABLES LIKE '%ssl%';

Above will give the below output.

+---------------+----------------------------+

| Variable_name | Value                      |

+---------------+----------------------------+

| have_openssl     | YES                                 |

| have_ssl             | YES                                  |

| ssl_ca                 | /etc/mysql/ca.pem         |

| ssl_capath         |                            |

| ssl_cert             | /etc/mysql/server-cert.pem |

| ssl_cipher         |                            |

| ssl_crl               |                                |

| ssl_crlpath        |                            |

| ssl_key              | /etc/mysql/server-key.pem  |

+---------------+----------------------------+

Now MYSQL configurations are done. Now lets configure WSO2 products to connect to MYSQL via SSL.

Connecting WSO2 Products to secured MySQL Server

1. First, we need to import client and server certificates to the client-truststore of WSO2 server. You can do this with following commands. (The certificates we created when configuring MySQL)

keytool -import -alias wso2qamysqlclient -file  /etc/mysql-ssl/server-cert.pem -keystore repository/resources/security/client-truststore.jks

keytool -import -alias wso2qamysqlserver -file  /etc/mysql-ssl/client-cert.pem -keystore repository/resources/security/client-truststore.jks

2. Now specify the SSL parameters in the connection URL. Make sure you specify both options useSSL and requireSSL.

jdbc:mysql://192.168.48.98:3306/ds21_carbon?autoReconnect=true&useSSL=true&requireSSL=true

The Full datasource will look like following.

<configuration>
    <url>jdbc:mysql://192.168.48.98:3306/ds21_carbon?autoReconnect=true&amp;useSSL=true&amp;requireSSL=true</url>
    <username>root</username>
    <defaultAutoCommit>false</defaultAutoCommit>
    <password>root</password>
    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
    <maxActive>80</maxActive>
    <maxWait>60000</maxWait>
    <minIdle>5</minIdle>
    <testOnBorrow>true</testOnBorrow>
    <validationQuery>SELECT 1</validationQuery>
    <validationInterval>30000</validationInterval>
</configuration>

3. Now you can start the server. If everything is set properly, the server should start without errors.