I installed a Letsencrypt SSL certificate on my Ubuntu 20.4 VPN server and it works. Now, I am trying to configure mysql on this server for SSL. I read many relating posts that deal with the same issue and I spent many hours to fix it, without success.
These are the steps I did:
I copied the files cert.pem, chain.pem, fullchain.pem and privkey.pem into /var/lib/mysql.
These are the same files that I used for the SSL configuration of my
domain.
The configuration in mysql was not clear. I tried different combinations in [mysql] and [mysqld]. The one I expected to be correct, did not work: ssl_ca=/var/lib/mysql/cert.pem,ssl_cert=/var/lib/mysql/chain.pem,ssl_key=/var/lib/mysql/privkey.pem
Only this configuration works without throwing an error. Note: fullchain.pem contains the certificates from cert.pem and chain.pem. In /etc/mysql/mysql.conf.d:
[mysqld]
ssl_cert=/var/lib/mysql/fullchain.pem
ssl_key=/var/lib/mysql/privkey.pem
I can connect to mysql locally from my server and when checking the ssl attributes, I get:
(I list only the variables with values )
| Variable_name | Value |
+--------------------------------------+-----------------------------+
| have_openssl | YES |
| have_ssl | YES |
| performance_schema_show_processlist | OFF |
| ssl_cert | /var/lib/mysql/fullchain.pem | |
| ssl_fips_mode | OFF |
| ssl_key | /var/lib/mysql/privkey.pem
When I run mysql > \s, I get:
mysql Ver 8.0.21 for Win64 on x86_64 (MySQL Community Server - GPL)
Connection id: 66
Current database:
Current user: someUser@someIP
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter: ;
Server version: 8.0.26-0ubuntu0.20.04.3 (Ubuntu)
Protocol version: 10
Connection: maraxai.de via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Binary data as: Hexadecimal
When I run $ openssl s_client -connect maraxai.de:3306 -servername maraxai.de, I expect to get the same result as with $ openssl s_client -connect maraxai.de:443 -servername maraxai.de, i.e. the complete certificate chain with the successful handshake but instead, I get:
139990121219392:error:1408F10B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:331:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 5 bytes and written 302 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
Some posts suggested that the line SSL handshake has read 5 bytes and written 302 bytes suggests that the SSL handshake has been started but aborted since the server returns something that is not expected.
To further check I use openssl s_client -connect maraxai.de:3306 -servername maraxai.de -starttls mysql. The first section tells me about error:num=20:unable to get local issuer certificate and error:num=21:the server certificate is not verified for the server certificate (depth:0). Further, I see only one certificate (cert.pem). The intermediate certificates of chain.pem are not listed. This is weird since I use fullchain.pem which is a concatenation of the certificates in chain.pem and cert.pem.
CONNECTED(00000003)
depth=0 CN = maraxai.de
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 CN = maraxai.de
verify error:num=21:unable to verify the first certificate
verify return:1
---
Certificate chain
0 s:CN = maraxai.de
i:C = US, O = Let's Encrypt, CN = R3
---
Server certificate
-----BEGIN CERTIFICATE-----
//MIIF...
-----END CERTIFICATE-----
subject=CN = maraxai.de
issuer=C = US, O = Let's Encrypt, CN = R3
---
No client certificate CA names sent
Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:RSA+SHA224
Shared Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: X25519, 253 bits
---
SSL handshake has read 2027 bytes and written 448 bytes
Verification error: unable to verify the first certificate
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 21 (unable to verify the first certificate)
---
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
Protocol : TLSv1.3
Cipher : TLS_AES_256_GCM_SHA384
Session-ID: B157BD91FF0A458D6A546C26CB5665C95CD88B99CE6C66A5D98783642C39EFA4
Session-ID-ctx:
Resumption PSK: CB807FC16CE11EB47FE7BDDD99C71A5AAF1AE5CDC600A127230E914AFC4AE1018A34F72F44741D2440EB4917D5DDD0D7
PSK identity: None
PSK identity hint: None
SRP username: None
TLS session ticket lifetime hint: 7200 (seconds)
TLS session ticket:
// ...0000 - 00e0
Start Time: 1645286635
Timeout : 7200 (sec)
Verify return code: 21 (unable to verify the first certificate)
Extended master secret: no
Max Early Data: 0
---
read R BLOCK
2▒▒#08S01Got timeout reading communication packetsread:errno=0
To check if issuers and subjects of the certificates are set correctly, I run:
openssl crl2pkcs7 -nocrl -certfile fullchain.pem | openssl pkcs7 -print_certs -noout
Everything looks ok here:
subject=CN = maraxai.de
issuer=C = US, O = Let's Encrypt, CN = R3
subject=C = US, O = Let's Encrypt, CN = R3
issuer=C = US, O = Internet Security Research Group, CN = ISRG Root X1
subject=C = US, O = Internet Security Research Group, CN = ISRG Root X1
issuer=O = Digital Signature Trust Co., CN = DST Root CA X3
Also, for privkey.pem, I changed the key format to PKCS#1 to get the correct header -----BEGIN RSA PRIVATE KEY----- with the command $ openssl rsa -in privkey.pem -out privkey.pem.
I have no idea how to further investigate this issue. Any help would be greatly appreciated.
