Unable to make TLS TCP connection to remote MySQL server with PyMySQL, other tools work

Setting up a new server that I want to communicate to a central MySQL database, using a TLS connection for security

Following steps like this I have been able to set up TLS for my MySQL server, I have made several users that are able to login from any host (%), and require SSL connections

+------------+-----------+----------+
| user       | host      | ssl_type |
+------------+-----------+----------+
| testuser   | %         | ANY      |
+------------+-----------+----------+

I can confirm this on any host by connecting using tools like HeidiSQL or the MySQL CLI tool Ex: mysql -u testuser -p -h mysql_server_IP this will initiate a TLS connection, as confirmed by s This rules out the majority of issues I have seen on this and other forums, which is caused by the host being set to localhost.

When accessing local databases, the following works fine. When connecting to non-TLS remote database servers, it also works fine.

import pymysql.cursors

connection = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db,
                             cursorclass=pymysql.cursors.DictCursor)

When attempting to access my server with require-tls I receive the following error pymysql.err.OperationalError: (1045, "Access denied for user 'testuser'@'desktop.example.com' (using password: YES)")

The other findings I have suggest that that error is caused by: Invalid username / password combinations, or, connection prohibited by that host. However I know I can make connections from this host, as demonstrated by the CLI.

Answer

When connecting to a server that only has the require_secure_transport = ON in my.cnf, PyMySQL gives a more obvious error around being unable to start a TLS connection. pymysql.err.InternalError: (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.' But if the MySQL user itself requires SSL, you get the more generic permission denied error from the question above.

On the github issue tracker there is mention of supplying the CA .pem file. If you don’t have access to these files and want to trust the self signed cert implicitly. The docs mention the -ssl flag, which allows you to pass in paths for various cert files.

However, by passing in a valid dictionary, without any of the valid keys, you can effectively blanket trust self-signed certs. Example:

connection = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=db,
                             cursorclass=pymysql.cursors.DictCursor,
                             ssl={"fake_flag_to_enable_tls":True})