Connecting WSO2 Carbon Server to a MySQL DB over a Secured Channel

In my previous blog post I explain how to connect WSO2 Carbon Server to a MySQL database. Please make sure it works fine before going through this.

In this blog post I will only explain changes required to enable SSL.

SSL in MySQL Connector/J encrypts all data (other than the initial handshake) between the JDBC driver and the server. The performance penalty for enabling SSL is an increase in query processing time between 35% and 50%, depending on the size of the query, and the amount of data it returns.

1. Verify whether your MySQL distribution supports SSL. Use following command and make sure it doesn't result with an Error.
$ cd /usr/local/mysql

$ sudo ./bin/mysqld_safe --ssl
101226 13:50:26 mysqld_safe Logging to '/usr/local/mysql-5.1.51-osx10.6-x86_64/data/prabath-siriwardenas-MacBook-Pro.local.err'.
101226 13:50:26 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.51-osx10.6-x86_64/data
2. Then login to the mysql and run the following query. 
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.00 sec)
3. If all works fine - then your MySQL distribution is enabled for SSL.

4. Now we need to set certificate to the MySQL Server. Here I am generating certificates with OpenSSL and copy them to /usr/local/mysql/ssl [If you don't see a directory 'ssl' - just create one there.]
$ pwd
/usr/local/mysql/ssl

$ openssl  req -x509 -newkey rsa:1024 -keyout server-key.key -out server-cert.pem

$ cp server-cert.pem ca-cert.pem

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

5. Stop the running mysql server and start it as below.
$ cd /usr/local/mysql

$ sudo  ./bin/mysqld_safe  --ssl-ca=/usr/local/mysql/ssl/ca-cert.pem  --ssl-cert=/usr/local/mysql/ssl/server-cert.pem  --ssl-key=/usr/local/mysql/ssl/server-key.pem
 
101226 18:16:42 mysqld_safe Logging to '/usr/local/mysql-5.1.51-osx10.6-x86_64/data/prabath-siriwardenas-MacBook-Pro.local.err'.
101226 18:16:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.51-osx10.6-x86_64/data
6. Once again login to the mysql and run the following query. 
mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /usr/local/mysql/ssl/ca-cert.pem     |
| ssl_capath    |                                      |
| ssl_cert      | /usr/local/mysql/ssl/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_key       | /usr/local/mysql/ssl/server-key.pem  |
+---------------+--------------------------------------+
7 rows in set (0.01 sec)
7. That's it we all need from the MySQL Server end.

8. Now you need to import the public certificate and the CA certificate of MySQL server to [CARBON_HOME]\resources\security\client-truststore.jks. But in this case since we use verifyServerCertificate=false in the JDBC connection url of the WSO2 Carbon Server - we can skip this step.

9. Now in both the user-mgt.xml and registry.xml we need to edit the ConnectionUrl and add useSSL=true, verifyServerCertificate=false and requireSSL=true.

jdbc:mysql://localhost:3306/regdb?verifyServerCertificate=false&useSSL=true&requireSSL=true

Labels: ,