使用带有SSL的ODBC(5.1)的MySQL连接。

We've got a client application that connects to our online MySQL database (5.1.44-community-log) thru a ODBC connector (the server is a managed* dedicated webserver). This works very nice. However I can't get it to work using SSL. This is what I've done so far:

我们有一个客户端应用程序连接到我们的在线MySQL数据库(5.1.4 -community-log)，通过ODBC连接器(服务器是一个托管的专用webserver)。这是很好。但是，我不能让它使用SSL。这就是我迄今为止所做的:

1. MySQL server

1。MySQL服务器

I've got the server manager* set up MySQL with SSL, this is 'proven by':

我有服务器管理器*用SSL设置MySQL，这是“经过验证的”:

mysql> SHOW VARIABLES LIKE '%ssl%';

which results is this response:

其结果是:

+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /***/mysql-cert/ca-cert.pem     |
| ssl_capath    |                                 |
| ssl_cert      | /***/mysql-cert/server-cert.pem |
| ssl_cipher    |                                 |
| ssl_key       | /***/mysql-cert/server-key.pem  |
+---------------+---------------------------------+

Question: is the server configured right? I'm guessing it is...

问:服务器配置正确吗?我猜它是……

2. Certificates

2。证书

I've purchased real certificates (via my server manager). These are in the directory shown above. I've also downloaded the client-cert.pem, client-key.pem and ca-cert.pem from that directory.

我已经购买了真正的证书(通过我的服务器管理员)。这些在上面显示的目录中。我还下载了客户证书。pem,client-key。pem和ca-cert。pem的目录。

3. MySQL user with REQUIRE [SSL|X509]

3所示。有需要的MySQL用户[SSL|X509]

I've created a new user and then granted it access from any location (for testing) with SSL:

我创建了一个新用户，然后允许它通过SSL访问任何位置(用于测试):

GRANT USAGE ON *.* TO 'somevaliduser'@'%' IDENTIFIED BY PASSWORD 'somevalidpass' REQUIRE X509 

4. ODBC Client

4所示。ODBC客户端

I've (just downloaded and) installed : mysql-connector-odbc-5.1.8-winx64.msi (64-bit) as my machine is a 64-bit Windows 7 machine (so that's not what's wrong).

我已经安装了:mysql-connector-odbc-5.1.8-winx64。因为我的机器是64位的Windows 7机器(所以这不是错误的)。

And I've created a User DSN configuring it like this (no options set on tabs), which shows it connecting to the server (however not using - nor requesting to do so - SSL) successfully (using some valid user which doesn't requires SSL):

我已经创建了一个用户DSN，这样配置它(选项卡上没有设置选项)，它显示它连接到服务器(但是没有使用- SSL)成功(使用一些不需要SSL的有效用户):

So the connection is able to establish, now try using SSL.

因此，连接可以建立，现在尝试使用SSL。

This is configured like this, which is like I've read about on MySQL.com. So I'm not 100% sure the options set are right.

这是这样配置的，就像我在MySQL.com上看到的。所以我不能百分之百地确定这些选项是正确的。

As you can see it results in a error HY000. Turning on tracing (within the ODBC configuration) also shows this error.

正如您所看到的，它导致了一个错误HY000。打开跟踪(在ODBC配置中)也显示了这个错误。

Can anyone give me a hint on how to make this work? Even if you know about just a part of the solution?

谁能给我一个关于如何做这项工作的提示?即使你知道解决方案的一部分?

2 个解决方案

#1

I solved the problem. Because I tried several things at a time I don't know what did the trick:

我解决了这个问题。因为我一次尝试了几件事我不知道是什么把戏

1. I've had the server manager re-create the certificates: I bought some but I found out that those couldn't be used to SSL-encrypt the connection. So for now I'm using OpenSSL certificates. I've had them re-create the certificates with 4) Create your client .... server. They must be unique. (as mentioned here) in mind.
2. 我已经让服务器管理员重新创建了证书:我买了一些，但是我发现那些不能用来对连接进行ssl加密。现在我使用的是OpenSSL证书。我让他们重新创建证书，创建你的客户端服务器。他们必须是唯一的。(如上所述)。
3. I guess the checkbox 'Verify SSL Certificate' only applies when you buy a certificate and a thrid party service should check the validity of the certificate. Uncheck that box!
4. 我猜，只有当您购买了证书和thrid party服务时，复选框“验证SSL证书”才适用于验证证书的有效性。取消那个盒子!
5. Only fill out the fields:
   • 'SSL Key' (c:\path_to\client-key.pem)
   • “SSL密钥”(c:\ path_to \ client-key.pem)
   • 'SSL Certificate' (c:\path_to\client-cert.pem)
   • “SSL证书”(c:\ path_to \ client-cert.pem)
   • 'SSL Certificate Authority' (c:\path_to\ca-cert.pem)
   • “SSL证书权威”(c:\ path_to \ ca-cert.pem)
6. 只填写字段:“SSL密钥”(c:\path_to\client-key.pem)“SSL证书”(c:\ path_to \ client-cert.pem)“SSL证书权威”(c:\ path_to \ ca-cert.pem)

Please note:

请注意:

1. The port is still the same (for me).
2. 这个港口对我来说还是一样的。
3. The logs - as Michal Niklas proposed - didn't show any usefull information.
4. 这些日志没有显示任何有用的信息，就像Michal Niklas所建议的那样。
5. I've toggled on 'Use compression' which is said to improve performance.
6. 我一直在“使用压缩”上进行切换，这是为了提高性能。

#2

I am using Ubuntu 12.04 LTS with MySQL Ver 5.5.22-0ubuntu1 for debian-linux-gnu on x86_64 ((Ubuntu)) and OpenSSL OpenSSL 1.0.1 14 Mar 2012

我在x86_64 ((Ubuntu))和OpenSSL OpenSSL 1.0.1, 2012年3月使用了Ubuntu 12.04 LTS, MySQL Ver 5.5.22-0ubuntu1，用于debian-linux-gnu。

I created the certificates following the tutorial on http://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln (The tutorial is in German, but this is not important here).

我是在http://www.thomas- krenn.com/de/wiki/mysql_verbindungen_verbindungen_mit_ssl_verschl% c3%bcsn(本教程是德语，但这里不重要)的教程中创建证书的。

When trying to connect with

当尝试连接的时候。

mysql -u root -p --ssl-ca=/etc/mysql/ca-cert.pem --ssl-cert=/etc/mysql/client-cert.pem --ssl-key=/etc/mysql/client-key.pem  --protocol=tcp

I always got an error message SSL connection error: protocol version mismatch

我总是得到一个错误消息SSL连接错误:协议版本不匹配。

This lead me to the site http://bugs.mysql.com/bug.php?id=64870 which confirms (for me) that there is a bug.

这就把我带到了网站http://bugs.mysql.com/bug.php?id=64870确认(对我来说)有一个bug。

To make a long story short. In the end I created all certificates using my MacOS X Lion, copied the certificates to the server and client and it worked immediately!

长话短说。最后，我使用我的MacOS X Lion创建了所有证书，将证书复制到服务器和客户端，并且立即生效!

When I got the Linux side working, Windows worked immediatly, too! As mentioned above, you just have to set client-key, client-cert and ca-cert!

当我让Linux操作系统工作时，Windows也立刻工作了!如上所述，您只需要设置客户端-key、客户机-cert和ca-cert!