Connecting to SFTP server from PL/SQL

Table of Contents

Connecting

The first thing to do when initialing SFTP communication session is connecting to the remote machine. Connecting is done with ORA_SFTP.CONNECT_HOST.

The CONNECT_HOST method returns a connection identifier (PL/SQL datatype NUMBER) which is required by all the other functions of the package.

Connecting with username and password

In the most simple scenario, a connection is authenticated with a username and password, we shall know in advance those details and use them when invoking the CONNECT_HOST method:

1
2
3
4
5
6
7
8
9
10
11

DECLARE 
 connection_id NUMBER;
 remote_ssh_port NUMBER := 22;
 username VARCHAR2(200);
 password VARCHAR2(200); 
BEGIN
 username:= 'myuser'; 
 password:= 'mypass';
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', remote_ssh_port, username, password);END;
/

Connecting with a username and SSH private key.

The more secure connection option is when the connection is signed with a private key and a corresponding public key is already installed on the SFTP server instance.

The SSH private key must be located in a BLOB field.  A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY.sql script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE 
 connection_id NUMBER; 
 private_key BLOB; 
 private_key_handle BFILE;
 PRIVATE_KEY_PASSWORD VARCHAR2(500); 
BEGIN
 -- load the key data into private_key 
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( private_key, private_key_handle,  DBMS_LOB.GETLENGTH(private_key_handle) );
 
 PRIVATE_KEY_PASSWORD := 'changeit';
 
 connection_id := ORA_SFTP.CONNECT_HOST('sftp host name or IP', 22, 'my sftp user name', private_key, private_key_password); -- Do SFTP stuff here ...
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Connecting with both password and SSH private key.

Although not very common it is possible for an SFTP server to be configured to require both username/password and private key for authentication. In order to establish a connection, we must use a dedicated method CONNECT_WITH_KEY_AND_PASSWORD

A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY_AND_PASSWORD.sql script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE 
 connection_id NUMBER; 
 private_key BLOB; 
 private_key_handle BFILE;
 private_key_password VARCHAR2(500); 
BEGIN
 -- load the key data into private_key 
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( private_key, private_key_handle,  DBMS_LOB.GETLENGTH(private_key_handle) );
 
 private_key_password := 'changeit';
 
 connection_id := ORA_SFTP.CONNECT_WITH_KEY_AND_PASSWORD('host', 22, 'user', 'password', private_key, private_key_password); -- Do SFTP stuff here ...
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

HTTP Proxy support

The method used for connecting through HTTP proxy is ORA_SFTP.CONNECT_HOST_PROXY. It expects the same key or password parameters as the CONNECT_HOST method and an additional proxy parameter of type ORA_SFTP_HTTP_PROXY.

Below is an example code block where the ORA_SFTP_HTTP_PROXY structure is initialized. We have to set Host, IP address, username/password, and additional HTTP headers for the HTTP proxy server connection, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

DECLARE 
  connection_id NUMBER;
  proxy ORA_SFTP_HTTP_PROXY;
 
  private_key_handle BFILE;
  private_key BLOB;  
  private_key_password VARCHAR2(500);  
BEGIN    ... -- load key
 
    -- OK, this is just a test, we run a local HTTP Proxy on port 8000 and our test SFTP server is on port 22
    -- in a real world scenario your Proxy and SFTP servers probably won't be on the same machine
    proxy := ORA_SFTP_HTTP_PROXY(Address => '127.0.0.1', Port => 8000, Username => NULL, Password => NULL, AdditionalHeaders => NULL);
 
    connection_id := ORA_SFTP.CONNECT_HOST_PROXY('localhost', 22, 'nasko', private_key, '', proxy => proxy);
    ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

The Username and Password fields of ORA_SFTP_HTTP_PROXY must be filled if the HTTP Proxy requires authentication.
The AdditionalHeaders field can hold HTTP headers needed by the HTTP proxy. We can pass several header lines separated with “|” like

AdditionalHeaders => 'Accept-Language: en-US|From: johndoe@acm.com'

A complete example is available in \Examples\CONNECT_WITH_SSH_KEY_PROXY.sql file

Connection timeout

In network communications, a timeout is a maximum period that we are eager to wait for an action to complete.

ORA_SFTP accepts two last parameters in the CONNECT_HOST method: connection_timeout and read_timeout, of type PLS_INTEGER and representing the timeout periods in milliseconds (1000 milliseconds = 1 second). By default, both have a value of 0 (zero) which means wait infinitely. There are situations when we may prefer the operations to fail (with SFTP exception) after a certain amount of time. In that case, we have to provide initial values when connecting to a remote host.

The example below will wait for 0.5 (half) second for connection and a maximum of 1 (one) second for an operation to complete.

1
2
3
4
5
6
7
8
9
10

DECLARE 
 connection_id NUMBER;
 connection_timeout PLS_INTEGER;
 read_timeout PLS_INTEGER;
BEGIN
 connection_timeout:= 500; -- 0.5 seconds 
 read_timeout:= 1000; -- 1 second
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 'myusername', 'mypassword', connection_timeout, read_timeout);END;
/

SSH Keys

When using the more secure way of identifying with an SSH key, the public SSH key must be installed (e.g. copied) on the remote SSH/SFTP server machine and associated with the username that we are going to use for the connection. When we start the connection we use our Private SSH key to sign the initial SSH handshake and thus authenticate to the remote machine.

SSH keys can be met in various formats, the most common being OpenSSH, PuTTY, and ssh.com format. OraSFTP recognizes them automatically and uses them transparently. The keys must be put into a BLOB field and then passed to the package.

You can create SSH key pair (public and private key) with the free utility PuttyGen.

The example below illustrates how to load an SSH private key test_putty_private.ppk from a local machine folder into a BLOB filed, which afterward can be passed to ORA_SFTP.CONNECT_HOST.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DECLARE 
 private_key_handle BFILE;
 private_key BLOB; -- the SSH key will be loaded here
BEGIN
 -- initialize the key storage
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 
 -- the direcory name must be created with CREATE DIRECTORY MY_KEYS_DIR AS '/my_folder_with_ssh_keys';
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 
 -- load the data into a BLOB
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( DEST_LOB => private_key,                         SRC_LOB => private_key_handle, 
                        AMOUNT => DBMS_LOB.GETLENGTH(private_key_handle) );
 DBMS_LOB.CLOSE(private_key_handle);
END;

Summary

This chapter is an introduction to how to connect to an SFTP server with PL/SQL from inside the Oracle(r) Database version 11 and above.

The next step that you may want to check is how to disconnect an already established connection.