MySQL Bugs: #21287: "SSL connection error" is not helpful! (ssl-verify-server-cert=true vs localhos)

Bug #21287	"SSL connection error" is not helpful! (ssl-verify-server-cert=true vs localhos)
Submitted:	25 Jul 2006 22:48	Modified:	26 May 2011 18:13
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.0.23	OS:	Linux (Linux)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
Hello

 $ mysql -h 192.168.42.109
 ERROR 2026 (HY000): SSL connection error

So... what?! :) This message is just not very helpful. The certs seem to be ok:

openssl verify -purpose sslserver -CAfile ca-cert.pem /etc/mysql/server-cert.pem 
/etc/mysql/server-cert.pem: OK
openssl verify  -purpose sslclient -CAfile ca-cert.pem user-cert.pem 
user-cert.pem: OK

strace, ltrace and ethereal only reveal that there is some communication between server and client so no firewall or "skip-networking" problems.

Now I ran out of ideas.

bye,

-christian-

How to repeat:
For creating I tried both tinyca and the example script in
http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html

-=(/etc/mysql)# mysqladmin extended-status |grep -i ssl
| Com_show_processlist              | 0        |
| Ssl_accept_renegotiates           | 0        |
| Ssl_accepts                       | 0        |
| Ssl_callback_cache_hits           | 0        |
| Ssl_cipher                        |          |
| Ssl_cipher_list                   |          |
| Ssl_client_connects               | 0        |
| Ssl_connect_renegotiates          | 0        |
| Ssl_ctx_verify_depth              | 0        |
| Ssl_ctx_verify_mode               | 0        |
| Ssl_default_timeout               | 0        |
| Ssl_finished_accepts              | 0        |
| Ssl_finished_connects             | 0        |
| Ssl_session_cache_hits            | 0        |
| Ssl_session_cache_misses          | 0        |
| Ssl_session_cache_mode            | Unknown  |
| Ssl_session_cache_overflows       | 0        |
| Ssl_session_cache_size            | 0        |
| Ssl_session_cache_timeouts        | 0        |
| Ssl_sessions_reused               | 0        |
| Ssl_used_session_cache_entries    | 0        |
| Ssl_verify_depth                  | 0        |
| Ssl_verify_mode                   | 0        |
| Ssl_version                       |          |
-=(root@app109)=(pts/0)=(00:46:20|26)=-
-=(/etc/mysql)# mysqladmin variables |grep -i ssl
| have_openssl                    | YES                                  |
| ssl_ca                          | /etc/mysql/ca-cert.pem               |
| ssl_capath                      |                                      |
| ssl_cert                        | /etc/mysql/server-cert.pem           |
| ssl_cipher                      |                                      |
| ssl_key                         | /etc/mysql/server-key.pem            |

Suggested fix:
Depends on the problem but maybe yassl should return more hints what could have been gone wrong. Couldn't it parse my pem files? But why did the server started up then. Or has it problems verifying the CA?

Thank you for a problem report. Have you removed a passphrase from the key? Please, read Todd's last comment at http://bugs.mysql.com/bug.php?id=19653 also.

Yes, both keys are readable without a password, as I have explicitly chosen that option in tinyCA when I exported the keys.

After some more testing I found out that at least with the current key combinations it *does* work when I connect to localhost but give this ominous error when connecting to the eth0 IP address of my host:

 -=(~)$ mysql -u ch -h localhost
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 30 to server version: 5.0.23-Debian_1-log
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql> Bye
 
 -=(~)$ mysql -u ch -h app109.intern
 ERROR 2026 (HY000): SSL connection error

the corresponding mysql.user entry should not impose any host limitations:

  mysql> SELECT * FROM user WHERE User='ch'\G
  *************************** 1. row ***************************
                 Host: %
                 User: ch
             Password: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
             ssl_type: X509
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)
 
 
bye,

-christian-

Are certificate files actually readable by mysql process?
What connection parameters were used by mysql process? 
Was CA public key passed to MySQL client too?

Does this certificate generation procedure work for you?:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -md5 -days 1000 -key ca-key.pem -subj "/C=SE/ST=Uppsala/O=MySQL AB/OU=Test/CN=CA" > ca-cert.pem
for target in client server
do
 openssl req -newkey rsa:1024 -md5 -days 1000 -nodes -keyout $target-key.pem -subj "/C=SE/ST=Uppsala/O=MySQL AB/OU=Test/CN=$target" > $target-req.pem
 openssl x509 -req -in $target-req.pem -days 1000 -md5 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > $target-cert.pem
done

Hello

I found out that the ~ch/.my.cnf option "ssl-verify-server-cert = true"
which checks the parameter "mysql -h HOSTNAME" against the CN field in the
server certificate causes the problem.
Adding "server" to my /etc/hosts' localhost line and then using "mysql -h server" allow me to connect when using your certificates.

So the option itself works, it just fails to give a sensible error message!

This is especially important as even a plain "mysql" which connects via socket
and not TCP/IP does not work anymore and thus when using this option either the
certificate must be created for "CN=localhost" or you can no longer use the
faster connection via mysql.sock which is translated to "localhost" in the ACLs.

bye,

-christian-

OF course my last sentence was invalid as admins are free to replace "ssl_type: X509" against something more sane.

But the request for a more verbose error message stays! :)

bye,

-christian-

So, we have a feature request for a more verbose error message. But by giving more information about the reason of failure to a client we'll create a new security-related problem. Connection probes will reveal some information on ssl setup of server. Do you agree with this?

Hello

It's correct that we have to be careful not to reveal too much information on what the security policy expects to have, but

- SSL security may soley based on the validity of public keys and their 
  certificates not on "security by obscurity" i.e. not knowing which field 
  is checked! (That just to keep in mind)

- In this case the *user* i.e. the possible attacker is the one who imposes
  the constraint by using the ssl-verfify-server-cert so it's ok to tell him
  that the check he wanted failed.

- In addition to his bugs' request I would even favour that the server also
  prints a log message to the error log if it is the one that makes the
  decision to reject a client for whatever reason to help the admin. Could be 
  made optional ("ssl-log-connection-errors = true" or so).
  Also IMHO it may return to the client either:
    - "SSL connection established but rejected due to authorization failure."
    - "SSL connection could not be established (not enabled on server or
       certificates unreadable/invalid)"

bye,

-christian-

OK. It looks like a valid feature request for me.

I don't think we are going to change the error message on the client side(except maybe if the --ssl-verify-server-cert fails). But it should be possible to log the error on the server to allow some debugging help.

You can start the server with the --debug option and have the error message printed to the trace file. But that will slow the whole server down(and only available in debug compiled server) so it's not such a good option.

Bumping request - I've been fighting for couple of hours now with equifax certificate. I've created exactly the same chain on my side and my certificates work, but equifax's fail.
"SSL connection error" is all I can get. Server logs don't contain any info about that login.
As someone has mentioned before - this is not security by obscurity. It's my own client cert anyway. If I try to connect somewhere with "openssl s_connect" it will tell me everything I want about the connection, but unfortunately mysql can't be checked that way.

Bug #50076 was marked as duplicate of this one. See bug #50076 for additional information regarding to SSL error messages.

I experienced this problem in porting a mysql database from RHEL4/mysql-4.1.22
to RHEL5/mysql-5.0.77.

In the former configuration the certificate and server pem were able to be read by mysql even though they were owned by root:root.  On the new platform this resulted in 2026 errors, which were fixable by changing the ownership of the pems to root:mysql.

In re: the security issue by exposing more information to the client

Instead of that how about logging the failure verbosely?  Or at least allow some settable switch to cause verbose logging of such errors?  I spent too much time on this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129271

3229 Tatiana Azundris Nurnberg	2011-01-20
      Bug#21287: "SSL connection error" is not helpful! (ssl-verify-server-cert=true vs localhos)
      
      SSL errors on client now more specific, without giving away any secrets
     @ libmysql/errmsg.c
        SSL errors now extended, more specific
     @ mysql-test/r/openssl_1.result
        SSL error messages now more specific
     @ sql-common/client.c
        SSL errors on client now more specific, without giving away any secrets

Noted in 5.5.14, 5.6.3 changelogs.

Client programs now display more information for SSL errors to aid in
diagnosis and debugging of connection problems. 

CHANGESET - http://lists.mysql.com/commits/138207