Sending Scheduled Exports to a Remote SFTP Server

Slate supports sending a scheduled export to a remote SFTP server.  This practice is generally discouraged, as the export process will fail if the remote SFTP server becomes unavailable for any reason, such as a network connectivity or maintenance issue.  It is generally recommended that all exports be delivered to the Technolutions SFTP servers, where we can provide high availability and from which they can be downloaded at any time.  These best practices aside, we do support pushing an exported file to a remote SFTP server, where this may be useful to your business process.

The recommended configuration for sending files to a remote server is to use the SSH File Transfer Protocol (SFTP) with certificate-based authentication, as this will provide the highest level of security.

FTP is not an encrypted transfer protocol, which means that all data is sent in clear text. Therefore, FTP is not recommended.

Outbound connections are initiated from the IP address 34.197.57.115, 34.197.58.165, and 35.161.51.209.

Certificate-Based Authentication
Create a Remote Service Account User

The service account that you create will be able to store the SSH Private Key, which will be used by any exports that are configured to export files to a remote server using the User ID for this account.

Note: The Security Administrator exclusive permission (not inherited by any roles, even the Administrator role) is required to create or edit user accounts.

  1. Select Database on the top navigation bar and select User Permissions.
  2. Select New User
    • If you do not already have a keypair to use, you can use an SSH key generator, like this.
      Note: The corresponding public key needs to be installed on the remote server for this User ID.
    • Since this is a service account, enter name information that clearly states this (rather than entering the name of a person who is already a Slate user).
    • Enter an email address of a real user or distribution group that is able to receive email notifications.
    • Change the User Type to Service Account (Remote).
    • Provide a user ID (this should match the user ID created in the remote system).
    • Enter the SSH Private Key.
    • Certificate-based authentication does not require a password, so leave the password field blank.
    • Be sure to check the Enable account for access checkbox.
  3. Select Save.

 Tip

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.

Note: the private key above has been shortened for illustrative purposes.

Schedule Export for the Query
  1. Select Queries / Reports on the top navigation bar.
  2. Go to the query that will be exporting data to the remote server.
  3. Select Edit Query.
  4. Select Schedule Export.
  5. Update the Destination setting to Custom File Transfer
  6. Under Connection, provide the protocol, username, and hostname (e.g. sftp://remoteuser@ft.technolutions.net)  Be sure to set the username as the User ID of the service account you created in the previous steps.  The hostname should be the remote server address.

  7. See the Path section below for important notes about configuring the path.

All other settings are the same as when you configure the file to use the Technolutions SFTP servers.

Password-Based Authentication
Scheduled Export for Query
  1. Select Queries / Reports on the top navigation bar.
  2. Go to the query that will be exporting data to the remote server.
  3. Select Edit Query.
  4. Select Schedule Export.
  5. Update the Destination setting to Custom File Transfer
  6. Under Connection, provide the protocol, username, password, and hostname (e.g. sftp://remoteuser:mypasswordthatshouldbecomplex@ft.technolutions.net)  

    • The protocol should be sftp for SFTP connections (recommended), ftps for FTPS connections, ftpes for FTPES connections, or ftp for the much less secure FTP connections.

      Note: If you must use FTP, we highly recommend using PGP encryption (see the section below)
    • The username should match the user name in the remote system.
    • The password should be the password that the remote system expects for the user name.
    • The hostname should be the remote server address.

    7. See the Path section below for important notes about configuring the path.

PGP Encryption

SFTP offers secure transfer, so PGP Encryption is superfluous, but still supported.  FTP traffic is not encrypted, so if you must use FTP, it is highly recommended that you encrypt the exported data with PGP encryption.

You are able to configure a data export to encrypt the generated file prior to exporting to the remote server within the Schedule Export options:

  1. Update the Encryption setting to PGP Encryption.
  2. In the Public Key box, paste the Public Key that Slate should use to perform the encryption.

    If you do not already have a PGP key pair, you may use a PGP key pair generator, including this website to generate a new key pair.
    Note: The server or entity that will be consuming the file must have the matching Private Key configured in order to read the file.

Important

If the export is configured to allow empty files, you may receive the following error message: File or folder... does not exist. System Error. Code: 2. The system cannot find the file specified.

Exports to remote servers are first generated on local Technolutions SFTP servers and then transferred to the remote endpoint. For empty files, this process attempts to move the export results but is not able to find the file, which causes the error.

Switching to the Suppress empty files setting prevents the error.  

 Tip

If a port number must be specified, this can be done in the Connection field in the Schedule Export settings, e.g.:

sftp://remoteuser:mypasswordthatshouldbecomplex@ft.technolutions.net:22
Path

For the path, you should enter the remote path on the server along with the name of the file.

  Best Practice

Using date/time variables is recommended. This can help prevent the following type of error: a file or directory with the same name already exists.  

For example, if you want to upload the file into a directory called Files on the remote server, and the file name should be "test%FT%T.txt" you would enter:

/Files/test%FT%T.txt

Path names on many servers, including Unix/Linux servers, are case-sensitive, so keep this in mind when entering the path.

Was this article helpful?
4 out of 4 found this helpful