Connecting WW to a remote mysql database over a secure (ssl) connection

Connecting WW to a remote mysql database over a secure (ssl) connection

by Michael Gage -
Number of replies: 0
Advise for connecting WeBWorK up to a remote mysql database using secure communications (ssl).

* These notes are from Henry Agnew at UCDavis who has set WeBWorK up to work with LibreTexts.

The standard dbi configuration in site.conf reads:

 # The database dsn is the path to the WeBWorK database which you have created.
 # Unless you have given the database a different name or the database resides on another
 # server you do not need to change this first value.
 # The format is dbi:mysql:[databasename] for databases on the local machine
 # For a remote database the format is dbi:mysql:[databasename]:[hostname]:[port]
 $database_dsn ="dbi:mysql:webwork";
 $database_storage_engine = 'myisam';

For ssl communication Henry used:
$database_dsn =";mysql_ssl=1;mysql_ssl_ca_file=/mnt/c/Users/Henry/Desktop/Perl/ca-certificate.crt"

"The only change that I needed to do was modify the DBI string a bit and remove the parts about client keys. Basically, you can enable ssl support in WeBWorK just by adding mysql_ssl=1;mysql_ssl_ca_file=/path/to/ca-certificate.crt. I think the issue I was having before was bad permissions on the certificate and my inclusion of the client keys which are not necessary.

Here is my working DBI in my webwork configuration: ";mysql_ssl=1;mysql_ssl_ca_file=/mnt/c/Users/Henry/Desktop/Perl/ca-certificate.crt"

If WeBWorK has a wiki, I would recommend adding this somewhere incase anyone else wants to use an SSL MySQL database.
-Henry Agnew"
I asked about the permissions on the files:

"For the file, I left it as a .crt instead of changing it to a .pem. While Linux usually doesn’t care about file endings, this may have affected how Perl parsed the certificate. The file is root owned with Chmod 744, and I think what I changed was making sure all folders above it also had global read permissions as well. It did not seem like it had an issue about permissions being too loose as CHMOD 777 works fine.

I think what also led me astray was that all examples I saw had client keys, but it turns out that excluding them is better since OpenSSL handles it better automatically.

Here is what we found out in terms of error codes:
* SSL_CTL_set_Default_verify_paths: This means that the file does not have the permissions or path correct for access (permission error).
* Unable to get Certificate: This means there is a problem with the file (like the extension).
Getting WW and the remote database to talk to each other over a secure connection seems to still be delicate operation. YMMV. Please contribute any additional experiences you have with this kind of connection in order to help the community.

[[Category:Release Notes]]