| Bug #59325 | OpenSSL generated key/certificate required for SSL/TLS connections | ||
|---|---|---|---|
| Submitted: | 6 Jan 2011 10:06 | Modified: | 25 Dec 2012 22:47 |
| Reporter: | Dave Kelly | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S3 (Non-critical) |
| Version: | 5.5.8 GA | OS: | Windows (WS 2008 Std SP2 x86 32-bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | certificate, ERROR 2026, hy000, key, openssl, PEM, SSL, SSL connection error, tls, WinSock Error 10054, WSAECONNRESET | ||
[9 Jan 2011 12:20]
Dave Kelly
Changed Synopsis from: "MySQL Server will not accept SSL or TLS connections"; to: "OpenSSL generated key/certificate required for SSL/TLS connections". See my last entry for Bug #59227 (dated 2011-01-09). (http://bugs.mysql.com/bug.php?id=59227) FYI: apparently the 'openssl s_client' utility does not work; at least not for testing SSL connections with MySQL server. Even after getting SSL/TLS working between MySQL server/clients, the openssl s_client utility continued to complain about various errors, which did not seem to exist. (?)
[20 Dec 2012 18:22]
Sveta Smirnova
Thank you for the report. I get different md5 when verify your certificate and private key using method described at https://www.sslshopper.com/certificate-key-matcher.html How did you create keys and certificates?
[21 Dec 2012 8:59]
Dave Kelly
Hi Sveta - wow, that was 2 years ago! Let's see if I can recall... I'm sorry for not making it clear - the certs and key (attached to this posting) were the one's that actually work. I created them using an OpenSSL CA (DAWKCo NetSecurity Open Root CA). I should point out that the web-based test you referenced (sslshopper.com) must have corrupted the cert or key data in transmission or something. I just tested the cert and key files locally using OpenSSL and the md5 hashes match, as follows: C:\Apache22\bin>openssl x509 -noout -modulus \ -in ns1.dawkco.com-cert.pem | openssl md5 (stdin)= 31bf164e1534f42bf3c5c0b67dac4103 C:\Apache22\bin>openssl rsa -noout -modulus \ -in ns1.dawkco.com-key.pem | openssl md5 (stdin)= 31bf164e1534f42bf3c5c0b67dac4103 The original certs and keys that would not work were issued by a Windows Server 2008 certificate services CA (DAWKCo NetSecurity Root CA). The cert and key were originally in a PFX file and I used OpenSSL to extract the cert and key into separate PEM files, as follows: openssl pkcs12 -in "combined-cert.pfx" \ -out "combined-cert.pem" -clcerts -nodes openssl x509 -inform PEM -outform PEM \ -in "combined-cert.pem" -out "tls-cert.pem" openssl pkey -inform PEM -outform PEM \ -in "combined-cert.pem" -out "tls-key.pem" I know they were good because the original cert was in use by the computer account for authentication in the Windows domain and the extracted cert/key PEMs were successfully used for https on an apache web server. To get the full history and all the gory details, you really need to read the other post that I referenced earlier in this post (Bug #59227): http://bugs.mysql.com/bug.php?id=59227 I believe the original cert/key that wouldn't work with MySQL were attached to that posting. You may get some good clues from the info there.
[21 Dec 2012 9:10]
Dave Kelly
Sveta, As I said, it was so long ago, I guess I'm not 100% sure now about the status of the cert/key files uploaded back then (although the hashes did match OK just now). What I do know for sure is that a valid cert/key issued by a Windows CA would not work with MySQL, while a cert/key issued by an OpenSSL CA did work with MySQL. That's all I'm really saying.
[25 Dec 2012 15:51]
Sveta Smirnova
Thank you for the feedback. Verified as described. Changing BEGIN/END TRUSTED CERTIFICATE to BEGIN/END CERTIFICATE or using command `openssl x509 -in cacert.pem -out cacert.pem` solved issue. Not sure if this should be server or documentation bug.
[25 Dec 2012 22:47]
Dave Kelly
Sveta, Well, it seems to me that if a TLS certificate and key are valid, then MySQL should just work with them without the user having to modify the cert or key. So, IMHO this should be handled as a server bug - at least for future versions. However, a documentation update could provide this work-around for current/prior versions.
[10 Oct 2014 7:30]
Daniël van Eeden
It looks similar to Bug #71271, but that is related to the key and this bug is related to the certificate. More details about 'trusted' certificates: https://www.openssl.org/docs/apps/x509.html#TRUST_SETTINGS
Description: With SSL enabled in the MySQL Server, attempting to connect to it using SSL/TLS protocol fails. For example, connecting with the mysql client program (with SSL enabled in the client) immediately returns the following error and exits: ERROR 2026 (HY000): SSL connection error MySQL Workbench 5.2 just gives a popup with the error message 'SSL connection error'. Also, see the following results from the 'openssl s_client' SSL test utility (openssl 1.0.0); specifically, Note the 'write:errno=10054', which is the WinSock error WSAECONNRESET (An existing connection was forcibly closed by the remote host): C:\>openssl s_client -connect 65.168.232.6:3306 -ssl3 -state -debug -cipher RC4-MD5:RC4-SHA:DES-CBC-SHA:DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA -CAfile C:/Apache22/conf/ca/cacert.pem Loading 'screen' into random state - done CONNECTED(00000114) SSL_connect:before/connect initialization write to 0x661578 [0x69e723] (63 bytes => 63 (0x3F)) 0000 - 16 03 00 00 3a 01 00 00-36 03 00 4d 24 ee 92 3d ....:...6..M$..= 0010 - 8e 44 3b fa 9a 2c 7d 6f-ab 7b e0 3b dc a4 aa 57 .D;..,}o.{.;...W 0020 - 63 55 f9 30 75 46 ec 74-5e 5b 6c 00 00 0e 00 04 cU.0uF.t^[l..... 0030 - 00 05 00 09 00 0a 00 15-00 16 00 ff 02 01 .............. 003f - <SPACES/NULS> SSL_connect:SSLv3 write client hello A read from 0x661578 [0x69a1d3] (5 bytes => -1 (0xFFFFFFFF)) SSL_connect:error in SSLv3 read server hello A write:errno=10054 --- no peer certificate available --- No client certificate CA names sent --- SSL handshake has read 0 bytes and written 0 bytes --- New, (NONE), Cipher is (NONE) Secure Renegotiation IS NOT supported Compression: NONE Expansion: NONE SSL-Session: Protocol : SSLv3 Cipher : 0000 Session-ID: Session-ID-ctx: Master-Key: Key-Arg : None PSK identity: None PSK identity hint: None Start Time: 1294266002 Timeout : 7200 (sec) Verify return code: 0 (ok) --- And, here's another run using just the default settings: C:\>openssl s_client -connect 65.168.232.6:3306 Loading 'screen' into random state - done CONNECTED(00000114) 1892:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:.\ssl\s23_clnt.c:683: --- no peer certificate available --- No client certificate CA names sent --- SSL handshake has read 7 bytes and written 211 bytes --- New, (NONE), Cipher is (NONE) Secure Renegotiation IS NOT supported Compression: NONE Expansion: NONE --- Using the mysql client without SSL enabled in the client, I get the following query results from the server, which shows that SSL is indeed enabled on the server side: mysql> show variables like '%ssl%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | C:/Apache22/conf/ca/cacert.pem | | ssl_capath | | | ssl_cert | C:/Apache22/conf/ca/certs/server-cert.pem | | ssl_cipher | RC4-MD5:RC4-SHA:DES-CBC-SHA:DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA | | ssl_key | C:/Apache22/conf/ca/private/server-key.pem | +---------------+--------------------------------------+ 7 rows in set (0.09 sec) The ssl_cipher list is just the non-export grade SSL3 ciphers. (Am I missing something here? Don't think so...). Using the mysql client with SSL enabled in the client, I get the following help info: C:\>mysql --help: ... ssl TRUE ssl-ca C:/Apache22/conf/ca/cacert.pem ssl-capath (No default value) ssl-cert (No default value) ssl-cipher RC4-MD5:RC4-SHA:DES-CBC-SHA:DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA ssl-key (No default value) ssl-verify-server-cert FALSE ... How to repeat: 1. Install MySQL Server 5.5.8 Community GA binaries from the MSI Install download. 2. Set the my.ini options file as follows: [mysql] # ... ssl = On ssl-ca = "C:/Apache22/conf/ca/cacert.pem" # Not using client auth # ssl-cert = "" # ssl-key = "" ssl-cipher = "RC4-MD5:RC4-SHA:DES-CBC-SHA:DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA" # ... [mysqld] # ... ssl = On ssl-ca = "C:/Apache22/conf/ca/cacert.pem" ssl-cert = "C:/Apache22/conf/ca/certs/server-cert.pem" ssl-key = "C:/Apache22/conf/ca/private/server-key.pem" ssl-cipher = "RC4-MD5:RC4-SHA:DES-CBC-SHA:DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA" # ... 3. Try to connect using mysql or MySQL Workbench (with same SSL settings). Suggested fix: No clue.