SFTP Server
  • 5 Minutes to read
  • Dark
    Light

SFTP Server

  • Dark
    Light

Article Summary

SFTP, or Secure File Transfer Protocol, is a network protocol used for securely transferring files between a client and a server. An SFTP server is a server that supports this protocol, enabling secure and encrypted file transfers over a network, making it a safer and more protected alternative to traditional FTP for data exchange.

Prerequisites

Authorize Connection to SFTP Server

In SFTP

Please keep in mind that this guide provides general instructions, and your individual setup might need a different approach or some extra steps. Always take your organization's security guidelines into account when you're making these changes. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly.

Enable Port Forwarding

Enable port forwarding to make sure your server is accessible from the Internet by following these steps.

  1. Assign a static IP to your server on your local network. This makes sure that the server's address remains constant, so other devices and services always know where to find it.
    1. Windows: You can set a static IP from the network settings.
    2. Linux: This is typically done by editing network configuration files, which vary by distribution (e.g., /etc/network/interfaces or /etc/netplan/ directory for Ubuntu).
  2. Configure port forwarding on your router: Log in to your router's web interface and look for a section called Port Forwarding, Virtual Server, or something similar. Create a new port forwarding rule:
    1. Service or Application: Name it e.g. SFTP Server.
    2. Internal IP: Enter the static IP address of your server.
    3. Port Range: For SFTP, the standard port is 22.
    4. Protocol: Usually TCP for FTP and SFTP.
    5. External IP: Unless you are restricting access to specific external IPs, you can leave this set to all or the equivalent option.
  3. Save your new rule.

Create User

These following steps are for Windows users. If you use Linux, please see use these steps to configure your user instead.

To create a user with required permissions for SFPT, please follow these steps.

  1. Right-click This PC or My Computer, select Manage and open the Computer Management tool.
  2. Navigate to Local Users and Groups and select Users.
  3. Right-click Users and proceed with creating a new user. Use something like DataddoUser and create a strong password.

Configure Access Permissions

  1. Locate your folder, right-click in and select Properties.
  2. Navigate to the Security tab and click on Edit.
  3. Click on Add to add your user or a group. Enter the username and verify.
  4. With the user selected in the Group or user names section, in the Permissions for [DataddoUser] section, check the permissions you want to assign to this user:
    1. Full control
    2. Modify
    3. Read & execute
    4. List folder contents
    5. Read
    6. Write
  5. Click on Apply to save your changes.

Allow Network Connection

For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access at the firewall level. Here are the general steps:

  1. Identify the firewall that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
  2. Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
  3. Look for the setting that allows you to define inbound rules or policies.
  4. Create a new rule or policy that allows inbound traffic on the port that the service is using (the default is 22) from the Dataddo IP addresses.
  5. Save your changes and test the connection from Dataddo to make sure everything is working as intended.

In Dataddo

  1. On the Authorizers page, click on Authorize New Service and select FTP server.
  2. You will be asked to fill the following fields
    1. Server IP or Host name: The address of your SFTP server.
    2. Username: The username for the SFTP account.
    3. Password: The password for the SFTP account.
    4. Port: The port your SFTP server is running on (default value is 22).
  3. [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
  4. Click on Save.

Create a New SFTP Destination

  1. Under the Destinations tab, click on the Create Destination button and select the destination from the list.
  2. Select your account from the drop-down menu.
  3. Fill in the Path to your directory.
  4. Name your destination and click on Save to create your destination.
Need to authorize another connection?

Click on Add new Authorizer in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

Creating a Flow to FTP Server

  1. Navigate to Flows and click on Create Flow.
  2. Click on Connect Your Data to add your sources.
  3. Click on Connect Your Data Destination to add the destination.
  4. Choose the write mode and fill in the other required information.
  5. Check the Data Preview to see if your configuration is correct.
  6. Name your flow, and click on Create Flow to finish the setup.

File Partitioning

File partitioning refers to the practice of dividing large datasets into smaller, more manageable segments or partitions based on specific criteria, such as values in a particular column or range of values. Each partition contains a subset of the data that shares common attributes or characteristics. File partitioning is commonly used to improve data organization, query performance, and data management.

Dataddo supports file partitioning during flow creation. If you use, for example,

file_{{1d1|Ymd}}

Dataddo will create a file every day, e.g. file_20xx0101, file_20xx0102 etc.


Troubleshooting

Configuring User Permissions in Linux

To create a user with the necessary permissions for SFTP in Linux, follow these steps.

  1. Connect to your server: Access your server's command line interface. This might be directly or via SSH, using a host address and port that your server operates on.
  2. Create a new user: This can typically be done using the useradd or adduser command followed by the desired username. Follow the prompts to set a strong password and, optionally, fill in any additional user information. For instance:
    sudo adduser new_user
    
  3. Create a directory for SFTP access: If it doesn’t already exist, create a directory that you want the new user to access via SFTP. For example:
    sudo mkdir -p /home/new_user/file_name
    
  4. Set the owner and permissions for the directory: You need to set the appropriate ownership and permissions for the directories and files that the user should access. For example:
    sudo chown new_user:new_user /home/new_user/file_name
    sudo chmod 700 /home/new_user/file_name
    
  5. Configure SFTP settings in the SSHD config file: Open the SSH daemon's configuration file in a text editor. This is usually located at /etc/ssh/sshd_config. You might add or modify a section like the following:
    Match User new_user
    ChrootDirectory /home/new_user
    ForceCommand internal-sftp
    AllowTCPForwarding no
    X11Forwarding no
    
  6. Restart the SSH service: Apply the changes by restarting SSH.


Was this article helpful?

What's Next