Sending Scheduled Exports to a Remote SFTP Server
- 06 May 2024
- 5 minute read
- Print
- DarkLight
- PDF
Sending Scheduled Exports to a Remote SFTP Server
- Updated 06 May 2024
- 5 minute read
- Print
- DarkLight
- PDF
Article Summary
Share feedback
Thanks for sharing your feedback!
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 addresses listed in the Outbound Networks article.
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.
Select Database on the top navigation bar and select User Permissions.
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.
Select Save.
✨ TipThe 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 be exported in OpenSSH format and include the wrapper comments as shown below.
Note: the private key above has been shortened for illustrative purposes.
Schedule Export for the Query
Select Queries / Reports on the top navigation bar.
Go to the query that will be exporting data to the remote server.
Select Edit Query.
Select Schedule Export.
Update the Destination setting to Custom File Transfer
Under Connection, provide the protocol, username, and hostname (e.g. sftp://[email protected]) 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.
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
Select Queries / Reports on the top navigation bar.
Go to the query that will be exporting data to the remote server.
Select Edit Query.
Select Schedule Export.
Update the Destination setting to Custom File Transfer
Under Connection, provide the protocol, username, password, and hostname (e.g. sftp://remoteuser:[email protected])
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. Certain special characters should not be used within the username or password, or, if they are, they must be URL encoded. Common special characters and their encodings are listed in the table below.
Character
Encoding
Space
%20
# (number sign/hash)
%23
% (percent sign)
%25
+ (plus sign)
%2B
/ (slash)
%2F
@ (at symbol; only when included as part of the username or password)
%40
: (colon)
%3A
; (semicolon)
%3B
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:
Update the Encryption setting to PGP Encryption.
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.
🔔 ImportantIf 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.
✨ TipIf a port number must be specified, this can be done in the Connection field in the Schedule Export settings, e.g.:
sftp://remoteuser:[email protected]:22
Path
For the path, you should enter the remote path on the server along with the name of the file.
⭐ Best PracticeUsing 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?
