Integrations and Extensions

Enterprise Resource Planning

Topic

    Jack Desai
    Inbound and Outbound Data File Encryption through ERP...
    Topic posted August 19, 2016 by Jack DesaiRed Ribbon: 250+ Points, tagged Cloud Integration, Export Bulk Data, Extensions, File Based Data Integration - FBDI, Financials, Fusion Applications Integration, Import Bulk Data, Integrated Cloud Services, PaaS - SaaS Extensions, Pre-built Integration, Procurement, Project Portfolio Management, REST Services, SaaS Integration, SOACS, SOAP Services, Supply Chain Management, Transaction Services 
    4870 Views, 18 Comments
    Title:
    Inbound and Outbound Data File Encryption through ERP Integration Service
    Summary:
    How to encrypt and decrypt data files between ERP Cloud and On-premise/PaaS
    Content:

    Introduction

    Since your inbound or outbound data files are transmitted over the internet and often times contain company sensitive information and financial transactions like journal entries, invoices, payments and bank records, data encryption is a critical and essential element in implementing your integrations with Oracle ERP Cloud. You can secure data files between Oracle ERP Cloud R11+ and your on-premise/PaaS applications or systems. This is supported through ERP integration service that supports 100+ interfaces across Financials, Project Portfolio Management, Procurement and Supply Chain Management.

    The following diagram illustrates the import integration flow (also known as File-Based Data Import - FBDI). Please refer this post for more details.

    The following diagram illustrates the export process (extracting data out from ERP Cloud). Please refer this post for more details.

    Oracle ERP Cloud supports Pretty Good Privacy (PGP) unsigned encryption with 1024 key size. There are two types of encryption keys:
    1.    Oracle ERP Cloud Key
    2.    Customer Key

    Oracle ERP Cloud PGP Key

    The public key is used by the customer to encrypt the data file and respective private key is used by import bulk data process to decrypt the data file before starting load and import process. The file stored in content server (UCM) remains encrypted. 

    Customer PGP Key

    ERP Cloud uses customer's public key to encrypt the extracted file and uploads to UCM. Customer uses their private key to decrypt the file in on-premise or PaaS systems. 
     

    Manage PGP Certificates

    Certificates establish keys for the encryption and decryption of data that Oracle Cloud applications exchange with other applications. The Oracle Fusion Applications Security Console is an easy-to-use administrative interface that you access by selecting Tools - Security Console on the home page or from the Navigator. Use the Certificates page in the Security Console functional area to manage PGP certificates.

    This is a Security Console Screen:

    Generate ERP Cloud PGP Certificate

    From the Certificates page, select the Generate option. In a Generate page, select the certificate format PGP, and enter values appropriate for the format.

    For a PGP certificate, these values include:

    • An alias (name) and passphrase to identify the certificate uniquely.
    • The algorithm by which keys are generated, DSA or RSA.
    • A key length – select 1024.

    Once the key is generated, customer must export the public key to encrypt the FBDI data file.

    Export Customer's PGP Public Key

    Follow these steps to export public key:

    1. From the Certificates page, select the menu available in the row for the certificate you want to export. Or open the details page for that certificate and select its Actions menu.
    2. In either menu, select Export, then Public Key
    3. Select a location for the export file. By default, this file is called [alias]_pub.asc

    Import Customer's PGP Public Key

    The customer public key will be used by ERP Cloud to encrypt outbound file. Customer will decrypt this file using their private key. Follow these steps to import customer's public key:

    1. On the Certificates page, select the Import button.
    2. In the Import page, select PGP and specify an alias (which need not match the alias of the file you are importing).
    3. Browse for the public-key file, and then select Import and Close.

    The Certificates page displays a record for the imported certificate, with the Private Key cell unchecked.

     

    Enabling Encryption in Import Process

    Please refer this post on automating bulk import process. This post will document additional information to encrypt the file only.

    When enabled, ERP Cloud will decrypt the inbound data file using cloud private key before starting load and import process. These are the following steps to enable encryption in your import process

    1. Encrypt Data (zip) File

    Encrypt inbound data (zip) file using Oracle ERP Cloud public key. Use "gpg" utility in Linux system to encrypt the file as follows:

    Import ERP Cloud public key (one-time configuration) using the following command

    gpg --import <MY_ERP_KEY_pub.asc>

    ###Verify the imported key using this command

    gpg --list-keys

    Once ­public key is imported, use the following command to encrypt your inbound data file:

     

    gpg --cipher-algo=AES -r=<alias> --encrypt <my_data_file>.zip

     

     

    The encrypted file will be renamed as <my_data_file>.zip.gpg.

    2. Add Encryption options in "importBulkData" Payload

    In importBulkData payload, provide the following job options

    Options

    Value

    FileEncryption PGPUNSIGNED

    FA_ALIAS

    ERP Cloud Key Alias Name

    CUSTOMER_ALIAS

    Customer Key Alias Name

     

    Example in your importBulkData request payload: <typ:jobOptions>FileEncryption=PGPUNSIGNED,FA_ALIAS=<ERP_CLOUD_KEY>,CUSTOMER_ALIAS=<CUSTOMER_KEY></typ:jobOptions>

    Note: Alias names are defined when you generate ERP Cloud key or import customer key.

    The following sample payload illustrates the Journal import process request payload:

       <soapenv:Body>
          <typ:importBulkData>
             <typ:document>
                <erp:Content>AAgACAMgAAAB5AQAAAAA=</erp:Content>
                <erp:FileName>journal_1234.zip</erp:FileName>
             </typ:document>
             <typ:jobDetails>
               <erp:JobName>/oracle/apps/ess/financials/generalLedger/programs/common,JournalImportLauncher</erp:JobName>
                <!--Optional:-->
                <erp:ParameterList>#NULL,#NULL,Balance Transfer,#NULL,1,jrd1,N,N,N</erp:ParameterList>
             </typ:jobDetails>
             <typ:notificationCode>10</typ:notificationCode>
             <typ:callbackURL>my_callbacl_endpoint_url</typ:callbackURL>
             <typ:jobOptions>FileEncryption=PGPUNSIGNED,FA_ALIAS=JACK_ERP_KEY,CUSTOMER_ALIAS=JACK_ERP_KEY</typ:jobOptions>
         </typ:importBulkData>
       </soapenv:Body>

     

     

    Enabling Encryption in Export Process

    Please refer this post on automating bulk export process. When enabled, ERP Cloud will encrypt extracted data file using customer’s public key and upload it to UCM. These are the following steps to enable encryption in import process

    1. Add Encryption options in "importBulkData" Payload

    In exportBulkData payload, provide the following job options

    Options

    Value

    FileEncryption PGPUNSIGNED

    FA_ALIAS

    ERP Cloud Key Alias Name

    CUSTOMER_ALIAS

    Customer Key Alias Name

     

    Example in your exportBulkData request payload: <typ:jobOptions>FileEncryption=PGPUNSIGNED,FA_ALIAS=<ERP_CLOUD_KEY>,CUSTOMER_ALIAS=<CUSTOMER_KEY></typ:jobOptions>

    Note: Alias names are defined when you generate ERP Cloud key or import customer key.

    The following sample payload illustrates the export process request payload:

      <soap:Body>
      <ns1:exportBulkData xmlns:ns1="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
                                <ns1:jobName>job_package_name,job_name</ns1:jobName>
        <ns1:parameterList>48,1001</ns1:parameterList>
        <ns1:jobOptions>FileEncryption=PGPUNSIGNED,FA_ALIAS=JACK_ERP_KEY,CUSTOMER_ALIAS=JACK_ERP_KEY</ns1:jobOptions>
        <ns1:callbackURL>30</ns1:callbackURL>
        <ns1:notificationCode>http://hostname:port/myCallbackService</ns1:notificationCode>

      </ns1:exportBulkData>
    </soap:Body>>

     

    2. Encrypt Data (zip) File

    Decrypt the output file using customer private key. To decrypt outbound data file:

    First you must import customer’s private key as follows:

    gpg --allow-secret-key-import --import <my_private.asc>

    ###Verify the imported key using this command

    gpg --list-keys

     

    Once customer’s private key is imported, use the following command to decrypt your outbound data file:

    gpg --decrypt <EncryptedFileName> > <DecryptedFileName>

     

    Conclusion

    This post provides detailed information on how to protect both inbound and outbound data file. This is in addition to SSL and Oracle Web Service Manager (OWSM) message protection policy over the internet.

    Comment

     

    • Musthafa Shaik

      Followed above steps but getting 'gpg: XXXX unusable public key' error while encrypting using command gpg --cipher-algo=AES -r=<alias> --encrypt <my_data_file>.zip.

      Please let me know if any other configurations needed for encrypting the zip file.

      • Francesco Arbasi

        Hi Musthafa,

        I'm having the same issue. Whenever I try to encrypt a file using the cloud's publick key I get an "encryption failed: Unusable public key" error. Did you solve this problem in the meantime?

        Thank you

        • Musthafa Shaik

          Hi Arbasi,

          Download the certificate with below specification from cloud ERP

          Alias: fusion-key

          Key Algorithm: RSA

          Key Length: 1024

          DSA Algorithm doesn't worked for me so we downloaded in RSA, it worked.

    • Naveen Mohankumar

      Hi Jack,

      We have implemented the custom BI report and triggered it using the exportBulkData operation, the extract output is produced correctly and uploaded into UCM how ever the file is not encrypted even though the public key name of the certificate was passed explicitly in the job options. Could you kindly clarify the following :

      1) Does exportBulkData operation with oracle/apps/ess/financials/commonModules/shared/common/outbound,FinOutboundProcess (Finance data extract job) support encryption of output files in R12 ?

      2) Does exportBulkData operation with a custom ESS Job on type BI triggering a Custom BI report support encryption of output files ?

      3) The output files produced using exportBulkData with oracle/apps/ess/financials/commonModules/shared/common/outbound,FinOutboundProcess (Finance data extract job) is producing the file in UCM with an extra space in it, is there a way to over come spaces in file names ? File name example that is produced : SuppliersReportBIPOnly56490_1494501403893 _BIPONLY.csv

      Regards

      Naveen

    • Usman Askofare

      Hi Jack,

      Thanks for the great document.

      This solution it seems will not replace the steps for AP Payment Transmission via SFTP,

      Which is a separate management of Public Keys between Oracle Cloud and Payment Manager Systems>

      Can you confirm that for us?

       

      Best Regards,

      Usman

      • Zakir Mir

        Hello Usman,

        Yes.. these PGP key files are managed separately from the PGP encryption/Signing key files in Transmission Configuration for Fusion Payments.
        Fusion Payments does not use Security Console to generate/maintain PGP key files. 

        Note: Fusion Expense does use the Security Console PGP key files for Encryption of Corp Card File.

        Thanks
        Zakir
         

    • Sunil Sapa

      Hi Jack,

      Is it an optional one or mandatory?
      If optional, can we use it for specific interface?

      Thanks,
      Sunil

    • Anubhav Rai

      Hello,

      I am trying to import AP invoices. using Gpg4Win to encrypt the zip file using Cloud's public key. After encryption, the file gets renamed to apinvoiceimport.zip.gpg

      After this, I convert the content into Base 64 format.

      Then I run the importBulkData operation of ErpIntegrationService with appropriate job option for file encryption.

      I receive a response from SOAP and 'Load Interface File for Import'  gets triggered. But it ends with below error:

      There were no valid data files found in the zip file . Only .dat , .csv , .xml , .txt , .ack  files are accepted for data files.

      Can you please guide me in resolving this issue.

      Is there anything that I am doing wrong?

      When I do the same process for a regular non-encrypted file, it works for me.

      Thanks,

      Anubhav

      • Monish Munot

        Anubhav, Can you download package from Fusion File Import and Export and check whether .csv files available with mentioned names?

        • Anubhav Rai

          Hi Monish,

          When I download the zip file from 'File Import and Export' and try to unzip it, I get the message that the file is invalid and is empty. However I see that the size of the file is 1KB, but I can not unzip/open it.

          Thanks for your response!

          Anubhav

          • Monish Munot

            In that case your file is not created properly and it is junk, because one should not get this kind of error if packaging is done properly. Please review file creation code again.

            Regards,
            Monish

            • Anubhav Rai

              I am using Gpg4Win tool to encrypt my zip file. Is there any other way we could encrypt our file?

              After encryption, the generated file's name is apinvoiceimport.zip.gpg and then I am converting this in Base 64 format. I hope this is the correct process.

              Have you done this process in past?

              Can you please share how you encrypted the file and steps that you followed?

              Thanks.

               

              • Monish Munot

                I have done the similar process, can you try following steps? Also you may share the zip file downloaded.

                1. Download the file and open in notepad++
                2. Select all content, do base64decode and save it as zip.
                3. Try to open the zip file.

    • Sankha Deb Barman

      Hi Experts

       

      I tried to use PGP encryption for FBDI import using PGP encryption in UCM server.

      FBDI fails saying .pgp is not supported format but we could outbound to the UCM server with PGP encryption.

       

      Please help for the missing setup during inbound.

       

      Regards

      Sankha

    • Sankha Deb Barman

      Hi Experts

       

      Please help , we have a customer who wants to sent data through PGP encryption and FBDI should be able to decrypt the same before load.

      Please help with the update.

      Regards

      Sankha

    • Swati Dang

      Hi Sankha,

      I was also facing the same issue. Decryption was failing for Data loader service. You need to perform the below steps:

      1. Zip the FBDI file

      2. Encrypt the Zipped file using Fusion Public key

      3. Rename the encrypted file to .zip (because data loader program accepts filename ending with .zip ) (Test.zip.pgp -> Test.zip)

      4. Now decryption will work fine.

      Regards,

      Swati Dang

       

       

       

    • Vetrivel J

      Hello Guys,

      I have sucessfully encrypt the PGP File with UNSIGNED using the following Option in the Import Bulk data operation :

      <ns1:jobOptions>FileEncryption=PGPUNSIGNED,FA_ALIAS=JACK_ERP_KEY,CUSTOMER_ALIAS=JACK_ERP_KEY</ns1:jobOptions>

      but reqruire to do PGP File encryption with SIGNED but it was not working with the below option ,eventhough my Encrypted file was Signed :

      <ns1:jobOptions>FileEncryption=PGPSIGNED,FA_ALIAS=JACK_ERP_KEY,CUSTOMER_ALIAS=JACK_ERP_KEY</ns1:jobOptions>

       

      Is there anything i were missing??

       

      Thanks in advance..