topic Re: SFTP Private key authentication SAS Code throws ERROR in SAS Programming

SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 07:02:40 GMT

Hi Team,

iam trying to read the file from remote server by making use of private key authentication in SAS code.but iam getting error.please help

%let host = XXXXXXXXXXXXXXXXXXXXXXXX.com;
%let ftpuser = YYYYYYYYYY;
filename ftpin sftp "CCCCCCC..xls" recfm=f host="&host" user="&ftpuser"
recfm=f cd= "/AAA/ALL/SSS"
options="-oPort=40022 -i /hhh/uuuu/uuuu/.ssh/ggg.key -o StrictHostKeyChecking=no";
data _null_;
infile ftpin;
/* file ftpout;*/
input;
put _infile_;
run;

error log:

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

The SAS task name is [OBJECT_E]
Segmentation Violation

Traceback of the Exception:

0xf548(?)
yxrasgn+0x294
yxrasgn+0x38 -- bridge stub
ytlibf+0xd80
yasany+0x4c8
yasany2+0x188
yttknx+0x1480
yalexec+0xf98
yalinln+0x4d8
yalproc+0x234
yalproc+0x38 -- bridge stub
sasxobj+0x3d8
vvtentr+0x194
_pthread_body+0xf0

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 07:21:42 GMT

Open a track with SAS technical support. When SAS throws a segmentation violation (a memory access error, caused either by a null pointer or pointing to unallocated memory), it encountered a condition it is unprepared for.

Does the ERROR happen immediately, or after some time running the sftp?

Re: SFTP Private key authentication SAS Code throws ERROR

ballardw — Tue, 31 Mar 2020 09:09:53 GMT

Note that INFILE in a data step basically expects a TEXT file of some form, plain text, delimited text or similar to read the file. XLS files are NOT text and I would not expect to read one with a data step.

You might try Proc Import to generate a data set.

As an example create a small xls file locally and try that data step on that file and see what happens.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 09:33:08 GMT

After sometime just 2seconds i will get below dialog box and then i click ok to see the log.please help .

why it is the causing issue.is it because of SAS EG application issue or any other SFTP issue

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 09:40:21 GMT

you mean SFTP will not work for excel sheet....Would you please point me where i am exactly going wrong and where i need to modify the code to work this please.i did not clearly understood please.

Re: SFTP Private key authentication SAS Code throws ERROR

andreas_lds — Tue, 31 Mar 2020 09:44:48 GMT

@JJP1 wrote:

you mean SFTP will not work for excel sheet....Would you please point me where i am exactly going wrong and where i need to modify the code to work this please.i did not clearly understood please.

The infile statement can't be used to read an excel-file regardless of the place where it is stored (local file-system, unc-path, sftp-server). Excel files are read either by defining a libref with the libname statement or by proc import.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 09:48:07 GMT

iam sorry,but we all ready reading the excel file using infile statement and it works fine .

as part of my change i just need to change the connections,so that it will take file from SFTP server

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 10:10:58 GMT

Look at the code. It's nothing but an sftp transfer complicated by using SAS. It uses recfm=f to read blocks and write blocks to the outfile.

There might be an issue when the program reads the last "record" of the binary file and finds less than the default 254 bytes.

But since it ends in a segmentation violation, it's a clear case for SAS TS IMO.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 10:15:19 GMT

Thnaks @Kurt. So what does it mean "clear case for SAS TS IMO."Iam sorry and iam new to SFTP SAS connection as i never heard of SAS TS IMO.

So would you please suggest how can i overcome this error please

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 10:29:45 GMT

Go to support.sas.com, scroll down, and select Technical Support. On the next page, go to "3 Contact Technical Support", and on the next page, select Create a Track.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 10:35:07 GMT

Thanks @Kurt_Bremser

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 10:35:18 GMT

BTW, I think it would be much better to run the whole sftp from the commandline; once it works from there, you can use SAS to create the commandline call and run it with a filename pipe.

It is also advised that you set up automatic passwordless authentication to all sftp targets first by properly setting your entries in your .ssh directory. The aim is to get this working:

sftp sourceuser@sourcehost:sourcepath/sourcefile targetuser@targethost:targetpath/targetfile

without having to enter any further options.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 10:39:45 GMT

The connection was successful when i run as command line with same conection details on unix server.

So it was successfully connected and then i am trying now in SAS please.any suggestions please.

.ssh set was done @Kurt_Bremser and privatekey authentication was successfull on unix SAS server

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 10:55:05 GMT

I have to state that I do not use SAS FILENAME SFTP at all in our environment, as I have found it to be unreliable.

That is because SAS uses an external sftp client (with FILENAME FTP, the whole logic is built into SAS itself) to create a local copy, and reads that copy after a given delay. If the sftp is slow, SAS can "overtake" the sftp and stop reading prematurely.

So I always run the sftp as an external command:

filename oscmd pipe "sftp user@host:filename %sysfunc(pathname(work))/localname 2>&1";

data _null_;
infile oscmd;
input;
put _infile_;
run;

filename in "%sysfunc(pathname(work))/localname";

data want;
infile in;
/* further processing */

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 11:21:43 GMT

Thank you @Kurt_Bremser .

I was running the code you suggested and it ran for 1 minutes and i thought it was successfull.thanks @kurt

but iam getting below note now and not the old error

connect to host XXXX.com port 22: Connection timed out
Connection closed

where can i include the port no and option keyword in the sample code you provided please

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 11:41:21 GMT

I would use your .ssh/config file to store such host-specific options; find example documentation here: https://www.digitalocean.com/community/tutorials/how-to-configure-custom-connection-options-for-your-ssh-client

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Tue, 31 Mar 2020 11:49:37 GMT

Yes thanks @Kurt_Bremser .Actually i was trying to mention everything including options also in SAS code.

So what can i do please.

Do i need to raise SAS track that SAS SFTP option code is not working ?

you want me to setup the options under .ssh ?

Sorry how can i proceed further from now.

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Tue, 31 Mar 2020 11:58:50 GMT

As I said, a segmentation violation points to an internal problem in SAS, and that is the domain of SAS Technical Support.

As a workaround, I recommend running the command from the commandline, and once this works without entering a password, you can use the same (whole) command in a filename pipe. The 2>&1 I added redirects the stderr output to stdout, so that the data step can catch it and write it to the log.

For the sake of simplicity, I recommend to use the ssh config file for host-specific options.

Re: SFTP Private key authentication SAS Code throws ERROR

JJP1 — Wed, 01 Apr 2020 12:33:07 GMT

hi @Kurt_Bremser ,

found that remote server was making use of TLS 1.2 version for FTP connection.

but in my case SAS does not support TLS 1.2 version and only supports TLS 1.0 verison.

but same connection where SFTP with SSH keys works fine when i ran command via putty application.

but same command gets failed with segmentaion error in SAS EG,do you think it is due to TLS version please..

So If it is due to TLS version may i know why & how please.

Re: SFTP Private key authentication SAS Code throws ERROR

Kurt_Bremser — Wed, 01 Apr 2020 13:27:14 GMT

As I said, get it to run from the commandline, and then use the whole commandline command in a filename pipe from SAS; all the work is done outside SAS, and SAS only fetches the results.