Connecting to MySQL over SSL is not working

[Former user]

What steps will reproduce the problem? 1. Set up a MySQL 5.5.11 Server on Ubuntu 10.04.1 and configure it to support SSL 2. Try to connect from a remote OSX 10.6.7 or Ubuntu 10.04.1 machine running the client app (Pyrit in this case) which is utilizing SQLAlchemy (v0.6.7).

The specific Pyrit command I'm using that results in a SQLAlchemy stack trace is as follows:

pyrit -u "mysql://dbuser:dbpassword@dbserverip/pyritdb?ssl_key=/local/path/to/clientkey.pem&ssl_cert=/local/path/to/clientcert.pem" eval

I'm using Pyrit v0.4.0 (r308) running on Ubuntu Linux 10.04.01 LTS with a remote MySQL 5.5.12 as the backend for the Pyrit database. The issue occurs when trying to connect to the MySQL backend server remotely over SSL using the SQLAlchemy database toolkit for Python.

I can successfully connect to the remote MySQL server instance with this command (i.e. minus the SSL-specific connection string parameters):

pyrit -u "mysql://dbuser:dbpassword@dbserverip/pyritdb" eval

According to the SQLAlchemy documentation, the "?ssl_key=/local/path/to/clientkey.pem&ssl_cert=/local/path/to/clientcert.pem" part of the url should work. But, I get a big stack trace and this error:

sqlalchemy.exc.NotSupportedError: (NotSupportedError) client library does not have SSL support None None

I also tried setting up an ODBC DSN that included SSL parameters, but couldn't get SQLAlchemy by way of Pyrit to work with this command:

pyrit -u mysql://MySQL -e target-essid create_essid

I'm able to successfully connect using the DSN "MySQL" with iodbctest with this connection string:

Driver={MySQL ODBC 5.1 Driver};DSN=MySQL;Option=3;

I can also connect using the MySQL command line client with the below command, so I know the SSL connection and all other variables are working as expected:

mysql -h dbserverip -u dbuser -p --ssl-key=/local/path/to/clientkey.pem --ssl-cert=/local/path/to/clientcert.pem

There seems to be an issue connecting to MySQL over SSL when using the SQLAlchemy database toolkit for python.

I also opened a similar ticket with the Pyrit developer(s) but he recommended that I open a ticket with the SQLAlchemy developers since all he's doing is passing the connection string as entered over to the SQLAlchemy.

[Mike Bayer]

Replying to guest:

According to the SQLAlchemy documentation, the "?ssl_key=/local/path/to/clientkey.pem&ssl_cert=/local/path/to/clientcert.pem" part of the url should work.

that is correct. Here is a test script which illustrates the connect arguments it creates for MySQL-python:

from sqlalchemy import create_engine

e = create_engine('mysql://scott:tiger@localhost/test'
                  '?ssl_key=/local/path/to/clientkey.pem'
                  '&ssl_cert=/local/path/to/clientcert.pem')
c = e.connect()

print e.dialect.create_connect_args(e.url)

output:

[[]([), {'ssl': {'cert': '/local/path/to/clientcert.pem', 
         'key': '/local/path/to/clientkey.pem'}, 'host': 'localhost', 
         'user': 'scott', 'passwd': 'tiger', 'db': 'test', 'client_flag': 2}]

But, I get a big stack trace and this error:

sqlalchemy.exc.NotSupportedError: (NotSupportedError) client library does not have SSL support None None

that error is generated by your DBAPI library, MySQL-python.

Here is some background:

http://www.mikusa.com/python-mysql-docs/docs/MySQLdb.connections.html#Connection

dictionary or mapping, contains SSL connection parameters; see the MySQL documentation for more details (mysql_ssl_set()). If this is set, and the client does not support SSL, NotSupportedError will be raised.

so this is an issue with your MySQL client library, and most likely the way that MySQL-python itself was compiled (i.e. without SSL support).

Here's a test script you can use that is against MySQL-python itself. If you continue having problems, email their list:

import MySQLdb

conn = MySQLdb.connect(user='scott', passwd='tiger', 
                        db='pyritdb', 
                        ssl={
                            'key':'/local/path/to/clientkey.pem', 
                            'cert':'/local/path/to/clientcert.pem'
                        }
                    )

I can also connect using the MySQL command line client with the below command, so I know the SSL connection and all other variables are working as expected:

mysql -h dbserverip -u dbuser -p --ssl-key=/local/path/to/clientkey.pem --ssl-cert=/local/path/to/clientcert.pem

that isn't making usage of your MySQL-python DBAPI library which may be where the problem resides.

[Former user]

I traced this issue down to the version of MySQL that I was running on the client side. MySQLdb (MySQL-python) can't utilize SSL when the client version of MySQL is anything higher than 5.5. However, I was able to get it working with MySQL versions 5.1.41 and 5.1.57. However, the remote MySQL server can be 5.5.x (which is where I need all the performance improvements from 5.5.x anyway).