File, FTP, SFTP Requirements

This section describes the requirements, access privileges, and other features of Fivetran Local Data Processing when using File/FTP/SFTP for replication.

For information about compatibility and supported versions of File/FTP/SFTP with Local Data Processing platforms, see .

For the capabilities supported by Local Data Processing, see .

Introduction

Local Data Processing supports different kinds of file locations:

• regular file locations (a directory on a local file system);
• FTP file locations;
• SFTP file locations.

Generally, the behavior of Local Data Processing replication is the same for all of these kinds of file locations. For all of them, capture is defined with action and integration is defined with action . All other file location parameters are supported and behave normally.

Local Data Processing can connect to an FTP or SFTP file location using a file protocol in two ways:

• connecting with this protocol directly from the hub machine;
• or first connecting to a remote machine with Local Data Processing's remote protocol, and them connecting to the file location from that machine using FTP or SFTP.

These two options have small difference in the timing and latency of capture jobs:

• direct file capture jobs check for new files once a second;
• indirect capture (capture from a non-local file location) jobs check for new files every 10 seconds.

Also, if action Capture is defined without the parameter, the capture job may have to wait for up to a minute before capturing new files. This happens because these jobs rely on comparing timestamps, but the file timestamps in the FTP protocol have low granularity (minutes not seconds).

A proxy server to connect to FTP or SFTP can be configured with File_Proxy_* .

Local Data Processing uses the cURL library to communicate with the file systems.

Integrate Limitations

By default, for file-based target locations, Local Data Processing does not replicate the delete operation performed at the source location.

FTPS: Server Authentication

FTP and WebDAV support certificates for authentication. These are held in the certificate directory HVR_HOME/lib/cert. The following files are included or can be easily created:

• ca-bundle.crt: Used by Local Data Processing to authenticate SSL servers, like FTPS. It can be overridden by creating new file host.pub_cert in this same certificate directory. No authentication is done if neither file is found. Delete or move both files to disable FTPS authentication. This file can be copied from e.g. /usr/share/ssl/certs/ca–bundle.crt on Unix/Linux.
• host.pub_cert: Used to override ca-bundle.crt for server verification for host.
  FTP connections can be unencrypted or they can have three types of encryption; this is called FTPS, and should not be confused with SFTP. These FTPS encryption types are SSL/TLS implicit encryption (standard port: 990), SSL explicit encryption and TLS explicit encryption (standard port: 21).

If the FTP/SFTP connection is made via a remote Local Data Processing machine, then the certificate directory on the remote Local Data Processing machine is used, not the one on the hub machine.

FTPS: Client Authentication

Normally, clients (Local Data Processing) are authenticated using a username and password. In addition to this, you can use client-side SSL certificates.
To set this up, you need to have a public/private key pair in PEM format. Place them in the HVR_CONFIG/etc/cert directory as client.pub_cert and client.priv_key, and define the following actions:

Group	Table	Action/Parameters
File/FTP	*	=HVR_SSL_CLIENT_CERT, =client.pub_cert
File/FTP	*	=HVR_SSL_CLIENT_KEY, client.priv_key
File/FTP	*	If you have a password/phrase on your key, set: =HVR_SSL_CLIENT_KEY_PASSWD, =passwd

You can encrypt the password for Local Data Processing by running: shell hvrcrypt <em>client passwd</em>

If you have a PKCS#12 or PFX file, you can convert it to PEM format using openssl: shell openssl pkcs12 -in <em>cert</em>.p12 -clcerts -nokeys -out <em>client</em>.pub_cert shell openssl pkcs12 -in <em>cert</em>.p12 -nocerts -out <em>client</em>.priv_key

SFTP: Server Authentication

A file (named HVR_CONFIG/files/known_hosts) is used internally during SFTP connections. It is updated the first time Local Data Processing connects to reach the SFTP machine. On Unix or Linux this file can be initialized by copying it from HOME/.ssh/known_hosts.

If the FTP/SFTP connection is made via a remote Local Data Processing machine, then the certificate directory on the remote Local Data Processing machine is used, not the one on the hub machine.

SFTP: Client Authentication

Normally, clients (Local Data Processing) are authenticated using a username and password. Instead of this, you can use client-side keys.
To set this up, you need to have a public/private key pair. Place them in the HVR_CONFIG/etc/cert directory as client.pub and client, and define the following actions:

Group	Table	Action/Parameters
File/FTP	*	=HVR_SSH_PUBLIC_KEY, =client.pub
File/FTP	*	=HVR_SSH_PRIVATE_KEY, =client Note: If you have a password/phrase on your private key, you can set it in the PASSWORD field of the location.

You can generate keys using:

 ssh-keygen -f  client 
content_copy

Related Articles