BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
samgautam007
Fluorite | Level 6

Hello Everyone,

 

I am stuck at a point where I need to transfer CSV files generated from sas to db server location. Below are the details:

 

I am using Sas 9.4 and below process will be run on batch via control M.

 

SAS server: 10.20.30.40

Csv file location: /sasdata/test.csv

 

Db server: 10.20.30.50

Csv transfer location: /test1/test2/test.csv

 

I have tried "filename outfile sftp" but stuck at primary key think (no knowledge about this).

 

Below is the code that I tried:

 

Filename outfile sftp 'test.csv' cd='/test1/test2/'

Host='10.20.30.50' options="-oport=22" user="username" debug;

 

Proc export data=test outfile=outfile dbms=csv replace ;run;

 

Error: host key verification failed. Couldn't read packet: connection reset by peer.

 

Please provide your valuable suggestions.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

In order for filename sftp to work, you need to set up passwordless authentication and initialize the known_hosts entry.

On your SAS server, log on with your SAS user.

Use ssh-keygen to create a public/private key pair. This will create files in .ssh:

id_dsa
id_dsa.pub
id_rsa
id_rsa.pub

(rsa or dsa depending on which method you chose)

Change to directory .ssh

Enter

sftp username@10.20.30.50

You will be prompted to accept a host verification key. After doing that, 10.20.30.50 will be in your .ssh/known_hosts file with this key. In the future, ssh will check if the target host is still the same, to prevent a MITM attack.

After logging on (here you still need the password for username on 10.20.30.50), change to .ssh

use the put subcommand to copy your .pub file to the server, change the target name, so you do not accidentally overwrite a file already there.

log off, and log on with ssh

change to .ssh

Now, either rename your copied file to

authorized_keys

or append its contents to the file, if it already exists:

cat authorized_keys your_file > authorized_keys.new
rm authorized_keys
mv authorized_keys.new authorized_keys

While doing this, make sure that all files and the .ssh directory are read/writable only by the user

Log off and try ssh username@10.20.30.50 again. You should log on without being prompted for a password.

 

Now you can use filename sftp on that server from SAS.

 

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

In order for filename sftp to work, you need to set up passwordless authentication and initialize the known_hosts entry.

On your SAS server, log on with your SAS user.

Use ssh-keygen to create a public/private key pair. This will create files in .ssh:

id_dsa
id_dsa.pub
id_rsa
id_rsa.pub

(rsa or dsa depending on which method you chose)

Change to directory .ssh

Enter

sftp username@10.20.30.50

You will be prompted to accept a host verification key. After doing that, 10.20.30.50 will be in your .ssh/known_hosts file with this key. In the future, ssh will check if the target host is still the same, to prevent a MITM attack.

After logging on (here you still need the password for username on 10.20.30.50), change to .ssh

use the put subcommand to copy your .pub file to the server, change the target name, so you do not accidentally overwrite a file already there.

log off, and log on with ssh

change to .ssh

Now, either rename your copied file to

authorized_keys

or append its contents to the file, if it already exists:

cat authorized_keys your_file > authorized_keys.new
rm authorized_keys
mv authorized_keys.new authorized_keys

While doing this, make sure that all files and the .ssh directory are read/writable only by the user

Log off and try ssh username@10.20.30.50 again. You should log on without being prompted for a password.

 

Now you can use filename sftp on that server from SAS.

 

samgautam007
Fluorite | Level 6

Thank so much 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1117 views
  • 1 like
  • 2 in conversation