How to setup SSL connection between your MySQL server and your Navicat?

Abstract

Even you've installed OpenSSL with MySQL successfully, you still couldn't start to use SSL connection. It's because you have to setup SSL Certificates for your MySQL server and your Navicat.

Here is the required steps for OpenSSL+MySQL installation.
1. Download OpenSSL Version 0.9.6 (www.openssl.org)
2. Linux command : [zcat 0.96l.tar.gz | tar xvf -]
3. Linux command : [./config]
4. Linux command : [make]
5. Linux command : [make install]

6. Download MySQL Version 4.0.14 Source (mysql-4.0.14.tar.gz)
7. Linux command : [./configure --with -vio --with -openssl]
8. Linux command : [make]
9. Linux command : [make install]
10. Login MySQL Prompt and enter the following query statement to check the value of "have_openssl".

mysql> Show variables like 'have_openssl';
After the installation finished, you can check if a running mysqld server supports OpenSSL by examining if the query statement [SHOW VARIABLES LIKE 'have_openssl';] returns YES.

II. Setting Up SSL Certificates for MySQL
Even you've installed OpenSSL with MySQL successfully, you still couldn't start to use SSL connection. It's because you have to setup SSL Certificates for your MySQL server and your Navicat.

Here is an example steps for setting up SSL certificates for MySQL:

Login to your Linux server and become "root". Then use the following shell command to create the server and client side certificate

1. DIR=`pwd`/openssl
2. PRIV=$DIR/private
3. mkdir $DIR $PRIV $DIR/newcerts
4. cp /usr/share/ssl/openssl.cnf $DIR
5. replace ./demoCA $DIR -- $DIR/openssl.cnf

6. # Generation of Certificate Authority(CA)
/usr/local/ssl/bin/openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -config $DIR/openssl.cnf

Note : if you were requested to enter "PEM pass", please enter different "PEM pass" in the following steps.

7. # Create server request and key
/usr/local/ssl/bin/openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

8. # Remove the passphrase from the key (optional)
/usr/local/ssl/bin/openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem

9. # Sign server cert
/usr/local/ssl/bin/openssl ca -policy policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem

10. # Create client request and key
/usr/local/ssl/bin/openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

11. # Remove a passphrase from the key (optional)
/usr/local/ssl/bin/openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem

12. # Sign client cert
/usr/local/ssl/bin/openssl ca -policy policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

13. Create a my.cnf file that you can use to test the certificates. Store it either in /etc or MySQL data directory (typically `/usr/local/var' for a source installation)

my.cnf file example content:

[client]
ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
[mysqld]
ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/server-cert.pem
ssl-key=$DIR/server-key.pem

14. # To start MySQL daemon

/usr/local/libexec/mysqld -u mysql &

or

/usr/local/sbin/mysqld -u &

III. Setting Up client Certificates for Navicat

You can configure Navicat to connect to MySQL Server using SSL Encrypted Connections. To use SSL,

1. In Navicat Main Window, please add a new connection or modify the connection properties of an existing connection.

2. Select SSL Settings TAB
3. Check Use SSL checkbox
4. Fill in the locations to Client Key (e.g "C:\cert\client-key.pem"), Client Certificate (e.g "C:\cert\client-cert.pem") and CA Certificate (e.g "C:\cert\cacert.pem").

Note: The Client Key, Client Certificate and CA Certificate files are usually stored in your server , /usr/local/openssl folder. Please copy them from your remote server to your local computer.

5. In your MySQL server, there's a database named "mysql". Inside the database "mysql", you can find a table "user".

In the mysql.user table, you can find a file "ssl_type". If you chose the value "SPECIFIED", you would need to fill in Specified Cipher (e.g "EDH-RSA-DES-CBC3-SHA") in the field "ssl_cipher".

Note : You can create a text file to store the Specified Cipher and then you can click on the "Load" button to load it into the blob field "ssl_cipher".