Airflow SFTPHook transport.py Authentication (password) failed using Private Key

[ryanczarny]

Apache Airflow version

Other Airflow 2 version (please specify below)

What happened

I am running Airflow v2.3.2 / Python 3.10 from the Docker Image below.

apache/airflow:2.3.2-python3.10

The Docker Image has set paramiko==2.7.2 in order to address the authentication issues that had been seen in testing.

When calling the sftp, I am using the following:

sftp = SFTPHook("connection|sftp")
sftp.look_for_keys = False
sftp.get_conn()

I have also tried it without the sftp.look_for_keys line.

In the Connections within the Airflow UI, I have configured the Extra section as follows:

{
    "private_key": "privatekeyinfo", 
    "no_host_key_check": true
}

When I test the connection within the UI, it reports Connection successfully tested. However, when the script that calls the Hook runs, I receive the following:

[TIMESTAMP] {transport.py:1819} INFO - Connected (version 2.0, client dropbear)
[TIMESTAMP] {transport.py:1819} INFO - Authentication (password) failed.

I have also attempted to pass the "host_key" in the Extras field but get the same Authentication error.

To be explicit, I have tried the following -

• sftp.look_for_keys = False and "no_host_key_check": true
  

• sftp.look_for_keys = False and "host_key": "host_key_value"
  

• #sftp.look_for_keys = False and "no_host_key_check": true
  

• #sftp.look_for_keys = False and "host_key": "host_key_value"
  

• Connections in the Airflow is successful for "no_host_key_check": true in Extras
  

• Connections in the Airflow is successful for "host_key": "host_key_value" in Extras
  

Referenced SO questions -
Airflow SFTPHook - No hostkey for host found
Paramiko AuthenticationException issue
Verify host key with pysftp
"Failed to load HostKeys" warning while connecting to SFTP server with pysftp
How to use Airflow to SSH into a server with RSA public/private keys?
"No hostkey for host ***** found" when connecting to SFTP server with pysftp using private key

Additional Logging from Paramiko -

[TIMESTAMP] {transport.py:1819} DEBUG - starting thread (client mode): 0x9e33d000
[TIMESTAMP] {transport.py:1819} DEBUG - Local version/idstring: SSH-2.0-paramiko_2.7.2
[TIMESTAMP] {transport.py:1819} DEBUG - Remote version/idstring: SSH-2.0-dropbear [SERVER]
[TIMESTAMP] {transport.py:1819} INFO - Connected (version 2.0, client dropbear)
[TIMESTAMP] {transport.py:1819} DEBUG - kex algos:['diffie-hellman-group1-sha1', 'diffie-hellman-group14-sha256', 'diffie-hellman-group14-sha1'] server key:['ssh-dss', 'ssh-rsa'] client encrypt:['blowfish-cbc', 'aes128-ctr', 'aes128-cbc', '3des-cbc'] server encrypt:['blowfish-cbc', 'aes128-ctr', 'aes128-cbc', '3des-cbc'] client mac:['hmac-sha1', 'hmac-md5-96', 'hmac-sha1-96', 'hmac-md5'] server mac:['hmac-sha1', 'hmac-md5-96', 'hmac-sha1-96', 'hmac-md5'] client compress:['none'] server compress:['none'] client lang:[''] server lang:[''] kex follows?False
[TIMESTAMP] {transport.py:1819} DEBUG - Kex agreed: diffie-hellman-group14-sha256
[TIMESTAMP] {transport.py:1819} DEBUG - HostKey agreed: ssh-rsa
[TIMESTAMP] {transport.py:1819} DEBUG - Cipher agreed: aes128-ctr
[TIMESTAMP] {transport.py:1819} DEBUG - MAC agreed: hmac-sha1
[TIMESTAMP] {transport.py:1819} DEBUG - Compression agreed: none
[TIMESTAMP] {transport.py:1819} DEBUG - kex engine KexGroup14SHA256 specified hash_algo <built-in function openssl_sha256>
[TIMESTAMP] {transport.py:1819} DEBUG - Switch to new keys ...
[TIMESTAMP] {transport.py:1819} DEBUG - Attempting password auth...
[TIMESTAMP] {transport.py:1819} DEBUG - userauth is OK
[TIMESTAMP] {transport.py:1819} INFO - Authentication (password) failed.

What you think should happen instead

Authentication should verify and allow for the SFTPHook to perform as expected.

How to reproduce

Needs to be on a deployed version of Airflow or Docker Container not containing the ~/.ssh/known_hosts

1. Get an SFTP connection that requires a private key
2. Add the connection to the Connections in Airflow
3. Add the private key in the Extras field
4. Test both setting "no_host_key_check": true and "host_key"="public_key"
5. Test the connection and receive a Connected Result (green banner across the top)
6. Create a Python DAG
7. Import from airflow.providers.sftp.hooks.sftp import SFTPHook
8. access the connection in the script sftp = SFTPHook("conn_name|sftp")
9. Establish the connection sftp.get_conn()
10. Try the following with and without sftp.look_for_keys = False
sftp.store_file('./'+filename,filename,confirm=True)

Operating System

apache/airflow:2.3.2-python3.10

Versions of Apache Airflow Providers

apache-airflow-providers-sftp==4.2.4
apache-airflow-providers-ssh==3.6.0

Deployment

Other 3rd-party Helm chart

Deployment details

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[pankajkoti]

Was the public key of the key pair uploaded on the SFTP server?

[potiuk]

You have not explained that in your report so let me ask - is you DAG failing? Or is only abou the message you see?

Do you ACTUALLY see an error message or things not working? FWIW any ssh authentication tries out different authentication mechanisms and you can even sometimes decide usually which one should be used - so is your conection and DAG not working, or you are worrying about INFO level from Paramiko when the Password method fails?

Do you see failure message and was there an attempt to authenticate with teh private key of yours made? What are the logs for that?

If there is no error and the problem is the message and you would like the info message to not appear there, then you should direct your question to Paramiko not to Airflow.

If there is an error - could you please show the error messages that you see and the authentication attempt made with the private key?

[potiuk]

BTW. Can you also try more recent provider? 2.3.2 is pretty old - check your provider version and see in the released notes for SSH and https://airflow.apache.org/docs/apache-airflow-providers-ssh/stable/index.html and SFTP provider if there are no similar problems, upgrade to the latest versions you can and see if that helps (and report it back here)

[ryanczarny]

Was the public key of the key pair uploaded on the SFTP server?

Do you mean aside from adding the "host_key" value in the Extras field? No. I was under the impression from the Documentation that passing the public key in the "host_key" value served for verification.

[pankajkoti]

Was the public key of the key pair uploaded on the SFTP server?

Do you mean aside from adding the "host_key" value in the Extras field? No. I was under the impression from the Documentation that passing the public key in the "host_key" value served for verification.

host_key is different form the private-public key pair that you generate. host_key is the one for the SFTP server that you want to connect to. Specifying host_key ensures that there are no Man In The Middle(MITM) Attacks. However, since you're specifying no_host_key_check=True, providing host_key is not required although it is susceptible to MITM attacks.

Please find some theory below for public-private key pair authentication as I believe you're missing to add the public key on the SFTP server.
SSH authentication using public-key cryptography involves a pair of cryptographic keys: a private key and a public key. Here's how it works:
Key Generation: The user generates a key pair consisting of a private key and a corresponding public key. The private key is kept securely on the user's local machine, while the public key can be shared freely.
Public Key Distribution: The user provides their public key to the SSH server administrator or places it in the authorized_keys file on the server. The server stores the user's public key for authentication purposes.
Authentication Process:

1. Client Initiation: The client (user) initiates an SSH connection to the server.
2. Server Challenge: The server responds to the client's connection request by sending a challenge back to the client.
3. Key Exchange: The client signs the challenge using its private key and sends the signed challenge back to the server.
4. Server Verification: The server receives the signed challenge and uses the stored public key to verify the signature. If the signature is valid, the server authenticates the client.
5. Authentication Result: If the server successfully verifies the client's signature, the client is authenticated, and the SSH connection is established. The subsequent communication between the client and the server is encrypted using session keys derived from the authentication process.

The security of this authentication method relies on the fact that the private key is kept secret and cannot be derived from the corresponding public key. The server trusts that if someone possesses the private key and can sign the challenge correctly, they must be the legitimate user who owns the private key.

By using public-key authentication, SSH offers a more secure alternative to password-based authentication, as it eliminates the need to transmit and store passwords on the server.

how to store public key?
In an SFTP (SSH File Transfer Protocol) server, the public key needs to be stored in the user's authorized keys file. The authorized keys file is typically located in the user's home directory on the server.
The specific location of the authorized keys file may vary depending on the SFTP server software and configuration. However, a common location is ~/.ssh/authorized_keys.
Here's an overview of the steps to store the public key on the SFTP server:

1. Generate the key pair: Generate the public-private key pair on the client machine using a tool like ssh-keygen. This will create the public key file (id_rsa.pub) and the private key file (id_rsa) by default.
2. Connect to the SFTP server: Connect to the SFTP server using your SFTP client (such as sftp command or a graphical SFTP client).
3. Create the .ssh directory (if it doesn't exist): In the SFTP client, navigate to your home directory on the server and create a .ssh directory if it doesn't already exist. Use the mkdir .ssh command.
4. Copy the public key to the server: Transfer the public key file (id_rsa.pub) from your local machine to the server using the SFTP client's put or similar command. Place it in the .ssh directory on the server.
5. Set permissions: On the server, ensure that the .ssh directory has the correct permissions. Use the command chmod 700 ~/.ssh to set the directory permissions to 700. Also, make sure the authorized keys file has the correct permissions by running chmod 600 ~/.ssh/authorized_keys.
6. Append the public key to the authorized keys file: On the server, append the contents of the public key file (id_rsa.pub) to the authorized keys file (~/.ssh/authorized_keys). You can use the cat command to concatenate the files, like cat id_rsa.pub >> ~/.ssh/authorized_keys.
7. Save and exit: Save the changes to the authorized keys file and exit the SFTP client.
   By following these steps, you can store the public key in the authorized keys file on the SFTP server, allowing the server to authenticate the user using public-key authentication.

And with this, when you specify your private_key in the Airflow connection, the connection should be successful.

[pankajkoti]

I am converting this to a discussion as it does not seem to be an issue at this point time. If after following all the steps, you still feel this is an issue, please let me know and I will convert it back to an issue.

[ryanczarny]

@pankajkoti - just wanted to clarify the below -

The SFTP Server already has the public key and be be connected to using the private key (verified both using CyberDuck as well as a locally running version of Airflow).

Even on the hosted version of Airflow, in the Connections section within the Admin drop-down, when I go into the sftp connection and select Test it returns Connection successfully tested. The issue only occurs is within the DAG as it looks like it is trying to authenticate using a password instead of the private key that is provided for that connection.

[pankajkoti]

Okay, I will try to reproduce this. Can you please share what type of ssh key you're using? RSA, DSS, ECDSA, or something else?

Also could you share a sample of your DAG code?

[ryanczarny]

Yep, key type = ssh-rsa

Function in DAG calling the SFTPHook -

plogger = logging.getLogger("paramiko")
plogger.setLevel(logging.DEBUG)
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.DEBUG)
plogger.addHandler(console_handler)

def send_to_sftp(filename):
    sftp = SFTPHook("connection|sftp")
    sftp.get_conn()
    sftp.conn.logger = plogger
    sftp.store_file('./'+filename,filename,confirm=True)

def main_dag_function():
    #collect information and build dataframe needed to transfer
    #save file off to csv with name being "filename"
    send_to_sftp(filename)

[ryanczarny]

Hey @pankajkoti - I ended up changing it to not use the SFTPHook and got it to work.

def send_to_sftp(filename):
    var_key = Variable.get("Key")
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.client.WarningPolicy)
    host = "host.com"
    key = paramiko.RSAKey.from_private_key(io.StringIO(var_key))
    ssh.connect(host, port=###, username='xxxxxxxx', pkey=key, disabled_algorithms=dict(pubkeys=["rsa-sha2-512", "rsa-sha2-256"]))
    sftp = ssh.open_sftp()
    sftp.put('./'+filename,filename)
    print('File uploaded')

[pankajkoti]

Hi, sorry have been caught up with work, but I see you're using ssh.set_missing_host_key_policy

What if you remove that and clear up your known_hosts file?

Does that still work. I am guessing you're bypassing the host_key validation here and this could be insecure.

I will try tomorrow the SFTPHook and come back with an update.