I want to be able to connect to a database hosted on a Raspberry Pi (mysql-server) from a C# application. In the real world professionals use a Webservice for this, but I want to use the SQL-connector for C#.
I successfully pulled this off using a certificate from Comodo. See https://tutorials.pieterjan.pro/?a=Webdesign_Hosting_ExternHosten_HTTPS_SSL_SQL-TLS&lang=nl
I already installed a certificate from Let's Encrypt for pieterjan.pro. But when I configure mysql to use this certificate it doesn't work. While I actually did the same thing.
sudo nano /etc/mysql/my.cnf
contains:
ssl
ssl-cert=/etc/letsencrypt/live/pieterjan.pro/fullchain.pem
ssl-key=/etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key
The key-file has been reproduced with openSSL, So it wouldn't be password-protected. The paths to the cert and key are correct. Why can't I use SQL over TLS using a LetsEncrypt Certificate?
pi@pieterjan:~ $ mysql -p -u root
Enter password:
mysql> show variables like '%ssl%';
+---------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | /etc/letsencrypt/live/pieterjan.pro/fullchain.pem |
| ssl_cipher | |
| ssl_key | /etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key |
+---------------+------------------------------------------------------------+
EDIT:
So far I've tested some things:
1) The error-log says:
SSL error: Unable to get certificate from '/etc/letsencrypt/live/pieterjan.pro/fullchain.pem'
2) The owner of the specified file is root:root
sudo ls -l /etc/letsencrypt/live/pieterjan.pro/fullchain.pem
-> user=root & group=root
3) The mysql-daemon was started by the mysql-user
ps -aux | grep mysql
-> mysql /usr/sbin/mysqld
4) Therefore I tried to add the mysql-user to the root group:
sudo usermod -a -G root mysql
5) Double-check -> OK
sudo grep 'root' /etc/group
-> root:x:0:mysql
6) Inspection of certificate-files
sudo ls -la /etc/letsencrypt/live/pieterjan.pro/
-> lrwxrwxrwx 1 root root 37 Jan 18 13:14 cert.pem -> ../../archive/pieterjan.pro/cert1.pem
-> lrwxrwxrwx 1 root root 38 Jan 18 13:14 chain.pem -> ../../archive/pieterjan.pro/chain1.pem
-> lrwxrwxrwx 1 root root 42 Jan 18 13:14 fullchain.pem -> ../../archive/pieterjan.pro/fullchain1.pem
They appear to be just symlinks.
7) Tracked the file down which is mentioned in the error-log
sudo ls -la /etc/letsencrypt/archive/pieterjan.pro/fullchain1.pem
-> -rw-r--r-- 1 root root
The certificate is readable for owner/group/world
Despite the fact that the mysql-user is in the root-group, mysql-server is still unable to read the certificate (after restarting mysql off course).
2 Answers 2
I'm a bit late replying here but I suspect the problem is that Let's Encrypt supplies certificates in PKCS #8 format, whereas MySQL needs them in PKCS #1 format. You will need to convert the key each time it is renewed. A hook along these lines should do the trick:
openssl rsa -in /etc/letsencrypt/live/example.com/privkey.pem -out /etc/mysql/privkey.pem && systemctl restart mysql
I Believe the problem you are experiencing is that MySQL being a closed service requires a certificate authority not the fullchain.pem
AFAIK (happy to be corrected if wrong), MySQL is fine to use with an internal CA certificate because it is not publicly accessed and a single public private key-pair and CA is responsible for the entire MySQL infrastructure.
Percona has a lovely little tutorial I've run on my Pi here which uses the key-generation method from the mysql Oracle mainline site here
If it still doesn't work, check here