MySQL Bugs: #64870: Got error ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Bug #64870	Got error ERROR 2026 (HY000): SSL connection error: protocol version mismatch
Submitted:	4 Apr 2012 15:49	Modified:	24 Mar 2014 15:40
Reporter:	Debbie Pierce	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S1 (Critical)
Version:	5.5.22	OS:	Windows (Windows 7 SP1)
Assigned to:		CPU Architecture:	Any
Tags:	SSL

Description:
I've got the mysql server 5.5 running, and SSL is enabled, the proper certificates seem to be loaded, as shown by:

mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | C:/sohdb_certs/ca-cert.pem     |
| ssl_capath    |                                |
| ssl_cert      | C:/sohdb_certs/server-cert.pem |
| ssl_cipher    |                                |
| ssl_key       | C:/sohdb_certs/server-key.pem  |
+---------------+--------------------------------+

I've created a user that requires SSL to connect, as shown by:

mysql> select User,Host from mysql.user where User='someuser';
+----------+-----------+
| User     | Host      |
+----------+-----------+
| someuser | localhost |
+----------+-----------+
mysql> show grants for 'someuser'@'localhost';
+---------------------------------------------+
| Grants for someuser@localhost               |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD 'HIDE' REQUIRE SSL |
| GRANT SELECT ON `sohdb`.* TO 'someuser'@'localhost'                     |
+---------------------------------------------+

When I try to connect to someuser from a Windows CMD shell, I'm getting the error shown below:

mysql -u someuser --password=hideme --ssl-ca=C:\sohdb_certs\ca-cert.pem --ssl-cert=C:\sohdb_certs\client-cert.pem --ssl-key=C:\sohdb_certs\client-key.pem
ERROR 2026 (HY000): SSL connection error: protocol version mismatch

I used OpenSSL (Win64) 1.0.1 14 Mar 2012 to create the certificates

How to repeat:
create a user that requires SSL
create certificates using OpenSSL (Win64) 1.0.1 14 Mar 2012 
Apply private key fix as described in http://forums.mysql.com/read.php?11,400856,401127#msg-401127 (I couldn't get SSL to be enabled in mysql server until I did this)
Try to connect someuser to mysql server using the certificates

What exact server version, 5.5.x, do you use?

From MySQL monitor, I'm using: Server version: 5.5.22 MySQL Community Server (GPL)

Thank you for the feedback.

Do you run client and server on the same machine? Do you use same version of MySQL client and server (are both 5.5.22)?

How did you create keys and certificates? Please send us server and client keys and certificates, so we can repeat the problem on our side.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug should be reopened. It looks like mysql cannot handle certificate files generated with this specific version of openssl (OpenSSL 1.0.1 14 Mar 2012). I had the exact same issue as the reporter with this exact version of openssl. Because the version number was identical, i started up my ancient RHEL4 virtual machine which has OpenSSL 0.9.7 (from 2003, no less!) and ran my certificate generation routines (see attachment, mysql-certificate-generator.sh).

Result: running the script using the old version of openssl produced certificates that worked in Mysql 5.5! I could succesfully connect, both from the commandline client and remotely, using DHE-RSA-AES256-SHA as a cipher.

See the attachment mysql-certificate-generator.sh for the precise commands used to make the certificates.

Simple ca + server certs + client certs creation script.

Attachment: mysql-certificate-generator.sh (application/octet-stream, text), 704 bytes.

My organization is experiencing this same issue on Ubuntu 12.04 LTS.

When trying to rebuild our database servers yesterday, we found that the certificates generated by OpenSSL 1.0.1 14 Mar 2012 (openssl-1.0.1-4ubuntu5) produced the described error when attempting to authenticate.

This morning, we worked around the issue by generating our certificates on our backup replication server in the back room which is running Ubuntu 10.04 LTS and OpenSSL 0.9.8. The certificates generated on the backup replication server worked successfully for authentication.

Thank you for the feedback.

I still can not repeat described behavior with script provided. Please try with current version 5.5.24 and, if problem still exists, provide your configuration file.

I'm getting this same error but I'm using openssl 0.9.8e 22.el5_8.3 on CentOS 5.8.

I followed the SSL cert creation instructions (http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html) to the letter.  The server starts just fine so the server cert at least appears to be ok.  But it returns this totally non-helpful "SSL connection error" message if I try to initiate an SSL connection to the server using the certs generated.

Specifically, this does work:

mysql --ssl-verify-server-cert --ssl -h (remote server IP) -ussltest -ppassword

However, if I try to test the CA cert:

[root@localhost test4]# mysql --ssl-verify-server-cert --ssl-ca=(cert path)/ca-cert.pem --ssl -h 10.1.1.68 -ussltest -ppassword
ERROR 2026 (HY000): SSL connection error

I can't troubleshoot this at all because none of the logs even mention this error, let alone provide any sort of details on it!  It would be EXTREMELY helpful if you guys could at least have this start catching specific SSL errors instead of just throwing a generic catch-all.

Here's the SSL info retrieved when logged-in to the server:

mysql> show global variables like '%ssl%';
+---------------+----------------------------------------------------+
| Variable_name | Value                                              |
+---------------+----------------------------------------------------+
| have_openssl  | YES                                                | 
| have_ssl      | YES                                                | 
| ssl_ca        | (cert path)/ca-cert.pem                            | 
| ssl_capath    |                                                    | 
| ssl_cert      | (cert path)/server-cert.pem                        | 
| ssl_cipher    |                                                    | 
| ssl_key       | (cert path)/server-key.pem                         | 
+---------------+----------------------------------------------------+
7 rows in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i686) using readline 5.1

Connection id:          8
Current database:
Current user:           (user)@(local IP)
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.95 Source distribution
Protocol version:       10
Connection:             (remote server IP) via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 18 hours 30 min 50 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 13  Flush tables: 1  Open tables: 7  Queries per second avg: 0.000
--------------

mysql> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   | 
+---------------+-------+
1 row in set (0.00 sec)

Additionally, I found a few forum posts that suggested adding a [Client] section to my.cnf which would contain entries for paths to the client certs.  However, this borked the server entirely, causing it to throw an "SSL connection error" even on non-SSL connection attempts to localhost (causing 'mysqld start' to timeout even though the server was started successfully).

I've spent a good 20 hours on this already and am thoroughly stumped.  According to all the docs I can find, this *should* be working.  Then I found this bug post, so I'm thinking this HAS to be related somehow.  At very least, the symptoms described are the same, though the OS and OpenSSL versions are different.

Is there any additional info I can provide that would help?  I'd really like to breathe some new life into this ticket if at all possible.

Thanks!

--Kris

Kris,

thank you for the feedback.

Please send us certificates you are having problems with.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Just a followup on this task.  I was not able to enable SSL in mysql server using keys/certificates created using openssl 1.0.1c.  The issue seemed to be that the server-key.pem file was created without "RSA" in the header and the footer.  When I manually added the "RSA" to the file, and restarted the server, mysql showed that SSL was enabled, but a client couldn't connect securely to the server.  Rolling back to keys and certificates created using openssl 0.9.8x solved all problems.

Thank you for the feedback.

I still can not repeat described behavior.

And having the fact: "The issue seemed to be that the server-key.pem file was created without "RSA" in the header and the footer. " this looks more likely like OpenSSL problem, not MySQL's, although in my case all "RSA" were in place. Closing as "Can't repeat".

Just want to add a comment about how I got this working.  

When I specified a host name during cert generation, then tried to validate the key, I got an error message about "self signed cert", but it then said "OK", but I couldn't use the cert for SSL connections.

When I skipped entering the hostname, the cert verification threw no warning or errors, and I could use the cert for SSL connections.

I managed to reproduce the error (or at least the same error message) with the following steps:

1. create two key/certs with the same subject/issuer
  1.crt, 1.pem, 2.crt, 2.pem
2. add both certificates to the cacert file
3. do a GRANT  ... REQUIRE ISSUER <issuer> AND SUBJECT <subject>
4. try to log in with the first cert in the cacert file: it works fine:
    mysql -u user  --ssl-cert=1.crt --ssl-key=1.pem --protocol=tcp -h <hostname>
5. try to log in with the second cert:
    mysql -u user  --ssl-cert=2.crt --ssl-key=2.pem --protocol=tcp -h <hostname>
    => ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Hopefully that's the same root cause than the others.

I'm getting the exact same problem on 5.5.29, having followed the instructions on the documentation exactly:

http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html

mysql> show variables like "%ssl%";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /var/lib/mysql/certs/cacert.pem      |
| ssl_capath    |                                      |
| ssl_cert      | /var/lib/mysql/certs/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_key       | /var/lib/mysql/certs/server-key.pem  |
+---------------+--------------------------------------+

Using OpenSSL 1.0.1 14 Mar 2012 on Ubuntu.

Here's the interesting part.. if I connect from a client instance specifying a client key, I get the standard error:

$ mysql -h 10.0.0.1 --ssl-ca=/var/lib/certs/ca-cert.pem --ssl-cert=/var/lib/certs/client-cert.pem --ssl-key=/var/lib/certs/client-key.pem -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error: protocol version mismatch

However, if I only specify the CA certificate and do not specify a client key or certificate, I can connect correctly with SSL:

$ mysql -h 10.0.0.1 --ssl-ca=/var/lib/certs/ca-cert.pem -u root -p
Enter password: 
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2
Connection id:		53
Current database:
Current user:		root@10.84.53.44
SSL:			Cipher in use is DHE-RSA-AES256-SHA
--

Again, I must stress that I have followed the instructions on the documentation to the letter. The resultant -key files are RSA encoded and have that in their headers.

Unfortunately only specifying a CA will not work for my tomcat web applications.

I am having this exact issue on Centos 6.4, it's been over a year since this has been reported and in all cases this bug has been closed with a 'can't repeat' status.

can you take the following steps
-install mysql-server from a binary with openssl support

Generate SSL Certificates
mkdir /etc/mysql-ssl
cd /etc/mysql-ssl
 
CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem
 
Server
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 9000  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
 
Client
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
 
 
Configure the MySQL Server to use SSL Encryption /etc/my.cfg
[mysqld]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem
 
 
Configure the MySQL Clients to use SSL Encryption
[client]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem
 
 
Create MySQL User that is Required to use SSL
GRANT SELECT, INSERT, UPDATE, DELETE on mydb.* to ‘ssluser’@’host’ IDENTIFIED BY ‘secretpass’ REQUIRE SSL;
FLUSH PRIVILEGES;
 
 
 
Login to MySQL using SSL Encryption
Using command line parameters without /etc/my.cnf [client] section
mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem –ussluser –p

This results in the Error report above
Got error ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Kernel
2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
MySQL
Server version: 5.1.73 Source distribution
OpenSSL
OpenSSL 1.0.1e-fips 11 Feb 2013

as previously reported this only works when we specify the CA cert only
mysql --ssl-ca=ca-cert.pem -ussluser -p

it does not work whatsoever using the MySQL workbench client

Please reopen as a bug

Thank you for the bug report.

If you're using 'openssl req -newkey rsa:2048 ...' to generate keys, please be advised that openssl 1.0 and newer now stores private keys in the PKCS#8 format instead of PKCS#1.  From https://www.openssl.org/news/changelog.html

     Make PKCS#8 the default write format for private keys, replacing the
     traditional format. This form is standardised, more secure and doesn't
     include an implicit MD5 dependency.
     [Steve Henson]

These keys will have a PEM header such as:
    -----BEGIN PRIVATE KEY-----

If MySQL is compiled with YaSSL as its SSL implementation (which I believe is the default), these keys won't load and MySQL will complain at startup:
  [Warning] Failed to setup SSL
  [Warning] SSL error: Unable to get private key

YaSSL expects RSA private keys in the PKCS#1 format, with the PEM header:
    -----BEGIN RSA PRIVATE KEY-----

Various "advices" online seem to suggest that you can change the PEM header and footer of those PKCS#8 private keys to get them to work with MySQL/yaSSL.  That will indeed stop MySQL from complaining at startup, but unfortunately SSL connections against MySQL will still fail with something like:
    ERROR 2026 (HY000): SSL connection error: protocol version mismatch

To fix this, convert the key to the older PKCS#1 RSAPrivateKey format using 'openssl rsa'.
$ openssl rsa -in key-from-openssl-1.pem -out pkcs1-yassl-compatible-key.pem

Using different MySQL community versions give different result.

When following 5.6 documentation to create keys and certificates, I tested on MySQL 5.6.16 and 5.6.17 which worked as described. Upgrading to MySQL version 5.6.19 gave this error when connecting:

# mysql -umyuser -p -hmyserver --ssl-ca=/var/mysql/ca-cert.pem --ssl-cert=/var/mysql/client-cert.pem --ssl-key=/var/mysql/client-key.pem
Enter password:
ERROR 2026 (HY000): SSL connection error: protocol version mismatch

seems something has changed after the latest upgrade.

Using Red Hat Enterprise Linux Server release 6.5

I have set up a SSL (with X509) connection for replication purposes with mysql 5.5.50 on two linux machines (master and slave) as a test without issue. The certs and keys were generated with OpenSSL 1.0.1 on linux. 

In the production environment the master is on windows (5.5.51 MySQL Community Server) and slave on linux (5.5.50) - the setup barfed the 'protocol version mismatch' error. After a few unsuccessful attempts at generating keys with different options I tried to use OpenSLL 1.0.2.h on Windows but that also did not work. Next was back to linux and compiling OpenSSL version 0.9.8. Using keys/certs generated with produced no error and SSL connection was established successfully.