Oracle Fusion BI Publisher – SFTP and PGP Configuration

Fusion Applications BI Publisher : How to request configuration of Public/Private key encryption for delivery from BI Publisher to external FTP Servers ( Doc ID 1987283.1 )

GOAL

 This document details how to setup the Public/Private key encryption for an external (to the Oracle Cloud) FTP Server in a Fusion Applications BI Publisher installation.

SOLUTION

Setting up the PGP key and using it in the FTP channel is a 2 step process:

A.  A new link named “PGP Keys” has been introduced under the “Security Center” in the “Administration” page:

1) Login to Fusion Application using a BI Administrator user.

2) From the Navigator, click on “Reports And Analytics”.

3) Click on the Book icon to go to /analytics page

4) Click on the Administration link

5) Click on Manage BI Publisher

6) Click on PGP Keys link

6) Click on PGP Keys link, It opens a PGP Keys page as shown in screen shot below.

1. User uploads public key by clicking on upload after selecting the key using Browse button. Upon upload, key is imported to keystore, if the key with exactly same id is imported to PGP keystore, it will be overwritten. Details of imported key will be shown in the PGP keys table
2. User can download the Encrypted Test Output for the all the keys imported. User can check upload of key done properly or not by decrypting the downloaded test output file in user’s own environment where by the secret key of the imported public key exists
3. Use can delete the imported public key by click on the delete Icon.
4. BI Publisher public key can be downloaded by clicking download icon in BI Publisher public key section.

B. How to setup the FTP channel using PGP Keys:

Create a FTP Channel by providing the required details and select the PGP Key from the drop down in the PGP Encryption section.

1) Filter command will disabled for cloud environment

2) Filter command will be updated automatically after selecting the PGP Key from the dropdown down in the PGP Encryption section

3) Filter command will be updated automatically after selecting “Sign Output” using the checkbox in the PGP Encryption section. “–s” will be added to existing filter command.

4) In edit mode of ftp server page , existing filter will be shown

Frequently Asked Questions

1) After decryption, why is the file name decrypted to <random_name>.tmp?

       Background
         a) Upload PGP Key as above  
         b) Schedule BI Publisher Job to deliver output to FTP Server (File_Name.txt)
         c) Use 3rd party decryption software to decrpt the file name to its original name
         d) The File Name after decryption is (random_name.tmp) and not (File_Name.txt)

     Solution
         We apply encryption to a temporary file that has name like <random_name>.tmp so the name can be stored in the encrypted gpg file. It is how the system works by design.
         It is 3rd party decryption software that decrypts the file. If it tries to use original file name when decrypting then the name is <random_name>.tmp.
         Customer can check with 3rd party decryption software to handle the file name, OR they can just easily rename the output file from <random_name>.tmp to <File_Name>.txt

2) What is difference between the “Encrypted Test Output” (downloaded from above steps) and the “BIP Encrypted Output” (Output sent by BI Publisher to FTP)

      A) What is the difference between the two?
        Ans)  “Test output” is just to test and encrypted output is encrypted output from BIP that is real report data.
      B) Is there any difference on how they are encrypted?
        Ans) Same command is used but test output is simply encrypting a ready-made fixed small text file for testing purpose and encrypted output is encrypting real report data which is dynamically generated.
      C) How come the “Encrypted Test Output” returns with a filename and then “BIP Encrypted output” returns random filename.
         Ans) We use dynamically generated temporary file for real report processing but test output is always using a fixed file as there is no need to dynamically change content each time for testing.

3) Download the bipublisher@oracle.com.key. Open it and the content in it cannot be seen i.e its not in readable format. Customer need it in readable format to verify signature.

Ans) If the size is non-zero and the file being binary and not human readable, it is designed to be a binary file. It is a file generated by Unix gpg. If the customer’s decryption software does not support the file, unfortunately it cannot be used. We recommend to use Unix gpg on customer’s end too if customer is having trouble importing the public key generated by gpg.

4) What is the size of bipublisher@oracle.com.key is zero size?

Ans) Cloud Customers, please log a Service Request with Oracle Support to debug this issue.

 5) Renewal of PGP Key fails with an error / Upload duplicate PGP Key fails with an error

Ans) This is discussed in the below doc:

                   Fusion Applications – BI Publisher – PGP – Public key XXXXXXX could not be signed: exit value = 2 (Doc ID 2277458.1)

6) Is there any limit on the number of PGP keys and/or sftp connections that use the encryption key?

Ans) PGP key – SFTP server relationship is one to many. You can import one PGP public key to BIP, then you can use it with unlimited number of SFTP servers.

On the other hand, one SFTP server can use only one PGP public key to encrypt the content. If you want to use multiple keys with single SFTP target server, set up as many SFTP server definitions on BIP as you want to point to the same SFTP target server and set up each with different PGP key