SAP Commissions – Smart Data Integration[SDI] – Part 7a

This article is intended for database admins, consultants, customers & partners to enable the File Adapter using PGP/GPG encryption & configure encrypted sample data from your local file path to load into your SDI Project

In this blog, PGP tool is installed, we will be encrypting the source file provided by customer (extract & dump regularly) in the local path where DP Agent is installed and public & secret key is saved for decrypting the files.
Few customers may not connect to their local database (source) due to privacy & security.

PGP VERSION
You must use a version older than GnuPGP 2.3 or a version compatible with -rfc4880.

Download & Install PGP Tool

For example if using Kleopatra you can use the version 3.3.16 or older: (this is specifically tested with 3.1.15 from https://gpg4win.org/change-history.html)

Version which works

After you installed, Create your Key

Export your Public Key & Private Key into your local path and keep it safe. Ensure your Certificate Key expiry date is still valid. ( Do not share it to anyone)

The PGP Secret Key Path represents the PGP Private key. This is used for decrypting the file. The Third Party Public Key Path represents the PGP Public Key. This is used to read the PGP Signature. Prior to DP Agent version 2.6.3 all PGP encrypted files needed to be signed and therefore the Third Party Public Key Path parameter must be maintained in the remote source and the public key present in the FileAdapter local store.

Prepare a file for upload

Download this sample file into the default workspace.

The default workspace is located in <<ROOT DIRECTORY>>workspace, for example, usrsapdpaworkspace

Select the file which you need to encrypt

Review the file which you need to sign/encrypt

Enter the Passphrase which you set as per your certificate signature.

File is encrypted successfully and you can see your encrypted file in your path.

The datafiles being sent to the DP Agent needs to end with .gpg. File with the extension .pgp are not supported

This is how it should look like in your directory for file to process..

Create a text file called salarydata.cfg with the following content:

#Configuration file for data load
CODEPAGE=UTF-8
ERROR_ON_COLUMNCOUNT=false
ESCAPE_CHAR=
EXPONENTIAL=E
FORCE_DIRECTORY_PATTERN=C:usrsapdataprovagentDatafiles
FORCE_FILENAME_PATTERN=salarydata.csv
FORMAT=CSV
LENIENT=true
LOCALE=en_US
ROW_DELIMITER=n
SKIP_HEADER_LINES=1
COLUMN=id;INTEGER;
COLUMN=salary;INTEGER;
COLUMN=start_year;INTEGER;
COLUMN=gender;NVARCHAR(256);
COLUMN=region;NVARCHAR(256);
COLUMN=T-Level;NVARCHAR(256);

Save the configuration file in the same directory.

Create a Remote Source

Go back to the Database Explorer. You will see the adapter under Catalog -> Remote Source

Right-click on Remote Sources. Choose Add Remote Source

Here you can define the Source Name (arbitary), the Adapter will be the FileAdapter

Provide the location of the PGP keys. You get asked for their location when registering the FileAdapter preferences and in the remote source.

Scroll down to the credentials and choose Technical User as the credentials mode and enter the access token in the AccessToken field. If you used the example token before, enter FileToken. (Refer Part 7 how to generate fileAdapter token)

Also you need to provide PGP Passpharse which you set while installing your PGP.. this will enable to decrypt the file while proceessing

Click Create

Check the remote objects to make sure configuration has been successful and to complete the validation below.

Also you can CREATE REMOTE SOURCE using below statement in your webIDE

CREATE REMOTE SOURCE "SDI_FileAdapter_encryption" ADAPTER "FileAdapter" AT LOCATION AGENT "Localfiledump2process"
CONFIGURATION
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ConnectionProperties>
	<PropertyEntry name="rootdir">C:usrsapdataprovagentDatafiles</PropertyEntry>
	<PropertyEntry name="fileformatdir">C:usrsapdataprovagentDatafiles</PropertyEntry>
	<PropertyEntry name="usecdc">true</PropertyEntry>
	<PropertyEntry name="source_options">local</PropertyEntry>
	<PropertyEntry name="target_options">local</PropertyEntry>
    <PropertyGroup name="fileformatconfiguration">
        <PropertyEntry name="format">flatfiles</PropertyEntry>
        <PropertyEntry name="errorHandling">false</PropertyEntry>
    </PropertyGroup>
    <PropertyGroup name="errorHandlingConfiguration">
        <PropertyEntry name="logDataConversionWarnings">true</PropertyEntry>
        <PropertyEntry name="maximumWarningsToLog"></PropertyEntry>
        <PropertyEntry name="captureDataConversionErrors">false</PropertyEntry>
        <PropertyEntry name="captureRowFormatErrors">true</PropertyEntry>
        <PropertyEntry name="captureStringTruncationErrors">false</PropertyEntry>
        <PropertyEntry name="maximumErrosToStopJob"></PropertyEntry>
        <PropertyEntry name="writeErrorRowsToFile">false</PropertyEntry>
        <PropertyEntry name="errorFileRootDirectory"></PropertyEntry>
        <PropertyEntry name="errorFileName"></PropertyEntry>
    </PropertyGroup>
    <PropertyGroup name="additionalConfiguration">
        <PropertyEntry name="autodetect">true</PropertyEntry>
        <PropertyEntry name="overwriteConfigByCFG">true</PropertyEntry>
    </PropertyGroup>
	<PropertyEntry name="use_decryption">pgp</PropertyEntry>
	<PropertyGroup name="Decryption">
		<PropertyEntry name="pgp_secret_key_path">C:usrsapdataprovagentgpg_pgp_keysSECRETkey.asc</PropertyEntry>
		<PropertyEntry name="thirdparty_public_key_path">C:usrsapdataprovagentgpg_pgp_keyspublickey.asc</PropertyEntry>
	</PropertyGroup>
</ConnectionProperties>
' WITH CREDENTIAL TYPE 'PASSWORD' USING
 '<CredentialEntry name="AccessTokenEntry">
	<password>XXXXXXXXXXXX</password>
 </CredentialEntry>'
 '<CredentialEntry name="PGPSecretKeyCredential">
   <password>XXXXXXXXXXXX</password>
 </CredentialEntry>';

Create a Virtual Table

you will have to create a Virtual table by connecting to your remote source to load the data into table.

create virtual table ext.VT_encryption_file_yoga_pgptool AT "SDI_FileAdapter_encryption_pgptool"."<NULL>"."<NULL>"."salarydata";

select count(*) from ext.VT_encryption_file_yoga_pgptool;

select * from ext.VT_encryption_file_yoga_pgptool;

you can preview your data if that’s coming from local file.. File will be decrypted and you can see csv file shown in your file path

The FileAdapter is merely coded to look for the extension “.gpg” to identify files that need to be decrypted. Note that great care needs to be taken when mixing both encrypted and unencrypted files in the same folder, from the same remote source. The FileAdapter only decrypts those with .gpg extension but a file cannot exist with the same name as the decrypted file.