Python MySQL connector not working with SSL

Question

I recently configured SSL on my MySQL server and it appears to have broken my python application. I can connect to the the server fine via command line:

user@kiosk:~$ mysql -u <user> -p -h <remote host>
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

And SSL seems to be working on the connection:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

Connection id:          19
Current database:
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
....

Threads: 1  Questions: 17  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.007

The user is configured to require SSL, and I'm not currently using the CA/client-cert/client-key on my client to connect (as I understand it, this is optional).

My python script uses a simple connection:

db_ip_address = raw_input('Ip Address: ')
db_username = raw_input('Username: ')
db_passwd = raw_input('Password: ')

try:
    db = mysql.connector.connect(
         host=db_ip_address,
         user=db_username,
         passwd=db_passwd,
    )
    cursor = db.cursor()
    connected = True

except Exception as e:
    installer.fatal('\r\n Failed to connect to SQL server please try again \r\n')
    print traceback.format_exception_only(type(e), e)[0]

Which is now failing with the following error:

ProgrammingError: 1045 (28000): Access denied for user '<user>'@'<IP>' (using password: YES)

I'm assuming this is due to my SSL config, but I'm not entirely sure why things work fine from the mysql CLI, but not from my script?

Any insight would be greatly appreciated.

Mr.mb · Accepted Answer · 2019-03-18 13:18:53Z

6

the following error is for wrong login info such your username or your password or your ip address. if your username and password is correct check the allowed IP in your database if % or localhost or 127.0.0.1. for the ssl connection check the following code:

import sys

#sys.path.insert(0, 'python{0}/'.format(sys.version_info[0]))

import mysql.connector
from mysql.connector.constants import ClientFlag

config = {
    'user': 'ssluser',
    'password': 'password',
    'host': '127.0.0.1',
    'client_flags': [ClientFlag.SSL],
    'ssl_ca': '/opt/mysql/ssl/ca.pem',
    'ssl_cert': '/opt/mysql/ssl/client-cert.pem',
    'ssl_key': '/opt/mysql/ssl/client-key.pem',
}

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)
cur.execute("SHOW STATUS LIKE 'Ssl_cipher'")
print(cur.fetchone())
cur.close()
cnx.close()

from here

and here how to setup your mysql with ssl

answered Mar 18, 2019 at 13:18

Mr.mb

1024 bronze badges

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

1 Comment

tparrott Over a year ago

That was it -- now to figure out how to make my SSL connections preferred instead of required. Thanks!

Collectives™ on Stack Overflow

Python MySQL connector not working with SSL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related