Wednesday, 26 May 2010

Using SSL Connections with MySQL

It is very important to secure connection between MySQL Server and Clients. This can be achieved by using SSL connections between MySQL server and client program.

Assumptions:

1. MySQL that has been built with SSL support, and you are going to use OpenSSL.
2. Reader has basic Unix/MySQL Skills
3. Client program is running on windows machine (IP:192.168..0.5)
4. Server program is running on Centos/Redhat machine (IP:192.168.0.4)

Setting Up SSL Certificates for MySQL:

The following openssl commands will prompt you for several inputs. For testing, you can press Enter to all prompts.

1. Create new folder

shell> mkdir -p /etc/mysql
shell> chown -R mysql:mysql /etc/mysql/
shell> cd /etc/mysql/

2. Create CA certificate

shell> openssl genrsa 2048 > ca-key.pem

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

3. Create server certificate

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

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

4. Create client certificate

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

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

5. Add these lines into MySQL option file (i.e. /etc/my.cnf)

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

6. Restart MySQL Instance

shell> /etc/init.d/mysql restart

7. Connect to MySQL server using mysql client program and check whether SSL support is enabled

shell> mysql -uroot -pxxxx



mysql> SHOW VARIABLES LIKE 'have_ssl'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| have_ssl      | YES   | 
+---------------+-------+

8. Create a user with REQUIRE SSL option


mysql> GRANT ALL ON test.* TO 'ssl_user'@'192.168.0.5' IDENTIFIED BY 'password' REQUIRE SSL;

9. Copy following files from /etc/mysql/ onto windows client on c:\ssl\

ca-cert.pem
client-cert.pem
client-key.pem

10. Test connectivity from windows O/S. From command prompt run this command





C:\>mysql -h192.168.0.4 -ussl_user  -ppassword --ssl-ca c:\ssl\ca-cert.pem --ssl-cert



c:\ssl\client-cert.pem --ssl-key c:\ssl\client-key.pem

11. Once connected successfully, run this command to verify SSL connection


mysql> SHOW STATUS LIKE 'Ssl_cipher';
 +---------------+--------------------+ 
| Variable_name | Value              |
 +---------------+--------------------+ 
| Ssl_cipher    | DHE-RSA-AES256-SHA | 
+---------------+--------------------+


Done!
Posted by at
Labels: , ,

11 comments:

  1. Frank9 June 2010 at 05:27

    I've recently purchased SSL Certificates courtesy of a great reseller SSL247.com and have been looking at how to make the clients connection more secure using MySQL, so this is a decent walkthrough. Thanks for sharing!

    ReplyDelete
  2. Sara Wright7 October 2010 at 04:25

    This comment has been removed by the author.

    ReplyDelete
  3. Anonymous8 November 2011 at 14:43

    First of all thank you for your information. They were really helpful. Unfortunately I have a problem. I did all of your steps and everything worked fine. I can connect to my database using the command prompt, but when I try to connect with navicat or MySQL Workbench, I get an 2026 Error. What am I doing wrong?

    ReplyDelete
    Replies
    1. Aftab Khan7 November 2018 at 00:35

      You need to strip the password from the private key file. Run the following command on the key file
      openssl rsa -in client-key.pem -out client-key-new.pem
      then rename keyclient-key-new.pem to client-key.pem in the same directory as your copied it to from earlier.

      Delete
  4. SSL Cert News14 March 2012 at 04:23

    WildCard SSL certificate are designed and developed to secure unlimited subdomains, including the Fully Qualified Domain Name. Using Single Wildcard SSL Certificate, you may incorporate an ample amount of pages over all subdomains.

    EV SSL Certificate or Extended Validation SSL Certificates is an unique form of the X.509 certificate which involves extensive verification of the organization by the Certificate Authority (CA) before a certificate is issued.

    ReplyDelete
  5. Muhammed Roshan19 July 2012 at 23:21

    The 2026 error is due to the the same CN(comman Name ) which you have given while creating the client and server keys. The CN for client and server should be different.

    ReplyDelete
  6. Brende25 September 2012 at 05:41

    9. Copy following files from /etc/mysql/ onto windows client on c:\ssl\
    ca-cert.pem
    server-cert.pem
    server-key.pem

    should be
    ca-cert.pem
    client-cert.pem
    client-key.pem

    ReplyDelete
  7. Unknown15 October 2012 at 08:37

    how to make the connection between CA(EJBCA-JBOSS) and MySQL using SSL ?? please help me.

    ReplyDelete
  8. Unknown19 May 2015 at 04:59

    Hi Guys,

    followed the guide... gave different common names to the 3 certificates.

    i can connect from navicat on my mac, but if i try and connect from navicat i get 2026 SSL error,

    any ideas??

    ReplyDelete
    Replies
    1. Aftab Khan7 November 2018 at 00:37

      You need to strip the password from the private key file. Run the following command on the key file
      openssl rsa -in client-key.pem -out client-key-new.pem
      then rename keyclient-key-new.pem to client-key.pem in the same directory as your copied it to from earlier.

      Delete
  9. Unknown19 May 2015 at 05:55

    if i try and connect from naviciat on windows**

    ReplyDelete

Subscribe to: Post Comments (Atom)