Using SSL with SQLAlchemy

Question

I've recently changed my project to use SQLAlchemy and my project runs fine, it used an external MySQL server.

Now I'm trying to work with a different MySQL server with SSL CA, and it doesn't connect.

(It did connect using MySQL Workbench, so the certificate should be fine)

I'm using the following code:

ssl_args = {'ssl': {'ca': ca_path}}
engine = create_engine("mysql+pymysql://<user>:<pass>@<addr>/<schema>",
                        connect_args=ssl_args)

and I get the following error:

Can't connect to MySQL server on '\addr\' ([WinError 10054] An existing connection was forcibly closed by the remote host)

Any suggestions?

score 21 · Accepted Answer · answered Feb 18 '18 at 12:09

21

I changed the DBAPI to MySQL-Connector, and used the following code:

ssl_args = {'ssl_ca': ca_path}
engine = create_engine("mysql+mysqlconnector://<user>:<pass>@<addr>/<schema>",
                        connect_args=ssl_args)

And now it works.

answered Feb 18 '18 at 12:09

Shahaf Finder

604
1
4
11

2

This just saved my life. Thanks! – Halvor Holsten Strand Dec 11 '19 at 08:49
3

ssl_args should be in this format: `ssl_args = {'sslrootcert':'server-ca.pem', 'sslcert':'client-cert.pem', 'sslkey':'client-key.pem'}` – Boz Sep 07 '20 at 12:50
Can I specify `tls_version` in ssl_args? – wawawa Jun 11 '21 at 09:01
where do I get this pem file for the client? – jtobelem Nov 25 '21 at 20:10
`ssl_ca` did not work for me, but this did: https://stackoverflow.com/a/14992181/ – Grimlock Apr 26 '22 at 19:50
Same as Grimlock, formatting is wrong on this answer, see official documentation: https://docs.sqlalchemy.org/en/14/dialects/mysql.html#ssl-connections – Nealon Feb 17 '23 at 20:49

score 5 · Answer 2 · edited Jun 06 '19 at 18:01

5

If you just connect from a client machine with an ssl connection (so you don't have access to the cert and key), you could simple add ssl=true to your uri.

Edit:

For example: mysql_db = "mysql+mysqlconnector://<user>:<pass>@<addr>/<schema>?ssl=true"

edited Jun 06 '19 at 18:01

Chris S

422
1
4
13

answered Oct 16 '18 at 10:31

Ben

696
9
19

For Postgres, try `sslmode=require` if you receive `sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) invalid connection option "ssl"` – danialk Jun 15 '23 at 10:07

score 3 · Answer 3 · answered Jun 29 '21 at 14:39

3

The official doc is well documented:

engine = create_engine(
    db_url,
    connect_args={
        "ssl": {
            "ssl_ca": "ca.pem",
            "ssl_cert": "client-cert.pem",
            "ssl_key": "client-key.pem"
        }
    }
)

answered Jun 29 '21 at 14:39

Yacine Mohamed Tidadini

51
1

sqlalchemy v.1.4.45 (late 2022) apparently has a bug in docs: keys in subdictionary should not have "ssl_" prefix. See https://stackoverflow.com/a/14992181/65736 which is still True. – bialix Dec 27 '22 at 20:31

score 0 · Answer 4 · answered Dec 08 '20 at 22:08

Another solution is to use sqlalchemy.engine.url.URL to define the URL and pass it to create_engine.

sqlUrl = sqlalchemy.engine.url.URL(
    drivername="mysql+pymysql",
    username=db_user,
    password=db_pass,
    host=db_host,
    port=3306,
    database=db_name,
    query={"ssl_ca": "main_app/certs/BaltimoreCyberTrustRoot.crt.pem"},
)
create_engine(sqlUrl)

You can include SSL parameters as a dictionary in the query argument.

This approach is useful if you are using Flask to initialize the SqlAlchemy engine with a config parameter like SQLALCHEMY_DATABASE_URI rather than directly using create_engine.

Using SSL with SQLAlchemy

4 Answers4

Linked