Question 20292 in Mysql Question posted in
The official MySQL documentation can be found here.

sshtunnel is not using specified key and not using private key password – Mysql

I’m writing a script that needs to connect to a MySQL server via SSH. I have the following:

import mysql.connector
from sshtunnel import SSHTunnelForwarder


def query_mysql_server(query):
    with SSHTunnelForwarder(
        ('ssh_server_ip', 22),
        ssh_username='sshuser',
        ssh_pkey='/Users/myhomedir/.ssh/id_rsa',
        ssh_private_key_password='my_ssh_key_passphrase',
        remote_bind_address=('127.0.0.1', 3306)
    ) as server:

        conn = mysql.connector.connect(
            host='127.0.0.1',
            port=server.local_bind_port,
            user='mysqluser',
            password='mysqluserpass',
            database='mydb'
        )

        cursor = conn.cursor()
        cursor.execute(query)

        results = cursor.fetchall()
        for row in results:
            print(row)

        cursor.close()
        conn.close()

query = "SELECT * FROM users;"
query_mysql_server(query)

Running this results in the error ERROR | Password is required for key /Users/myhomedir/.ssh/id_rsa.
I’ve also tried using a different key (/Users/myhomedir/.ssh/app_key), that doesn’t have a pass phrase set at all and get exactly the same error, referring to the "default" key id_rsa, so an alternative key is not picked up for some reason.

Both keys are added to the ssh authentication agent using ssh-add. The default key (id_rsa) is an RSA key, not an OpenSSH key.

System is macOS.

Any help is appreciated!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After troubleshooting it further and trying invalid SSH credentials I realized that the SSH tunnel actually works and the code hung on mysql.connector.connect until I added use_pure=True. Seemingly this error shows up always, regardless if the connection succeeds or not. The ssh_pkey also works as intended and will use the specified key file, but will still raise the error in question quoting the "default" key id_rsa.

    Same issue is described here: SSHTunnel searching for default private key (id_rsa) instead of the ssh_pkey I specify


  2. 0

    Basic Requirements

    Did you verify, that the connection succeeds on the commandline?

    I’ve already described it here for scp, but that’s almost ssh without shell and the required prerequisites are identical as they are here.

    Essentially I miss, that you state:

    • the program runs using the same user credentials as the user that has added the key to the ssh-agent.
    • There has been (by the same user) made a first connection, so the host-key is known and verified
    • The public key of the authentication key is transfered to the user-home on the server and added to the ~/.ssh/authorized_keys

    You also should be verifying that the access rights are restricted as required:

    700   ~/.ssh/
600   all private keys
664   at least, better 640 or more restricted the other files

    Undesired Fallback to id_rsa

    As you state the basic config is done and rechecked and also the connection directly via the shell is fine, a single detail remains:

    Have these successful shell checks been done with the app_key or with the default id_rsa?

    If also the connect via the app_key succeeds, these ‘basics’ are done.
    Else if the app_key fails at the shell the key-type and key-length would be of interest.
    Also so a check of the ~/.ssh/config would be of interest. But the priority lays on the log files:

    The key-choice might be influenced by the answers of the ssh server:

    HostbasedAcceptedKeyTypes

    This config can be requested by the client:

    ssh -Q HostbasedAcceptedKeyTypes <server>

    Server Log

    To enhance the log output the log-level should be set it to or DEBUG (up to DEBUG3is possible)

    File: server:/etc/ssh/sshd_config

    LogLevel DEBUG

    Client Log

    Also the client’s log-entries for the whole authentication process would be helpfull.

    At your MAC please choose "All Messages" in your Console.app

    Auth-Error:

    Here man ssh reveals a risk for a problem:

    SSH_ASKPASS
    If ssh needs a passphrase, it will read the passphrase from the current terminal if it was run from a terminal. If ssh does not have a terminal associated with it but DISPLAY and SSH_ASKPASS are set, it will execute the program specified by SSH_ASKPASS and open an X11 window to read the passphrase. This is particularly useful when calling ssh from a .xsession or related script. (Note that on some machines it may be necessary to redirect the input from /dev/null to make this work.)

    Perhaps you put an eye on this?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search