Access denied error in trying to connect to a MySQL db in Python

Question

I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct).

Here is what I’ve written so far:

import socket
from ssh2.session import Session
import mysql.connector
host = 'servername.logserverlog.net'
user = 'username'
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, 22))
session = Session()
session.handshake(sock)
session.userauth_publickey_fromfile(user, r'C:\Users\user\Docs\ssh-key')
cnx = mysql.connector.connect(user='username', password='$gHj1aFaVFRfhl*C', database='analyst_db')

The error I am getting reads:

File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site- packages\mysql\connector\connection.py", line 176, in _auth_switch_request raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user ‘username’@‘localhost’ (using password: YES)

1. I have already confirmed my user and password are valid.
2. I tried passing the password string as a raw string, to see if somehow the Python string syntax wasn’t being received by the MySQL db correctly, and still received the same error.
3. User privileges for the user on the database have been confirmed as GRANT ALL.
4. adding host='localhost' or host='127.0.0.1' to the parameters for the "cnx" object produces the exact same error.

So, I’m not sure where to go from here.

Answer 1

You seem to be trying to make an SSH tunnel, but I don't think you're actually using that tunnel for the mysql connection?

I also suspect the mysql error you receive is from a local mysql server, not the remote one you're trying to connect to.

I think you need to do port forwarding and use that port for the mysql connection. See e.g. Enable Python to Connect to MySQL via SSH Tunnelling (stackoverflow.com) or research making SSH tunnels in Python for MySQL connections.

• samueljames3: Yep. That was it! I ended up finding out about SSHTunnelForwarder from the sshtunnel library. Just because I made a connection to the server didn't mean my script knew where to look and listen. So once port forwarding was set up via the SSH Tunnel, the MySQL connection worked. Thanks for the help!