Erm's I.T. Girl – Zelna Ellis

Don't fear when Zel is near…

.NET & MySQL Part 5

MySQL and OpenSSL

Software used

In this guide:

  • Generate SSL Certificates
      Create CA certificate
      Create server certificate
      Create client certificate
  • Configure MySQL Server
  • Setting User SSL Properties
  • The MySQL manual is quite clear how to create the SSL certificates, but vague how to implement it on Windows.
    See Chapter 5.5.7 Using SSL for Secure Connections.
    See Chapter 5.5.7.4. Setting Up SSL Certificates for MySQL

    Generate Certificates

    Create CA certificate

  • openssl genrsa 2048 > ca-key.pem

    D:\newcerts>openssl genrsa 2048 > ca-key.pem
    Loading ‘screen’ into random state – done
    Generating RSA private key, 2048 bit long modulus
    ……………………………………………………………………..
    ……………….+++
    ……..+++
    e is 65537 (0x10001)

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

    D:\newcerts>openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.p
    em
    Loading ‘screen’ into random state – done
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:Test
    Organizational Unit Name (eg, section) []:Test
    Common Name (eg, YOUR name) []:Test
    Email Address []:test@mail.com

  • Create server certificate

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

    D:\newcerts>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pe
    m > server-req.pem
    Loading ‘screen’ into random state – done
    Generating a 2048 bit RSA private key
    ……………………………………………………………………..
    ………………………….+++
    ……………………………..+++
    writing new private key to ‘server-key.pem’
    —–
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:TestCo
    Organizational Unit Name (eg, section) []:TestCo
    Common Name (eg, YOUR name) []:TestCo
    Email Address []:testco@mail.com

    Please enter the following ‘extra’ attributes
    to be sent with your certificate request
    A challenge password []: xxxxxxxxxxxx
    An optional company name []: xxxxxxxxxx

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

    D:\newcerts>openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAk
    ey ca-key.pem -set_serial 01 > server-cert.pem
    Loading ‘screen’ into random state – done
    Signature ok
    subject=/C=ZA/ST=Ermelo/L=Ermelo/O=TestCo/OU=TestCo/CN=TestCo/emailAddress=testc
    o@mail.com
    Getting CA Private Key

  • Create client certificate

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

    D:\newcerts>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pe
    m > client-req.pem
    Loading ‘screen’ into random state – done
    Generating a 2048 bit RSA private key
    ……………….+++
    …+++
    writing new private key to ‘client-key.pem’
    —–
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:TestClient
    Organizational Unit Name (eg, section) []:TestClient
    Common Name (eg, YOUR name) []:TestClient
    Email Address []:testclient@mail.com

    Please enter the following ‘extra’ attributes
    to be sent with your certificate request
    A challenge password []: xxxxxxxxxxxxxxxx
    An optional company name []: xxxxxxxxxxxxxx

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

    D:\newcerts>openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAk
    ey ca-key.pem -set_serial 01 > client-cert.pem
    Loading ‘screen’ into random state – done
    Signature ok
    subject=/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/em
    ailAddress=testclient@mail.com
    Getting CA Private Key

Configure MySQL Server

Open the cmd propt and execure the following commands:
net start mysql
mysql -u root -p
enter the password
SHOW VARIABLES LIKE ‘have_ssl’;

mysql> SHOW VARIABLES LIKE ‘have_ssl’;

| Variable_name | Value    |

| have_ssl      | DISABLED |

1 row in set (0.06 sec)

This means MySQL supports SSL but it is not installed.

type exit and press Enter.
mysql> exit
Bye

Stop MySQL server.
D:\>net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

Take Note:
My PC’s root drive is D:\, usually it is C:\. Change it according to your needs.
From the directory you have created the certificates (See MySQL Manual Chapter 5.5.7.4. Setting Up SSL Certificates for MySQL) copy the following files to D:\Program Files\MySQL\MySQL Server 5.1\ssl\
Note: You can use any directory you want to.

  • ca-cert.pem
  • client-cert.pem
  • client-key.pem
  • server-cert.pem
  • server-key.pem

Open the file D:\Program Files\MySQL\MySQL Server 5.1\my.ini in your favourite text editor.
under the [client] add the following:
ssl-ca="D:/Program Files/MySQL/MySQL Server 5.1/ssl/ca-cert.pem"
ssl-cert="D:/Program Files/MySQL/MySQL Server 5.1/ssl/client-cert.pem"
ssl-key="D:/Program Files/MySQL/MySQL Server 5.1/ssl/client-key.pem"
under the [mysqld] add the following:

  1. Add SSL Support

ssl-ca="D:/Program Files/MySQL/MySQL Server 5.1/ssl/ca-cert.pem"
ssl-cert="D:/Program Files/MySQL/MySQL Server 5.1/ssl/server-cert.pem"
ssl-key="D:/Program Files/MySQL/MySQL Server 5.1/ssl/server-key.pem"

Save and close the file

Start MySQL Server

D:\>net start mysql
The MySQL service is starting…
The MySQL service was started successfully.

Log into MySQL monitor

D:\>mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.32-community MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

Check if SSL is active

mysql> SHOW VARIABLES LIKE ‘have_ssl’;

| Variable_name | Value |

| have_ssl      | YES   |

1 row in set (0.02 sec)

Check determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;

| Variable_name | Value              |

| Ssl_cipher    | DHE-RSA-AES256-SHA |

Setting User SSL Properties

Refer to MySQL Manual Chapter 12.5.1.3. GRANT Syntax
The SSL Options will be sent after REQUIRE clause.
The options are

  • SSL Tells the server to allow only SSL-encrypted connections for the account.
  • X509 Means that the client should have a valid certificate, but we do not care about the exact certificate, issuer or subject.
  • CIPHER ‘cipher’ Is needed to ensure strong ciphers and keylengths will be used.
  • ISSUER ‘issuer’ Means the client must present a valid X509 certificate issued by issuer "issuer".
  • SUBJECT ‘subject’ Means the client must present a valid X509 certificate with the subject "subject" on it.

Examples:
GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SSL;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE X509;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE CIPHER ‘DHE-RSA-AES256-SHA’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE ISSUER ‘/C=ZA/ST=Ermelo/L=Ermelo/O=Test/OU=Test/CN=Test/emailAddress=test@mail.com’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SUBJECT ‘/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/emailAddress=testclient@mail.com’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SUBJECT ‘/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/emailAddress=testclient@mail.com’
AND ISSUER ‘/C=ZA/ST=Ermelo/L=Ermelo/O=Test/OU=Test/CN=Test/emailAddress=test@mail.com’
AND CIPHER ‘DHE-RSA-AES256-SHA’;

Happy Coding!

In the Next Post we will return to VB.NET and the Connection Strings.

Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server
.NET & MySQL Part 3 Install PHP on Windows XP IIS Server
.NET & MySQL Part 4 Setup MySQL Connection String for a Windows application using VB.NET.

Version 1.0

31 March 2009 - Posted by | .NET & MySQL | , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: