MySQL - Setting Up ODBC and SSL
From All n One's bxp software Wixi
Database encryption is an important security measure and with SSL we can ensure end to end encryption.
Contents
1 Step 1: Get Open SSL onto the database server.
Open SSL is free and can be downloaded from the following link http://www.openssl.org/. Once this is installed add it to your system variables path. This can be done as follows:
- Right click my computer
- Go to properties
- Then advanced system settings
- next click environment variables
- add to the path: C:\OpenSSL-Win64\bin; or similar depending on your system.
2 Step 2: Create the certs
Create a directory on the C drive like C:\MySQLSSLCerts
Next you will need to open a command window as the admin of your machine/server. Right click on the the command window tool and select "Run as Administrator"
Run as follows and make absolutely sure the common name is different for each one.
openssl genrsa 2048 > "C:/MySQLSSLCert/ca-key.pem"
openssl req -new -x509 -nodes -days 24800 -key "C:/MySQLSSLCert/ca-key.pem" > "C:/MySQLSSLCert/ca-cert.pem"
- Country Name : IE
- State or Province Name : Leinster
- Locality Name: Dublin
- Organization Name: All n One Limited
- Organizational Unit Name : BXP
- Common name: mydomainname.ie
- Email Address: daniel@mydomainname.ie
openssl req -newkey rsa:2048 -days 24800 -nodes -keyout "C:/MySQLSSLCert/server-key.pem" > "C:/MySQLSSLCert/server-req.pem"
- Country Name : IE
- State or Province Name : Leinster
- Locality Name: Dublin
- Organization Name: All n One Limited
- Organizational Unit Name : BXP
- Common name: myotherdomainname.ie
- Email Address: daniel@mydomainname.ie
- A challenge password: password
- Optional name can be entered: All n One Limited
openssl x509 -req -in "C:/MySQLSSLCert/server-req.pem" -days 24800 -CA "C:/MySQLSSLCert/ca-cert.pem" -CAkey "C:/MySQLSSLCert/ca-key.pem" -set_serial 01 > "C:/MySQLSSLCert/server-cert.pem"
openssl req -newkey rsa:2048 -days 24800 -nodes -keyout "C:/MySQLSSLCert/client-key.pem" > "C:/MySQLSSLCert/client-req.pem"
- Country Name : IE
- State or Province Name : Leinster
- Locality Name: Dublin
- Organization Name: All n One Limited
- Organizational Unit Name : BXP
- Common name: somedomainname.ie
- Email Address: daniel@mydomainname.ie
- A challenge password: password
- Optional name can be entered: All n One Limited
openssl x509 -req -in "C:/MySQLSSLCert/client-req.pem" -days 24800 -CA "C:/MySQLSSLCert/ca-cert.pem" -CAkey "C:/MySQLSSLCert/ca-key.pem" -set_serial 01 > "C:/MySQLSSLCert/client-cert.pem"
They now need to be tested.
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
You should get two successes.
3 Step 3: Format certs for SSL
They're in the wrong format for MySQL to use them, so they need to be converted.
http://askubuntu.com/questions/194074/enabling-ssl-in-mysql
openssl rsa -in server-key.pem -out server-key.pem
openssl rsa -in client-key.pem -out client-key.pem
Test again and you should get success again.
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
4 Step 4: Configure MySQL to use the certs
Open up the Workbench and edit Options File > Networking Put a tick in ssl, ssl-ca, ssl-cert, ssl-key(for the next three include the commas when pasting in) Put in ssl-ca "C:/MySQLSSLCert/ca-cert.pem" Put in ssl-cert "C:/MySQLSSLCert/server-cert.pem" Put in ssl-key "C:/MySQLSSLCert/server-key.pem"
Down the bottom of the screen it will say "Configuration File: C:\ProgramData\MySQL\MySQL Server 5.6\my.ini". This is indeed the file you're editing but it may not be the one MySQL is using.
Open Control Panel > Administrative Tools > Services Find the MySQL service and double click it. In the "Path to executable" after the mysqld bit… it may have –defaults-file="D:\my.ini" which is actually the ini file.
Stop the service. Backup D:\my.ini Copy the ProgramData my.ini into the D:\my.ini Restart the service.
The service should start. Log into the workbench and run the following query. SHOW VARIABLES LIKE '%ssl%';
Have_openssl = Yes Have_ssl = Yes ssl-ca "C:/MySQLSSLCert/ca-cert.pem" ssl-cert "C:/MySQLSSLCert/server-cert.pem" ssl-key "C:/MySQLSSLCert/server-key.pem"
This means that SSL is now an optional connection type.
5 Step 5: Force users to use SSL
Create a Connection into MySQL for the Admin from the Workbench desktop. Don't turn on SSL. Log into this and make sure it works.
Run the command, it should come back empty SHOW STATUS LIKE 'Ssl_cipher';
Log back in as root
Username is The User on you DB, password is your db password. Adjust accordingly.
GRANT USAGE ON *.* TO 'Username'@'%' identified by 'password' REQUIRE SSL;
Once done, Adminstrator will have to use the SSL connection.
6 Step 6: Configure the connection to use SSL
If using workbench, edit the connection, on the SSL bit.
Now, test connection. You'll have to re-enter the password, but it should be fine. Go in to execute a query SHOW STATUS LIKE 'Ssl_cipher';
You should get 'Ssl_cipher', 'DHE-RSA-AES256-SHA'
If you want to test with mysql from a dos prompt
Open cmd
mysql -uUsername -pPassword --ssl-ca=C:/MySQLSSLCert/ca-cert.pem --ssl-cert=C:/MySQLSSLCert/client-cert.pem --ssl-key=C:/MySQLSSLCert/client-key.pem
That should give a warning that passwords on the command line is a bad idea, but it should work.
7 Step 7: Apply SSL to the ODBC connection string
Last step is to apply the ssl settings to the ODBC connection coming from a web server. https://www.connectionstrings.com/mysql/
https://www.connectionstrings.com/mysql-connector-odbc-5-1/
Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDataBase;
User=myUsername;Password=myPassword;sslca=c:\cacert.pem;
sslcert=c:\client-cert.pem;sslkey=c:\client-key.pem;sslverify=1;Option=3;
https://www.connectionstrings.com/mysql-connector-odbc-3-51/
Driver={MySQL ODBC 3.51 Driver};Server=myServerAddress;Database=myDataBase;
User=myUsername;Password=myPassword;sslca=c:\cacert.pem;
sslcert=c:\client-cert.pem;sslkey=c:\client-key.pem;sslverify=1;Option=3;
8 Troubleshooting
If you have trouble with the ODBC connection strings there is a number of solutions you can try. Please click the below link to start troubleshooting.
http://www.allnone.ie/wixi/index.php?title=ODBC_Troubleshooting