Retrieving and Importing Data from a Remote File Server (SFTP)

Slate supports retrieving files from a remote SFTP server with certificate-based or password authentication.

Slate initiates outbound connections from the IP addresses listed in the Outbound Networks article. Allow these IP addresses on the remote SFTP server.

⭐ Best Practice
Deliver files to the Technolutions SFTP servers instead of pulling them from a remote server. Imports fail whenever the remote server is unreachable, such as during a network connectivity or maintenance issue. 📖 See also: SFTP Overview

📝 Note
Standard FTP transfers are not encrypted: all data is sent in clear text. Use SFTP instead. If you must use FTP, encrypt the files with PGP.

To use password authentication, skip to Configure a source format for remote retrieval.

Certificate-based authentication

🔑 Required Permission: Security Administrator
The Security Administrator exclusive permission is required to create or edit user accounts. This permission is not inherited by any roles, including the Administrator role.

Certificate-based authentication provides the highest level of security and is the recommended method for retrieving files from a remote server. The service account you create stores the SSH private key used by any source formats configured to import files from a remote server under the User ID for this account.

Setting up certificate-based authentication involves three procedures: generating an SSH key pair, creating a service account in Slate, and configuring the remote server.

Generate an SSH key pair

Generate an SSH key pair with an SSH key generator, such as OpenSSH or PuTTYgen. Keep both parts of the pair: the private key, which you will enter in Slate, and the public key, which you will install on the remote server.

The SSH Private Key should be an RSA key of at least 2048 bits and be saved without a passphrase. In addition, the private key should include the wrapper comments as shown below.

The private key in this example has been shortened for illustrative purposes. If using PuTTYgen, use Conversions → Export OpenSSH Key to format the newly generated Private Key correctly. Save the OpenSSH Key without a passphrase by ignoring PuTTYgen's warnings.

Create a service account in Slate

The service account that you create stores the SSH Private Key used to import files from a remote server using the User ID for this account.

Go to Database → User Permissions
Select New User.
Configure the following settings:
- First Name, Last Name, Preferred Name: Enter a recognizable name that clearly identifies the account as a service account, not the name of an existing Slate user.
- Email: Enter the email address of a real user or distribution group that can receive email notifications.
- User Type: Select Service Account (Remote)
- User ID: Enter a user ID that matches the user ID of the account on the remote server.
- SSH Private Key: Enter the private key of the SSH key pair. For key requirements, see Generate an SSH key pair.
- Active: Select Enable account for access.
- Expires After: If the account is temporary, set an expiration date. Selecting the field opens a date picker.
Select Save.

Configure the remote server

Create a user account on the remote SFTP server with a user ID that matches the User ID of the Slate service account, and install the public key for that account. The exact steps vary by SFTP server software; consult the server documentation for adding users and authorized public keys.

Configure a source format for remote retrieval

The remaining configuration is the same for both certificate-based and password authentication. If a source format for these files does not exist yet, create it before continuing.

📖 See also: Creating a Custom Source Format

Go to Database → Source Formats.
Select the source format.
Select Edit.
Select the Import Automation tab.
In the Import Path/Mask field, enter the path on the remote server and the file name pattern for the files to retrieve. For details, see Format the import path and mask.
In the Import Remote Server field, enter the connection string for the remote server. Supported protocols are sftp (recommended), ftps, ftpes, and ftp for insecure FTP connections. The hostname is the address of the remote server:
1. Certificate-based authentication: sftp://remoteuser@sftp.example.edu, where remoteuser is the User ID of the service account. Slate authenticates with the SSH private key stored on that account.
2. Password authentication: sftp://remoteuser:password@sftp.example.edu, which includes the account password in the connection string.
In the Import Frequency (mins) field, enter how often, in minutes, Slate should repeat the import.
Select Save.

💡 Tip
To specify a port number, append it to the end of the connection string, for example sftp://remoteuser:password@sftp.example.edu:22.

Format the import path and mask

The Import Path/Mask field tells Slate which files on the remote server to retrieve. Enter the full path on the remote server, including any subfolders, followed by a file name pattern. For example, outgoing/SIS_to_Slate_*.txt retrieves any .txt file with a name beginning with SIS_to_Slate from the outgoing subfolder on the remote server.

Path names on many servers, including Unix and Linux servers, are case-sensitive.

You can use wildcard characters in the file name pattern:

A question mark ? matches any single character.
An asterisk * matches any number of characters.

Include a meaningful prefix or suffix in the file name pattern. Open-ended masks like *.txt are too broad and can match unintended files.

💡 Tip
Use date and time variables in the file name pattern to match date-stamped files. For example, to retrieve a file named test%FT%T.txt from a directory called Files on the remote server, enter /Files/test%FT%T.txt.