Connect to ftp using TLS and passive mode

[daRocha]

Hi,

I am trying to connect to a ftps server using passive mode.

Everything seems to be working fine until the moment I want to transfer data either from the local computer to the server or vice-versa.

This is the code I am using to test the connection and things seem to work as expected:

%let _host=###;
%let _user=###;
%let _pwd=###;

/**************************************************************************\
	TEST 1 (Server status): Passed 
\**************************************************************************/
options set=SAS_FTP_AUTHTLS="1";

filename LLL ftp '' rstat 
	host="&_host." 
	user="&_user." pass="&_pwd." 
	debug ;

data _null_;
   infile LLL;
   input;
   list;
run;

And this is the generated log:

NOTE: >>> AUTH TLS
NOTE: <<< 234 AUTH TLS successful
NOTE: >>> USER ####
NOTE: <<< 331 Password required for ####
NOTE: >>> PASS XXXXXXXX
NOTE: <<< 230 User eba logged in
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 200 UTF8 set to on
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 'OPTS UTF8' not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 500 OPTS UTF-8 not understood
NOTE: >>> STAT
NOTE: User #### has connected to FTP server on Host #####.#####.com .
NOTE: The infile LLL is:
2                                                          The SAS System                             18:50 Tuesday, January 7, 2020

      Filename=,
      Pathname,Local Host Name=######,
      Local Host IP addr=::1,
      Service Hostname Name=#####.#####.com,
      Service IP addr=###.###.###.###,Service Name=FTP,
      Service Portno=21,Lrecl=32767,Recfm=Variable

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
1         211-Status of 'FTPS server' 45
2          Connected from 195.227.57.67 (195.227.57.67) 45
3          Logged in as #### 17
4          TYPE: ASCII, STRUcture: File, Mode: Stream 43
5          No data connection 19
6         211 End of status 17
NOTE: >>> QUIT
NOTE: 6 records were read from the infile LLL.
      The minimum record length was 17.
      The maximum record length was 45.
NOTE: DATA statement used (Total process time):
      real time           0.82 seconds
      cpu time            0.06 seconds

I can even create directories in the remote server:

Code:

/**************************************************************************\
	TEST 2: Passed (Make directory)
\**************************************************************************/
options set=SAS_FTP_AUTHTLS="1";

filename LLL ftp '' /*rstat */
	host="&_host." 
	user="&_user." pass="&_pwd." 
	passive 
	rcmd="cwd /test;mkd test2;"
	debug ;

data _null_;
   infile LLL;
   input;
   list;
run;

Log:

NOTE: >>> AUTH TLS
NOTE: <<< 234 AUTH TLS successful
NOTE: >>> USER ####
NOTE: <<< 331 Password required for ####
NOTE: >>> PASS XXXXXXXX
NOTE: <<< 230 User eba logged in
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 200 UTF8 set to on
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 'OPTS UTF8' not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 500 OPTS UTF-8 not understood
2                                                          The SAS System                             18:50 Tuesday, January 7, 2020

NOTE: >>> cwd /test
NOTE: <<< 250 CWD command successful
NOTE: >>> mkd test2
NOTE: <<< 257 "/test/test2" - Directory successfully created
NOTE: User eba has connected to FTP server on Host ############# .
NOTE: The infile LLL is:
      Filename=,
      Pathname,Local Host Name=######,
      Local Host IP addr=::1,
      Service Hostname Name=######,
      Service IP addr=##.###.###.###,Service Name=FTP,
      Service Portno=21,Lrecl=32767,Recfm=Variable

NOTE: >>> QUIT
NOTE: 0 records were read from the infile LLL.
NOTE: DATA statement used (Total process time):
      real time           0.75 seconds
      cpu time            0.04 seconds

However, this stops being so successful whenever a try to download or upload a file:

Code:

filename inv ftp 'mtest.txt' 
	host="&_host." user="&_user." pass="&_pwd." 
	cd='/test/'
	RECFM=S 
	passive
	rcmd="cwd /test;"
	debug ;

filename lcl "&mytemp.\mtest.txt" recfm=n;

data _null_;
	N=1;
	infile inv NBYTE=N;
	input;
	file lcl;
	put _infile_ @@;
run;

Log:

NOTE: >>> AUTH TLS
NOTE: <<< 234 AUTH TLS successful
NOTE: >>> USER ####
NOTE: <<< 331 Password required for ####
NOTE: >>> PASS XXXXXXXX
2                                                          The SAS System                             18:50 Tuesday, January 7, 2020

NOTE: <<< 230 User #### logged in
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 200 UTF8 set to on
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 'OPTS UTF8' not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 500 OPTS UTF-8 not understood
NOTE: >>> PBSZ 0
NOTE: <<< 200 PBSZ 0 successful
NOTE: >>> PROT P
NOTE: <<< 200 Protection set to Private
NOTE: >>> PASV
NOTE: <<< 227 Entering Passive Mode (##,###,##,###,255,155).
ERROR: The connection has timed out..
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file LCL is:
      Filename=E:\SASDATA\PDRIVE\jrocha\WORK\temp\mtest.txt,
      RECFM=N,LRECL=256,File Size (bytes)=0,
      Last Modified=09 January 2020 16:14:21,
      Create Time=09 January 2020 12:29:38

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           21.80 seconds
      cpu time            0.01 seconds

From what I can see from the logs above I can connect to the server but I cannot create a data connection. Any ideas why this is happening and/how can I overcome it?

Thank you

Julio

[rbetancourt]

It is best to diagnose this issue by taking SAS out of the process and simply call SFTP from the command line to transfer the file in question.  When performing these tests without SAS, check to see if the SFTP command line command has a switch, like -v for verbose logging.

The first test to run without SAS is to determine if you have a mis-match for UTF encoding between the client and the server.  

NOTE: <<< 200 UTF8 set to on
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 'OPTS UTF8' not understood

[daRocha]

Thanks for the reply @rbetancourt,

this is not an sftp connection, it is an ftps connection (or, if you prefer FTP over TLS).

I am on a windows server and windows command line does not have the possibility of connecting ftp over TLS. When I try to connect using the Windows I get the message:

550 SSL/TLS required on the control channel

Btw, sftp works fine for me (connecting to a different server) and simple ftp also works fine.

This is the first time I am using ftps with SAS and I cannot make it work 😞

[CR-Horton]

I wanted to circle back on this thread as I am experiencing essentially the same issue. My connection is timing out. I am able to connect to this FTP via Filezilla without any issues. However, when I connect via Filezilla I do get a message about accepting a certificate. Note, per the FTP instructions "Access to these files
requires a secure (SSL enabled) FTP connection." What do I need to adjust to allow me to access the file?

Here is my code:

filename Ekey FTP "Inventory.csv"
CD="/"
HOST="ftp.ekeystone.com"
USER="XXXXXXXX"
PASS="XXXXXXXX";

data Keystone_Inventory;
infile Ekey dlm=',' dsd missover LRECL=32767 firstobs=2;
input
Vendor_Name :$12.
VCPN :$13.
Vendor_Code :$3.
Part_Number :$10.
Manufacturer_Part_No :$23.
Long_Description :$100.
Jobber_Price :best12.
Cost :best12.
UPSable :$5.
Core_Charge :best12.
Case_Qty :best12.
IsNonReturnable :$5.
Prop65Toxicity :$1.
UPC_Code :$14.
IsOversized :$5.
Weight :best12.
Height :best12.
Length :best12.
Width :best12.
AAIACode :$4.
IsHazmat :$5.
IsChemical :$5.
USAAccessorial :$5.
East_Qty :best12.
Midwest_Qty :best12.
California_Qty :best12.
Southeast_Qty :best12.
PacificNW_Qty :best12.
Texas_Qty :best12.
GreatLakes_Qty :best12.
Total_Qty :best12.
KitComponents :$1.
IsKit :$5.;
run;

My Log:

filename Ekey FTP "Inventory.csv"
357 CD="/"
358 HOST="ftp.ekeystone.com"
359 USER="XXXXXXXX"
360 PASS=XXXXXXXXXX;
361
362 data Keystone_Inventory;
363 infile Ekey dlm=',' dsd missover LRECL=32767 firstobs=2;
364 input
365 Vendor_Name :$12.
366 VCPN :$13.
367 Vendor_Code :$3.
368 Part_Number :$10.
369 Manufacturer_Part_No :$23.
370 Long_Description :$100.
371 Jobber_Price :best12.
372 Cost :best12.
373 UPSable :$5.
374 Core_Charge :best12.
375 Case_Qty :best12.
376 IsNonReturnable :$5.
377 Prop65Toxicity :$1.
378 UPC_Code :$14.
379 IsOversized :$5.
380 Weight :best12.
381 Height :best12.
382 Length :best12.
383 Width :best12.
384 AAIACode :$4.
385 IsHazmat :$5.
386 IsChemical :$5.
387 USAAccessorial :$5.
388 East_Qty :best12.
389 Midwest_Qty :best12.
390 California_Qty :best12.
391 Southeast_Qty :best12.
392 PacificNW_Qty :best12.
393 Texas_Qty :best12.
394 GreatLakes_Qty :best12.
395 Total_Qty :best12.
396 KitComponents :$1.
397 IsKit :$5.;
398 run;

ERROR: Open failed for file EKEY
ERROR: The connection has timed out..
NOTE: The SAS System stopped processing this step because of
errors.
WARNING: The data set WORK.KEYSTONE_INVENTORY may be
incomplete. When this step was stopped there were 0
observations and 33 variables.
WARNING: Data set WORK.KEYSTONE_INVENTORY was not replaced
because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 21.10 seconds
cpu time 0.14 seconds

Thanks in advance!

[SASKiwi]

For starters you need to switch from using the FTP FILENAME option to SFTP. That will enable you to establish a secure FTP connection.

[Tom]

How old is the server you are trying to connect to?

I don't think SSL is supposed to be used anymore.  At least that is what Wikipedia says.

https://en.wikipedia.org/wiki/FTPS

FTPS (also known FTP-SSL, and FTP Secure) is an extension to the commonly used File Transfer Protocol (FTP) that adds support for the Transport Layer Security (TLS) and, formerly, the Secure Sockets Layer (SSL, which is now prohibited by RFC7568) cryptographic protocols.

[Kurt_Bremser]

FTP is deprecated (except for anonymous transfers) because it transmits everything, including credentials, unencrypted. Use SFTP instead. SFTP from SAS requires that public/private key authentication is set up for that host/user pair outside of SAS.

[Zonie24]

Hello @daRocha!

I saw your message regarding the issues with the FTP transfer for Ekeystone. I'm having the same issues. Going through the same steps as listed in the SDK support site but can't get anything to connect. Wondering if you were able to figure out the issue and connect.

Thank you,