topic Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist in SAS Programming

FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

rbetancourt — Tue, 17 Oct 2017 18:11:54 GMT

I want to use the SAS 9.4 FILENAME SFTP access method between 2 Linux servers (local and remote) to read SASHELP.class on the local server and write this dataset as a text file on the remote server. I have tested this without having SAS execute the program below. From the local server, I can SFTP to the remote server to GET and PUT text files between the two servers. With the SAS program, I have also checked permissions in the remote environment.

NOTE: AUTOEXEC processing completed.

1 filename out sftp "data.txt" debug
2 user = 'DIR\thomas.betancourt'
3 options= 'pw "P@ssword1"'
4 host = '10.89.11.9'
5 cd = '/home/DIR/thomas.betancourt/jsn'
6 recfm = v;
7
8 data _null_;
9 set sashelp.class;
10 file out;
11 put _all_;
12 run;

NOTE: usage: sftp [-1Cv] [-B buffer_size] [-b batchfile] [-F ssh_config]

[-o ssh_option] [-P sftp_server_path] [-R num_requests]

[-S program] [-s subsystem | sftp_server] host
sftp
[user@]host[:file ...]
sftp [user@]host[:dir[/]]
sftp -b
batchfile [user@]host

NOTE: cd /home/DIR/thomas.betancourt/jsn

ERROR: Directory or file data.txt doesn't exist.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 1.50 seconds
cpu time 0.00 seconds

ERROR: Errors printed on page 2.

Notice on line 5, the cd command is honored as indicated by the NOTE in the SAS log. I've run out of ideas, and yet, this should be a rather straight-forward exercise 😞

Any insights are appreciated. Thanks,

Randy Betancourt

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Tue, 17 Oct 2017 19:04:11 GMT

Since the external sftp won't even start (this causes the "usage" note), nothing will work. Have you set up passwordless authentication (public/private key) for the user?

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

rbetancourt — Tue, 17 Oct 2017 21:19:20 GMT

This the part where i am not clear from the doc. I am pretty sure SAS starts the STFP sub-process, evidenced by the request to change directory request being successful from the SAS log. Without the use of SAS, the first time I executed SFTP between these 2 servers, I received the yes/no prompt to create the RSA keys. I've verified that the .ssh/know_hosts files contain the RSA keys on the local server that identifies the remote server as a known host.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Wed, 18 Oct 2017 04:49:27 GMT

Did you have to enter a password?

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Wed, 18 Oct 2017 06:40:04 GMT

Actually, SAS couldn't start your sftp correctly. What you see in the log is the typical output you get when you call sftp with incorrect commandline parameters:

$ sftp -garbage
sftp: illegal option -- g
usage: sftp [-1246Cpqrv] [-B buffer_size] [-b batchfile] [-c cipher]
          [-D sftp_server_path] [-F ssh_config] [-i identity_file] [-l limit]
          [-o ssh_option] [-P port] [-R num_requests] [-S program]
          [-s subsystem | sftp_server] host
       sftp [user@]host[:file ...]
       sftp [user@]host[:dir[/]]
       sftp -b batchfile [user@]host

This is the output of an invalid sftp call on our AIX.

Your next note

NOTE: cd /home/DIR/thomas.betancourt/jsn

might come from the fact that the cd is not submitted to the sftp, but locally to the shell.

I guess that this

options= 'pw "P@ssword1"'

is the reason for your troubles, as this option is not recognized by the sftp command, and should not be there at all. As I already said, SAS requires that a passwordless connection is set up with sftp/ssh. Verify this by doing

sftp user@host

from the commandline. You should immediately get a connection without any further input required. If not, you have to set up public/private key authentication.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Wed, 18 Oct 2017 06:42:48 GMT

PS Note that no security-aware application allows passing passwords on the commandline, as commandlines can easily be retrieved on servers by displaying the process status (ps -f on UNIX). That's why sftp does not have a pw option.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

rbetancourt — Wed, 18 Oct 2017 14:32:07 GMT

Hi KurtBremser,

Thank you for the insights and help. I agree with your observation about security-aware applications. I need this job to run batch. When I omit the pw option, I get prompted by the shell to input a pw string, so this obviously is not the right behavior. Below is my Linux session using sftp outside the SAS process:

$ uname -a
Linux slrs01va6dmss01 2.6.32-696.13.2.el6.x86_64 #1 SMP Fri Sep 22 12:32:14 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
$
$ sftp 10.89.11.9
Connecting to 10.89.11.9...
sftp> cd jsn
sftp> pwd
Remote working directory: /home/DIR/thomas.betancourt/jsn
sftp> ! ls /home/DIR/thomas.betancourt/jsn
ls: cannot access /home/DIR/thomas.betancourt/jsn: No such file or directory
Shell exited with status 2

On the local server, the directory ~/jsn does not exist, is just to illustrate the sftp process is connecting to a remote server using passwordless ssh. And obviously, there are no passphrase challenges here.

One the SAS execution side, omitting the password string gets me further, as the log now contains a note indicating there is an attempt by the sftp sub-process from SAS to connect to the remote server. The authentication failure is puzzling given the sftp process illustrated above.

I also altered the format for the user=string. I was thinking that the sftp sub-process started from SAS may not like the the user-id convention used in this shop as:

DIR\thomas.betancourt@slrs01va6f1clw5

Any insights are really apprecaited!

1 filename out sftp "data.txt" debug
2 user = 'thomas.betancourt@slrs01va6flclw5'
3 /* options= 'pw P@ssw0rd1"' */
4 host = '10.89.11.9'
5 cd = '/home/DIR/thomas.betancourt/jsn'
6 recfm = v;
7
8 data _null_;
9 set sashelp.class;
10 file out;
11 put _all_;
12 run;

NOTE: Connecting to 10.89.11.9...

NOTE: Received disconnect from 10.89.11.9: 2: Too many authentication failures
for thomas.betancourt@slrs01va6flclw5

NOTE: The file OUT is:
Filename=data.txt

NOTE: put "/tmp/SAS_work309500003AA1_slrs01va6dmss01/#LN00010" data.txt

ERROR: Directory or file data.txt doesn't exist.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Thu, 19 Oct 2017 07:42:11 GMT

When you do a simple

sftp hostname

sftp will use your current user-id for authentication on the remote server.

The syntax for using another user (or any user/hostname combination) is

sftp user@hostname

so I would reduce the filename statement to the bare necessities:

filename out sftp "data.txt"
  debug
  user = 'thomas.betancourt'
  host = '10.89.11.9'
  cd = '/home/DIR/thomas.betancourt/jsn'
  recfm = v
;

as this will transIate to

sftp thomas.betancourt@10.89.11.9

I also suggest using the domain name of the host instead of the IP address, to avoid maintainance when the server is moved in the network.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Tom — Thu, 19 Oct 2017 13:32:23 GMT

You will most likely need to create a key file. You can use the -o option that you see in the error message to pass in the IndentityFile option that points to the key file. Try it first from the command line.

sftp -oIdentityFile=~/.ssh/mykey.txt 'DIR\thomas.betancourt'@10.89.11.9

and see if you can connect to the remote host without it prompting for a password.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

rbetancourt — Thu, 19 Oct 2017 13:45:57 GMT

Your solution to stripping down the FILENAME statement to the bare minimum is the correct answer! At:

http://go.documentation.sas.com/?docsetId=lestmtsglobal&docsetTarget=p0xln1fiwsr340n1xxf4mkmfxp6f.htm&docsetVersion=9.4&locale=en

for the FILENAME statement states for the user= option syntax:

The username is not typically required on LINUX or UNIX hosts when using public key authentication.

1 filename out sftp "data.txt" debug
2 host = '10.89.11.9'
3 cd = '/home/DIR/thomas.betancourt/jsn'
4 recfm = v;
5
6 data _null_;
7 set sashelp.class;
8 file out;
9 put _all_;
10 run;

NOTE: Connecting to 10.89.11.9...

NOTE: sftp> cd /home/DIR/thomas.betancourt/jsn

NOTE: The file OUT is:
Filename=data.txt

NOTE: sftp> put "/tmp/SAS_work9D860000500A_slrs01va6dmss01/#LN00010"
data.txt
Uploading /tmp/SAS_work9D860000500A_slrs01va6dmss01/#LN00010 to
/home/DIR/thomas.betancourt/jsn/data.txt

NOTE: 19 records were written to the file OUT.

Just goes to show how the adage, "less is more" is very often true, I really appreciate your help here! Thank you.

Re: FILENAME SFTP access method ERROR: Directory or file data.txt doesn't exist

Kurt_Bremser — Thu, 19 Oct 2017 14:04:52 GMT

We do use the user= option, because we solve several problems in one step

- the userid used by the scheduler is the same on the production DB and on the SAS server

- we do not want our SAS batch jobs to be able to write to the production DB, so connecting with same userid is out of the question

- we want several developers on SAS to be able to have the same read access to the DB server, for development and testing

so we have a dedicated "SAS user" that is used by the scheduler user and the developers when connecting to the DB server; all those users have the same entries in the id_rsa files in their .ssh directory that correspond to the id_rsa.pub on the server.

Glad I could be of help.