How to Use an SSH File Transfer Protocol (SFTP) Connection in Oracle BI Publisher
Oracle BI Publisher provides an option to use an SSH File Transfer Protocol (SFTP) connection to deliver reports to an external location. This external location should have an SFTP server installed. In this blog, we are going to assume BI Publisher is part of an Oracle Financials Cloud environment. The figure below represents a working setup where a SFTP server, like OpenSSH, is installed on an AWS Server (Windows Server 2016). BI Publisher has a SFTP connection created, which is used to setup Report Jobs on a schedule.
Figure 1: Visual on Representing a Working Setup where a SFTP Server is installed on an AWS Server
As mentioned earlier, we are using OpenSSH as a SFTP server for this setup. Below are some high level steps for installing OpenSSH using PowerShell.
After downloading OpenSSH, run the two commands below in a PowerShell command window:
Command One: powershell.exe -ExecutionPolicy Bypass -File install-sshd.ps1
Figure 2: Picture demonstrating running Command One
Command Two: New-NetFirewallRule -Name sshd -DisplayName ‘OpenSSH SSH Server’ -Enabled True -Direction Inbound -Protocol TCP -Action Allow -LocalPort 22
Figure 3: Picture demonstrating running Command Two
With an Update 19B for Fusion Applications, Oracle implemented an enhancement to the SFTP delivery option. Now, SFTP connections to both standard and non-standard ports will be allowed via the proxy server. You can find the details in this Oracle document: Doc ID 2538902.1 (Note: You need an Oracle.com account to access this documentation).
With this update, the Oracle proxy server IP address needs to be added to the SFTP server white list. These IP addresses will correspond to your data center, and this information can be found in the Oracle documentation referenced above.
In addition, you also may have need to make an edit to the “sshd_config” file under “C:/ProgramData/ssh” to allow a specific Active Directory user. Basically, the “Match Group administrators” property was disabled and “AllowUsers” was enabled for the specific user.
Figure 4: How to make update the “sshd_config” file
The step shown in Figure 4 is done to update the “sshd_config” file. This might be completely optional based on whether or not the user is able to establish the SFTP connection from BI Publisher to SFTP server.
Once the SFTP server setup has been completed, the next step is to add a SFTP server connection from BI Publisher administration. This process has been well-documented in this Oracle documentation: https://docs.oracle.com/middleware/12211/bip/BIPAD/GUID-52398FBA-CB92-4A8D-9187-770A98C657E4.htm#BIPAD306.
Next, from the “Analytics” home screen of your Fusion Cloud environment, select “Report Job” option to schedule a new job using the SFTP server connection you just added.
Figure 5: Where to find “Report Job” in your Fusion Cloud environment
You can refer to this Oracle document for detailed instructions on creating report jobs: https://docs.oracle.com/middleware/12213/bip/BIPUG/GUID-22F94743-8AB7-43E8-AEE8-3D022AB306B2.htm#BIPUG188
Want to know more about this topic? Contact us here and let us what other information you might be looking for!