SFTP Batch Mode - Providing Password using EXPECT command

Dear All

Many may have come across requirement to connect to a remote server and SFTP a file from there to your Oracle server for further processing

We can do this in 2 ways as below

-----------------------------------------------------------------------------

Traditional Approach : Using Private-Public Key Pair setups

Steps to perform on Oracle server
1) Login to the app tier of the Oracle box as the applmgr (or user which is used to run HOST programs) user
2) In the home directory of the applmgr user, check to see if there is a directory .ssh (you need to use ls -al to see hidden directories)
3) If not, then create one and grant 755 permission to it
4) Execute the command ssh-keygen -t rsa
5) This will ask you a series of inputs, so just keep hitting enter until back to the prompt (do not provide any passphrase etc.)
6) In the .ssh directory you will now see 2 files id_rsa (your private key) and the id_rsa.pub (your public key)

Steps to perform on Remote server
1) Provide the id_rsa.pub file which was generated above to the remote server admin
2) Ask them to login using the user which will be used for actually performing the SFTP operation
3) Go to the home directory and check to see if a .ssh directory exists
4) If not, create it with 755 permissions
5) Inside the .ssh directory create a file called authorized_keys and provide it with 700 permissions
6) Copy the contents of the id_rsa.pub into the file (if already existing then append to it)

Now, back on the Oracle server try to do sftp username@hostname ... do this from the applmgr user and it should not prompt you for a password and hence batch SFTP connection is established

-----------------------------------------------------------------------------

Alternate Approach: Using EXPECT command to provide password hence not needing key files

The below command can be used as a base to write your script. The text in blue are all parameters for the user name, server, password etc

expect<<EOD
spawn /usr/bin/sftp $SFTP_USERNAME@$REMOTE_SERVER
expect "Enter password:"
send "$SFTP_PASSWORD\r"
expect "sftp>"
send "cd /out\r"
expect "sftp>"
send "get $FILE_NAME_PATTERN\r"
expect "sftp>"
send "quit\r"
EOD

In this approach you need not copy the public key to the remote server to enable batch mode SFTP

-----------------------------------------------------------------------------

Hope this helps you all sometime

Cheers

A