topic How to use Private Key in Filename SFTP in SAS Programming

How to use Private Key in Filename SFTP

EEEY — Mon, 08 Apr 2019 19:44:25 GMT

Hi,

I am trying to transfer a file from my SAS server (SAS 9.4 on Unix) to a Linux(Hadoop) environment. We were able to test to connect and file transfer through WinSCP. But I needed to add my Private Key in order to connect to the Linux server when using WinSCP. I am wondering how to do this in the Filename SFTP syntax in SAS.

I referenced another question in this community but it still did not work.

Please help.

My program:

filename attn '/apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv';

filename outfile sftp '/apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv' CD ='/hrtoaxion'
options="-p = 1000 -i = /apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/private_key_a135929.ppk"
host="host_name" user="username" lrecl=800 DEBUG ;

data _null_;
infile attn lrecl=800;
file outfile lrecl=800 ;
input;
put _infile_;
run;

The error log as bellows:

NOTE: The infile ATTN is:
Filename=/apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv,
Owner Name=a135929,Group Name=nawim,
Access Permission=-rw-r--r--,
Last Modified=29Mar2019:17:02:48,
File Size (bytes)=787

NOTE: sftp: illegal option -- pusage: 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 /hrtoaxion
ERROR: Directory or file /apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv doesn't exist.
NOTE: 0 records were read from the infile ATTN.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 1.51 seconds
cpu time 0.01 seconds

Re: How to use Private Key in Filename SFTP

ChrisNZ — Mon, 08 Apr 2019 22:41:57 GMT

From what I see, you use options -p and -i which are not supported.

Re: How to use Private Key in Filename SFTP

EEEY — Mon, 08 Apr 2019 22:45:31 GMT

thanks ChrisNZ for your response. But I saw another thread using this option as a solution. How to check what options can i use in SAS, specifically using private key? The SAS document did not have that details.

Thanks a lot!

Re: How to use Private Key in Filename SFTP

Tom — Mon, 08 Apr 2019 23:24:46 GMT

Try using the -o option.

-oIdentityFile=filename

Re: How to use Private Key in Filename SFTP

ChrisNZ — Mon, 08 Apr 2019 23:26:42 GMT

1. Can you send files outside of SAS?

What syntax and options do you use to connect outside of SAS?

Can you use the same options in SAS?

2. Have you registered the keys?

Something like this: https://www.jscape.com/blog/setting-up-sftp-public-key-authentication-command-line

Re: How to use Private Key in Filename SFTP

EEEY — Mon, 08 Apr 2019 23:34:24 GMT

am I supposed to this below?

options="-oIdentityFile = /apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/private_key_a135929.ppk"

I got the same error.

Re: How to use Private Key in Filename SFTP

EEEY — Mon, 08 Apr 2019 23:41:42 GMT

Please see my response in Italic below.

1. Can you send files outside of SAS?

What syntax and options do you use to connect outside of SAS?

Can you use the same options in SAS?

- Yes. I used WinSCP to test and was able to connect and send the file. But I create the file in SAS and need to automate the process including the file transfer.

2. Have you registered the keys?

Something like this: https://www.jscape.com/blog/setting-up-sftp-public-key-authentication-command-line

- We did the key pair and they added my public key and my private key. One thing I want to point out (but not sure if it caused the failure because of this) is that the target server does not support SSH connect. So I could only test the connect in WinSCP but not in Putty.

Re: How to use Private Key in Filename SFTP

Tom — Mon, 08 Apr 2019 23:44:04 GMT

I don't think it will like the spaces.

Try it just using normal ssh or sftp command from command prompt on a regular machine to get the syntax right.

Re: How to use Private Key in Filename SFTP

EEEY — Tue, 09 Apr 2019 00:07:20 GMT

I removed the space in the Options. the log seems a little different than last time, saying my private key are too open.It is recommended that your private
key files are NOT accessible by others.This private key will be ignored.bad permissions: ignore key:
/apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/private_key_a135929.ppkssh_askpass: exec(/opt/quest/libexec/ssh-askpass): No
2 The SAS System

such file or directoryPermission denied, please try again.ssh_askpass: exec(/opt/quest/libexec/ssh-askpass): No such file
or directoryPermission denied, please try again.ssh_askpass: exec(/opt/quest/libexec/ssh-askpass): No such file or
directoryPermission denied (publickey,gssapi-keyex,gssapi-with-mic,password).Connection closed
NOTE: cd /hrtoaxion
ERROR: Directory or file /apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv doesn't exist.
NOTE: 0 records were read from the infile ATTN.
NOTE: The SAS System stopped processing this step because of errors.

I don't know what I should do about it.

Regarding your 2nd recommendation -

Since they don't support SSH, I can't really use command in Putty to connect to the target sever and test. Argh..

Re: How to use Private Key in Filename SFTP

Tom — Tue, 09 Apr 2019 00:52:18 GMT

You need change the permissions on the file with your credentials.
Can you run commands?:
X "chmod 600 filename";
Otherwise there was a question just the other data about the PERMISSION option on FILENAME/FILE statement.

Re: How to use Private Key in Filename SFTP

EEEY — Tue, 09 Apr 2019 18:48:33 GMT

Thanks Tom. I tried the X command but it did not really do anything. still got the same error about :

ERROR: Directory or file /apps/sas/datasets/data30/NAWIM/Elaine/WO/EHS/Reports/test.csv doesn't exist.

I am sure that the file and the path of original file is absolutely correct.

One question - do I need to ask my SAS admin to authorize the target server's public key?

Re: How to use Private Key in Filename SFTP

Tom — Tue, 09 Apr 2019 19:10:53 GMT

Hard to tell without seeing the full SAS LOG to see what code you submitted and what error is reported, but it looks like you are now connecting but the file you asked for was not found.

Re: How to use Private Key in Filename SFTP

EEEY — Thu, 11 Apr 2019 18:04:41 GMT

Thank you so much everyone for your reply on my questions. I was able to find the final solution.

Basically the public key I provided should be the public key on the SAS server /.ssh/ir_rsa.pub file. Instead my IT ppl guided me to generate a public/private key in Windows. If they did the key pair correctly, I did not need to provide my private key to the target server and specify in my SAS program. The other issue we had was that the Port number at the target server is not standard one so I need to specify in my program. The syntax i finally use is like below. Hope someone in this community could be benefited from this:

filename attn 'path of source file/test.csv';

filename outfile sftp 'path of source file//test.csv' cd ='directory'
options="-oPort=1784" host="host_name" user="user_name" lrecl=800 DEBUG ;

/**in case in need of private key - -options="oIdentityFile=location of private key on SAS server/.ssh/id_rsa -oPort=1784*/

data _null_;
infile attn lrecl=800;
file outfile lrecl=800 ;
input;
put _infile_;
run;

filename attn clear;
filename outfile clear;

Re: How to use Private Key in Filename SFTP

ChrisNZ — Thu, 11 Apr 2019 22:00:17 GMT

> Thank you so much everyone for your reply on my questions. I was able to find the final solution.

Thanks for posting an update and a solution.