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?

Shahaf Finder · Accepted Answer · 2018-02-18 12:09:19Z

23

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, 2018 at 12:09

Shahaf Finder

6741 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Boz Over a year ago

ssl_args should be in this format:

ssl_args = {'sslrootcert':'server-ca.pem',             'sslcert':'client-cert.pem',             'sslkey':'client-key.pem'}

wawawa Over a year ago

Can I specify tls_version in ssl_args?

jtobelem Over a year ago

where do I get this pem file for the client?

Grimlock Over a year ago

ssl_ca did not work for me, but this did: stackoverflow.com/a/14992181

Nealon Over a year ago

Same as Grimlock, formatting is wrong on this answer, see official documentation: docs.sqlalchemy.org/en/14/dialects/mysql.html#ssl-connections

Chris S · Accepted Answer · 2019-06-06 18:01:16Z

6

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 6, 2019 at 18:01

Chris S

4371 gold badge4 silver badges13 bronze badges

answered Oct 16, 2018 at 10:31

Ben

7589 silver badges21 bronze badges

2 Comments

danialk Over a year ago

For Postgres, try sslmode=require if you receive sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) invalid connection option "ssl"

Student Over a year ago

For MySQL with libmysqlclient, use ssl_mode=REQUIRED

Yacine Mohamed Tidadini · Accepted Answer · 2021-06-29 14:39:19Z

4

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, 2021 at 14:39

Yacine Mohamed Tidadini

611 bronze badge

1 Comment

bialix Over a year ago

sqlalchemy v.1.4.45 (late 2022) apparently has a bug in docs: keys in subdictionary should not have "ssl_" prefix. See stackoverflow.com/a/14992181/65736 which is still True.

STEM FabLab · Accepted Answer · 2020-12-08 22:08:19Z

1

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.

answered Dec 8, 2020 at 22:08

STEM FabLab

3803 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using SSL with SQLAlchemy

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related