MySQL Bugs: #29955: cannot use ssl

Bug #29955	cannot use ssl
Submitted:	22 Jul 2007 10:44	Modified:	19 Feb 2010 22:58
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.27 and 5.1.6	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	SSL OpenSSL

Description:
I added "sslca=d:\root.crt" to the DSN, the file root.crt is really in this location, but I cannot connect using SSL. I always get the following error-message:
"Runtime error '-2147467259 (80004005)':

[MySQL][ODBC 3.51 Driver]SSL connection error"

How to repeat:
described above.

Thank you for the bug report. Could you please print here the output
of the command below using the mysql.exe client. Thanks in advance.

mysql> show variables like "%ssl%";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

mysql> show variables like "%ssl%";
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        |                                     |
| ssl_capath    |                                     |
| ssl_cert      | /usr/share/ssl/certs/servers.mysqld |
| ssl_cipher    |                                     |
| ssl_key       | /usr/share/ssl/certs/servers.mysqld |
+---------------+-------------------------------------+
7 rows in set (0.00 sec)

Btw. accessing mysqld with perl-DBI and perl-DBD-mysql with ssl works.

encrypted connection using mysql-connector-java-5.0.7-bin.jar from java to this mysql-server works too, but not using MyODBC. So there must be a bug somewhere in MyODBC.

Thank you for the report.

I can not repeat described behaviour with valid ssl-ca.

You provided output of show variables like "%ssl%":

mysql> show variables like "%ssl%";
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        |                                     |
| ssl_capath    |                                     |
| ssl_cert      | /usr/share/ssl/certs/servers.mysqld |
| ssl_cipher    |                                     |
| ssl_key       | /usr/share/ssl/certs/servers.mysqld |
+---------------+-------------------------------------+
7 rows in set (0.00 sec)

But this output shows you don't use ssl-ca. Please try to connect using mysql comand line client and your CA file and say us result.

Why do I have to set ssl-ca? - I run it this way and can connect using the command-line-client, MyJDBC and Perl-DBI-mysql ssl-encryption. I don't need to authenticate client-certificates.

I use the ssl-ca on client-side to authenticate the real server.

[me@proxy ~]# mysql --ssl-ca=/usr/share/ssl/certs/cacert.pem
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60476
Server version: 5.0.45 Linux - MySQL Standard Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.45, for linux-gnu (i586) using readline 5.2

Connection id:          60476
Current database:
Current user:           nobod@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.45 Linux - MySQL Standard Edition (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 10 days 22 hours 38 min 1 sec

Threads: 1  Questions: 389357  Slow queries: 5  Opens: 7111  Flush tables: 1  Open tables: 64  Queries per second avg: 0.412
--------------

mysql>

Thank you for the feedback.

In the description of the bug there is missprint "sslca=d:\root.crt". Is this missprint in the description or do you use this exact string in ODBC connection settings?

If your ODBC connections settings are syntactic correct, please, verify if server certificate is correct using openssl or other similar tool.

For openssl use command like `$openssl verify -CAfile /PATH/TO/root.crt /PATH/TO/server-cert.pem`

If I can connect using mysql-client, perl-DBI and JDBC with the root-certificate, do you really expect the server-certificate to be broken?
But ok: "openssl verify -CAfile /usr/share/ssl/certs/cacert.pem /usr/share/ssl/certs/servers.mysqld
/usr/share/ssl/certs/servers.mysqld: OK"

The querystring (as I told you in the initial discrtiption that i only added the sslca-line to the working DSN):
mysqlstring = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "SERVER=myserver;" _
        & "DATABASE=ausleihe;" _
        & "UID=ausleihe;" _
        & "sslca=d:\root.crt;" _
        & "PWD=geheim;" _
        & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

Btw. Won't the mysql-odbc-driver output something like "invalid certificate" or something instead of "SSL connection error" if there is a problem with the certificate?
And last but not least: I tested with MyODBC from several computers to connect to my linux-mysqld with the same result.
I tried to sniff the data on the wire and i saw the certificate from the server and a few packets later: "#08S01Bad handshake".

Is there any way to get some more debug-output?

Thank you for the feedback.

Verified as described.

Bug is repeatable only if one connects from Windows computer to other computer (mac in my case).

Could you please increase the severity of the bug?	
Ver. 3.51.20 still doesn't work..	

See also http://lists.mysql.com/myodbc/11295	
rgds, Santo Leto.

Is someone working on this bug? - When I search the web more and more ppl. are complaining and there is no real workaround (the only work around is to use an unencrypted/unverified connection which might be not acceptable).

I am using Connector/ODBC 3.51.20 on WindowsXPSP2. I am able to create new DSN in ODBC Data Source Administrator and I can even succesfully test SSL encrypted connection ('test' button in DSN configuration dialog). BUT when I try to link tables to MS Access SSL connection will fail with message "ssl connection error (#2026)". I can link tables by using unencrypted connections. 

I can connect to my server (Debian/Etch, 5.0.32) from linux and windows commandline by using SSL encrypted connections. 

DSN description from windows registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MySQL_SSL]
"Driver"="C:\\WINDOWS\\system32\\myodbc3.dll"
"DESCRIPTION"="Secure connection"
"OPTION"="2"
"SERVER"="mysql.server.net"
"SSLCERT"="C:\\Temp\\ca-cert.pem

still in 3.51.21

Is this being worked on?  It seems to be a serious issue for many people.

issue still in 3.51.22

This seems to be a windows encoding issue.
When I understand you in the right way, it only occurs, when using Microsoft Software not with the test at the DSN Test environment.

Please, could you add the trace file.

Sorry, I forgot:
Could you also test this with our MyODBC version 5.1, please.

I tested ODBC driver 5.1.0 Alpha. It seems to work just like version 3.51.21. I mean SSL connection doesn't work!

However, I think that this indeed is ODBC driver related problem. I think that connection testing from "ODBC Data Source Administrator" ignore SSL settings. I tried to test ssl connection and it succeeded even if path to ca-sert.pem was incorrect. There is not way to know if this test connection used SSL or not. I think that connection testing should give me SSL connection error message (#2026) if path to ca-cert.pem is incorrect. Comman line client gives me "ERROR 2026 (HY000): SSL connection error" if I specify incorrect path to ca-cert.

Teemu

PS: how to create trace file?

sorry for my english

3 days lost

and I find this bug report !!!!

not work ssl in 3.51.22 and not work ssl in 5.1 alpha !!!

I have a snifed connection fail 
I upload if is help you

If any people can work with ssl and odbc please write me

carmelonet20042hotmail.com

sorry

my correct email is carmelonet2004@hotmail.com

view with program ethereal, is a captured paquets of fail connection with ssl and odbc

Attachment: withsslerrorconnection (application/octet-stream, text), 1.64 KiB.

Bug is still repeatable with version 5.1

The solution is to disable the "verify server" option.

Index: driver/connect.c
===================================================================
--- driver/connect.c    (revision 1000)
+++ driver/connect.c    (working copy)
@@ -146,8 +146,14 @@
   /* set SSL parameters */
   mysql_ssl_set(mysql, ds->pszSSLKEY, ds->pszSSLCERT, ds->pszSSLCA,
                 ds->pszSSLCAPATH, ds->pszSSLCIPHER);
+  /*
+    TODO enable this in the future only if it can be disabled by
+    the user.
+  */
+  /*
   mysql_options(mysql, MYSQL_OPT_SSL_VERIFY_SERVER_CERT,
                 (const char *)&opt_ssl_verify_server_cert);
+  */

   if (!mysql_real_connect(mysql, ds->pszSERVER, ds->pszUSER, ds->pszPASSWORD,
                           ds->pszDATABASE, port, ds->pszSOCKET, flags))

SSL Verify server cert with GUI patch

Attachment: patch29955.diff (application/octet-stream, text), 14.73 KiB.

Mostly related to GUI, therefore test case not included

Modified patch that corresponds to the changes in the patch for bug 16653

Attachment: patch29955_v2.diff (application/octet-stream, text), 14.73 KiB.

Updated patch that does not write SSLVERIFY=0 option if it is not set

Attachment: patch29955_v3.diff (application/octet-stream, text), 14.73 KiB.

A note has been added to the 3.51.24 and 5.1.3 changelogs: 

In previous versions, the SSL certificate would automatically be verified when used as part of the Connector/ODBC connection. The default mode is now to ignore the verificate of certificates. To enforce verification of the SSL certificate during connection, use the SSLVERIFY DSN parameter, setting the value to 1.

This bug still exists for me. Setting the sslverify option to 0 or 1 doesn't help.

after further testing:
i build mysqld with openssl and i only can connect using ssl if i add "sslcert" as a dsn parameter, but i just want to use ssl to secure the communication and verify that the user sends his credentials to the correct server

Thanks for checking this. I have to re-verify the bug.

I have just successfully connected to MySQL server using SSL with only SSLCA parameter in DSN (using the test button in MyODBC GUI v3.51.24). Please note that you still have to pass valid --ssl-cert and --ssl-key parameters to the server. Please check whether these parameters are set.

A client can connect securely by using just the --ssl-ca option if the account has no special SSL requirements or was created using a GRANT statement that includes the REQUIRE SSL option. 

Here is the page with more details:
http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

Do you have more specific details?

As described above I can connect from mysql-command line version on the same computer, I can connect using Java MySQL Connector with only ssl-ca set and no sslkey and no sslcert parameter set, but not using Connector/ODBC without sslcert parameter set. NO GRANT depends on a certificate.

When you try to verify this bug, have you compiled your mysqld against openssl0.9.8?

I searched this bug-database and this looks like bug #29841, but i don't know against what kind of ssl connector/odbc is compiled.

I was using pre-compiled binaries linked against yaSSL.
Can you try yaSSL?

I'm sorry. There is no yaSSL package available for my distribution and i don't have any testing machine available atm :(

Ok, I'll make a build linked against OpenSSL.
Do you use the standard build of MyODBC driver? For which OS?

I use the standard build of MyODBC driver for Windows (on XP).

MyODBC fails to connect to OpenSSL-Linked MySQL server, whereas YaSSL works with just --ssl-ca parameter. I guess there are some compatibility problems...

MyODBC driver should be linked against mysql client library that contains OpenSSL code.

I checked different combinations of server and MyODBC builds:

MyODBC (YaSSL)   MySQL(YaSSL)   - working
MyODBC (YaSSL)   MySQL(OpenSSL) - not working
MyODBC (OpenSSL) MySQL(YaSSL)   - working
MyODBC (OpenSSL) MySQL(OpenSSL) - not working

Looks like the server side is more affected.

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".

issue still in 3.51.26

but i got it working when I specify on the DSN:
sslca=D:\root.crt;sslverify=1;sslkey=d:\123.pem;sslcert=d:\123.pem;
and 123.pem includes a certificate & key which is signed by the CA in root.crt, but this is no solution to the problem if I only want to verify that I connect to the right server.

Could you please try version 5.1. I was able to connect with SSL. Thanks in advance.

exactly the same problem with 5.1.5

I found unfortunate misprint in MYODBCSetupDataSourceDialog.cpp(693):
  if (!ptab2->getSSLCert().isEmpty())
	  stringConnectIn+= ";SSLERT=" + ptab2->getSSLCert();
must be:
  if (!ptab2->getSSLCert().isEmpty())
	  stringConnectIn+= ";SSLCERT=" + ptab2->getSSLCert();
of course.
Now it work :)

The patch has been pushed to the source repository. It will be included into the versions 3.51.27 and 5.1.6

An entry was added to the 3.51.27 and 5.1.6 changelogs:

It was not possible to use Connector/ODBC to connect to a server using SSL. The following error was generated: 

Runtime error '-2147467259 (80004005)':

[MySQL][ODBC 3.51 Driver]SSL connection error.

For me the bug is still there in 3.51.27. I cannot test 5.1.6 since it's not released so far.

With 3.51.27 it's even worse: SSLVerify=1 now works differently in 3.31.27 and 5.1.5. Either the servername isn't checked at all in 5.1.5 or 3.51.27 doesn't honour the subjectAltName any more.

This bug report is closed. If you're still encountering an issue, please open a new bug report with details on how to reproduce it.

This bug is there again in combination with MySQL 5.1.42 (I updated my server today from 5.1.40 to 5.1.42).

This bug report is fixed and closed.