Python - MySQL SSL Connections

Question

I have a MySQL Server set up to use SSL and I also have the CA Certificate.

When I connect to the server using MySQL Workbench, I do not need the certificate. I can also connect to the server using Python and MySQLdb on a Mac without the CA-certificate.

But when I try to connect using the exact same setup of Python and MySQLdb on a windows machine, I get access denied. It appears that I need the CA. And when I enter the CA, I get the following error

_mysql_exceptions.OperationalError: (2026, 'SSL connection error')

My code to open the connection is below:

db = MySQLdb.connect(host="host.name",    
                 port=3306,
                 user="user",         
                 passwd="secret_password",  
                 db="database", 
                 ssl={'ca': '/path/to/ca/cert'})

Could anyone point out what the problem is on a windows?

Ryan Spaulding · Accepted Answer · 2019-01-17 18:33:24Z

6

I just got the following to work with Python 2.7 and MySQLdb (1.2.4):

database = MySQLdb.connect(host='hostname', user='username', db='db_name',
    passwd='PASSWORD', ssl={'ca': '/path/to/ca-file'})

This is what you had so there must be something else going on here. I wonder if you have something either incorrect with the your local CA file or possibly the cert on the server? Can you get a copy of the CA file from the server?

answered Jan 17, 2019 at 18:33

Ryan Spaulding

1311 silver badge5 bronze badges

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

Comments

Rocky Mohan · Accepted Answer · 2020-03-11 07:13:43Z

1

import pymysql

conn = pymysql.connect(host= # your host, usually localhost,
user = # your username,
passwd = # your password,
db = #your database name ,
ssl ={'ssl': r'path of your pem file'})

answered Mar 11, 2020 at 7:13

Rocky Mohan

711 silver badge6 bronze badges

1 Comment

Itamar Mushkin Over a year ago

Please add some description alongside with your code.

anonymous · Accepted Answer · 2023-02-20 07:04:32Z

1

Try this

import ssl
from databases import Database
sslctx = ssl.create_default_context(ssl.Purpose.SERVER_AUTH, 
    cafile='ca.pem')
sslctx.verify_mode = ssl.CERT_REQUIRED
sslctx.check_hostname = True
sslctx.load_cert_chain(certfile='client.crt', keyfile='pkey.key')

database = Database(DATABASE_URL, ssl=sslctx)

databases library provides support for PostgreSQL, MySQL, and SQLite. Also useful for async frameworks.

edited Feb 20, 2023 at 7:04

answered Feb 15, 2023 at 8:20

anonymous

1612 silver badges14 bronze badges

Comments

Kid Yume · Accepted Answer · 2024-03-07 08:32:13Z

1

I know this is old but according to the documentation MYSQL on object and its properties. What i tested on Python 3.8 this is the format I used

        Cnxn=  mysql.connector.connect(
        host="ip_address",
        user="usern",
        password='pass',
        database="database",
        port="port",
        ssl_ca=Path("/path/to/cert/ca.crt")
        
    )

As Side note I had to run this openssl on PEM file first as well and convert to a .crt file

openssl x509 -in ./Downloads/ca.pem -out ./Downloads/ca.crt -outform PEM

answered Mar 7, 2024 at 8:32

Kid Yume

15713 bronze badges

Comments

mjkaufer · Accepted Answer · 2017-07-14 04:33:16Z

0

Im using pymysql and I had some problems to connect using SSL keys and certs: for the "ssl" attribute I set up as a dictionary inside. Try as below:

db = MySQLdb.connect(host="host.name",
    port=3306,
    user="user",
    passwd="secret_password",
    db="database",
    ssl={'ssl':
            {'ca': '/path/to/ca/ca',
             'key': '/path/to/ca/key',
             'cert': '/path/to/ca/cert'
            }
         }
)

edited Jul 14, 2017 at 4:33

mjkaufer

4,2565 gold badges30 silver badges57 bronze badges

answered Jul 13, 2017 at 22:59

GustavoPereira

91 bronze badge

2 Comments

Andy Over a year ago

Are you sure this isn't moving the options out of the way so that it stops using SSL?

Sahbi Over a year ago

@Andy Indeed, although it "solves" the error it disabled TLS entirely.

CClarke · Accepted Answer · 2018-09-25 15:03:00Z

0

I know this is a bit old but I found a way to get this to work. Use pymysql instead of MySQLdb and write the connection as:

import pymysql

conn = pymysql.connect(user = 'user', password = 'passwd'
, database = 'db', host = 'hst', ssl = {'ssl' : {'ca': 'pathtosll/something.pem'}})

The point people miss (including myself) is that ssl needs to be a dictionary containing a key 'ssl' which has another dictionary as a value with a key 'ca'. This should work for you.

answered Sep 25, 2018 at 15:03

CClarke

6261 gold badge11 silver badges26 bronze badges

1 Comment

Andrey Semakin Over a year ago

I don't know what's the reason for this nested ssl dictionary, and even can't find in pymysql code why this should work. I just use one (not nested) dictionary as follows: pymysql.connect(..., ssl={'ca': 'ca.pem'}) and it works.

Collectives™ on Stack Overflow

Python - MySQL SSL Connections

6 Answers 6

Comments

1 Comment

Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related