How do I configure PyMySQL connect for SSL?

Question

I'm trying to connect my database using SSL with PyMySQL, but I can't find good documentation on what the syntax is.

These credentials work in Workbench and with the CLI, but I get this error when using PyMySQL.

Can't connect to MySQL server on 'server.domain.com' ([WinError 10061] No connection could be made because the target machine actively refused it)")

db_conn = pymysql.connect(
    host=db_creds['host'],
    user=db_creds['user'],
    passwd=db_creds['passwd'],
    db=db_creds['db'],
    charset=db_creds['charset'],
    ssl={'ssl':{'ca': 'C:/SSL_CERTS/ca-cert.pem',
                'key' : 'C:/SSL_CERTS/client-key.pem',
                'cert' : 'C:/SSL_CERTS/client-cert.pem'
                }
        }
)

If I shut SSL off and drop the SSL parameter, I can connect unsecured just fine. What am I doing wrong with the SSL parameter?

Edit: PyMySQL now wants ssl parameters listed like this instead of in a dict.

db_conn = pymysql.connect(
     host=db_creds['host'],
     user=db_creds['user'],
     passwd=db_creds['passwd'],
     db=db_creds['db'],
     charset=db_creds['charset'],
     ssl_ca='C:/SSL_CERTS/ca-cert.pem',
     ssl_key='C:/SSL_CERTS/client-key.pem',
     ssl_cert='C:/SSL_CERTS/client-cert.pem'
                          )

Pigtail

I think the host might be incorrect as it looks like it's trying to connect to server.domain.com. Is that the actual hostname of your database? – Tovatovar

No, that's dummy data and not my actual server name. The error is correctly responding with my server name. – Pigtail

It could be a firewall issue or an incorrect port, although you mention that it works when you drop the SSL parameters. Take a look at https://mcmap.net/q/233854/-no-connection-could-be-made-because-the-target-machine-actively-refused-it-127-0-0-1-3446. I would also search for issues with the error you mentioned (specifically "No connection could be made because the target machine actively refused it"). I don't believe that there is anything wrong with the code you have. – Tovatovar

Answer 1

Thanks for the help everyone. The syntax listed in the question is right, but the server I was attempting a connection to was using a non-standard port. I needed to add

port = db_creds['port']

Thanks, MannyKary, for the clue.

Pigtail

I really think these error messages for database connections could be more helpful lol. In this case for example, it could say - check your port number, etc. Saves hours of frustration. – Ettie

Answer 2

I had the same problem connecting pyMysql using client-side cert and key for users that REQUIRE X509, the TiDB (mySQL 5.7 compatible) server complained that no cert was supplied!!!

[2021/05/18 16:31:23.881 +00:00] [INFO] [privileges.go:258] ["ssl check failure, require x509 but no verified cert"] [user=mindline_root] [host=%]

Looking through the sourcecode of PyMysql 1.0.2, it appears that the ssl parameter is now a boolean instead of a ssl_dict, so you should put all your ssl parameters into individual arguements, e.g.,

db_conn = pymysql.connect(
    host=db_creds['host'],
    user=db_creds['user'],
    passwd=db_creds['passwd'],
    db=db_creds['db'],
    charset=db_creds['charset'],
    ssl_ca='C:/SSL_CERTS/ca-cert.pem',
    ssl_key='C:/SSL_CERTS/client-key.pem',
    ssl_cert='C:/SSL_CERTS/client-cert.pem'
)

Seminary