Using SFTP connector with private/public keys

[pmscorca]

Hi,

I need to implement a pipeline to get some files from an SFTP folder, by using the related Fabric connector that but it doesn't support the authentication by private/public keys.

I've noticed there is this idea: SSH Support for SFTP Connection 

Does it exist any alternative and easy solution, please? Thanks

[nielsvdc · Accepted solution]

Hi @pmscorca,

As you can see from the idea status, the ability to connect to SFTP using a SSH key is planned to be developed. In the meantime you could use a notebook to get the data from the SFTP server.

Try the code below to connect to a SFTP server using paramiko library. Add your private key content to a key vault secret. 

from paramiko import SSHClient, AutoAddPolicy, RSAKey
from io import StringIO

# Configuration
SFTP_HOST = "<sftp-server>"
SFTP_PORT = 22
SFTP_USERNAME = "<your-username>"
KEY_VAULT_NAME = "<your-keyvault-name>"
PRIVATE_KEY_SECRET_NAME = "<sftp-private-key>"
REMOTE_DIRECTORY = "</sftp/folder_with_files>"
LAKEHOUSE_FOLDER = "Files/sftp_documents"
LAKEHOUSE_PATH = "/lakehouse/default"

# Retrieve private key from Key Vault
private_key_content = notebookutils.credentials.getSecret(KEY_VAULT_NAME, PRIVATE_KEY_SECRET_NAME)

# Create lakehouse folder if not exists
notebookutils.fs.mkdirs(LAKEHOUSE_FOLDER)

# Create SSH client
ssh_client = SSHClient()
ssh_client.set_missing_host_key_policy(AutoAddPolicy())

try:
    # Connect using private key from memory
    pkey = RSAKey.from_private_key(StringIO(private_key_content))
    ssh_client.connect(
        hostname=SFTP_HOST,
        port=SFTP_PORT,
        username=SFTP_USERNAME,
        pkey=pkey,
        look_for_keys=False,
        allow_agent=False
    )
    
    # Download file to Lakehouse path
    sftp_client = ssh_client.open_sftp()
    
    # List all files in directory
    print(f"Listing files in {REMOTE_DIRECTORY}:")
    file_attrs = sftp_client.listdir_attr(REMOTE_DIRECTORY)
    
    files_to_download = []
    for attr in file_attrs:
        # Skip directories, only process files
        if not (attr.st_mode & 0o040000):  # Not a directory
            files_to_download.append(attr.filename)
            print(f"  - {attr.filename} ({attr.st_size} bytes)")
    
    #print(f"\nFound {len(files_to_download)} file(s) to download")
    
    # Download all files directly to Lakehouse
    downloaded_count = 0
    for filename in files_to_download:
        remote_file_path = f"{REMOTE_DIRECTORY}/{filename}"
        lakehouse_file_path = f"{LAKEHOUSE_PATH}/{LAKEHOUSE_FOLDER}/{filename}"
        
        try:
            # Download directly to Lakehouse
            sftp_client.get(remote_file_path, lakehouse_file_path)
            print(f"Downloaded: {filename} -> {lakehouse_file_path}")
            
            downloaded_count += 1
        except Exception as e:
            print(f"Error downloading {filename}: {str(e)}")
    
    print(f"\nSuccessfully downloaded {downloaded_count}/{len(files_to_download)} file(s)")
  
finally:
    ssh_client.close()

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.

[pmscorca]

Hi, thanks for your reply.

However I hope that a such feature will be released as soon as possible. Azure Data Factory has it!

Thanks