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:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: SSL OpenSSL
Triage: D2 (Serious)

[22 Jul 2007 10:44] [ name withheld ]
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.
[24 Jul 2007 13:42] Godofredo Miguel Solorzano
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)
[24 Jul 2007 14:26] [ name withheld ]
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.
[10 Aug 2007 23:10] [ name withheld ]
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.
[16 Aug 2007 11:48] Sveta Smirnova
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.
[16 Aug 2007 14:13] [ name withheld ]
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.
[16 Aug 2007 14:14] [ name withheld ]
I use the ssl-ca on client-side to authenticate the real server.
[16 Aug 2007 19:08] [ name withheld ]
[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>
[17 Aug 2007 7:29] Sveta Smirnova
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`
[17 Aug 2007 10:08] [ name withheld ]
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?
[24 Aug 2007 11:07] Sveta Smirnova
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).
[12 Sep 2007 14:30] Santo Leto
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.
[13 Sep 2007 21:41] [ name withheld ]
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).
[17 Sep 2007 12:42] Teemu Kuulasmaa
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
[8 Oct 2007 21:10] [ name withheld ]
still in 3.51.21
[7 Nov 2007 20:13] Scott Zakarian
Is this being worked on?  It seems to be a serious issue for many people.
[15 Nov 2007 6:26] [ name withheld ]
issue still in 3.51.22
[16 Nov 2007 9:34] Susanne Ebrecht
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.
[16 Nov 2007 9:35] Susanne Ebrecht
Sorry, I forgot:
Could you also test this with our MyODBC version 5.1, please.
[16 Nov 2007 10:04] Teemu Kuulasmaa
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?
[28 Nov 2007 6:14] Martin Ortiz
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
[28 Nov 2007 6:15] Martin Ortiz
sorry

my correct email is carmelonet2004@hotmail.com
[28 Nov 2007 6:18] Martin Ortiz
view with program ethereal, is a captured paquets of fail connection with ssl and odbc

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

[2 Dec 2007 14:02] Sveta Smirnova
Bug is still repeatable with version 5.1
[23 Jan 2008 21:51] Jess Balint
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))
[5 Feb 2008 20:11] Bogdan Degtyariov
SSL Verify server cert with GUI patch

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

[5 Feb 2008 20:13] Bogdan Degtyariov
Mostly related to GUI, therefore test case not included
[5 Feb 2008 22:17] Bogdan Degtyariov
Modified patch that corresponds to the changes in the patch for bug 16653

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

[6 Feb 2008 16:04] Bogdan Degtyariov
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.

[26 Feb 2008 12:50] MC Brown
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.
[21 Mar 2008 13:21] [ name withheld ]
This bug still exists for me. Setting the sslverify option to 0 or 1 doesn't help.
[21 Mar 2008 13:39] [ name withheld ]
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
[21 Mar 2008 15:22] Bogdan Degtyariov
Thanks for checking this. I have to re-verify the bug.
[21 Mar 2008 20:20] Bogdan Degtyariov
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?
[22 Mar 2008 0:48] [ name withheld ]
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.
[24 Mar 2008 17:23] Bogdan Degtyariov
I was using pre-compiled binaries linked against yaSSL.
Can you try yaSSL?
[24 Mar 2008 17:35] [ name withheld ]
I'm sorry. There is no yaSSL package available for my distribution and i don't have any testing machine available atm :(
[24 Mar 2008 18:11] Bogdan Degtyariov
Ok, I'll make a build linked against OpenSSL.
Do you use the standard build of MyODBC driver? For which OS?
[24 Mar 2008 18:39] [ name withheld ]
I use the standard build of MyODBC driver for Windows (on XP).
[26 Mar 2008 15:52] Bogdan Degtyariov
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.
[27 Mar 2008 20:37] Bogdan Degtyariov
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.
[26 Apr 2008 23:00] Bugs System
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".
[17 Jul 2008 22:07] [ name withheld ]
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.
[7 Oct 2008 12:28] Godofredo Miguel Solorzano
Could you please try version 5.1. I was able to connect with SSL. Thanks in advance.
[8 Oct 2008 13:31] [ name withheld ]
exactly the same problem with 5.1.5
[14 Oct 2008 8:21] Dmitry Bug
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 :)
[12 Nov 2008 5:00] Bogdan Degtyariov
The patch has been pushed to the source repository. It will be included into the versions 3.51.27 and 5.1.6
[12 Nov 2008 10:54] Tony Bedford
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.
[8 Mar 2009 15:47] [ name withheld ]
For me the bug is still there in 3.51.27. I cannot test 5.1.6 since it's not released so far.
[9 Mar 2009 10:42] [ name withheld ]
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.
[18 May 2009 2:45] Jess Balint
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.
[22 Jan 2010 11:14] [ name withheld ]
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).
[19 Feb 2010 22:58] [ name withheld ]
This bug report is fixed and closed.