2

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.

0

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.

  • 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! – samueljames3 Aug 17 '18 at 23:11
  • @samueljames3 That's good, glad to hear it answered your question! Now the only thing missing is to mark my answer as the correct one! The warm and fuzzy feeling from selfless acts is nice, but the points really do help me, too :) – dbdemon Aug 18 '18 at 17:27
  • I marked it as correct. But I my reputation on SE doesn’t rank high enough for it to be recorded yet. – samueljames3 Aug 18 '18 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.