SAP Commissions – Smart Data Integration[SDI] – Part 7b
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.
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,
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:\usr\sap\dataprovagent\Datafiles 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
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:\usr\sap\dataprovagent\Datafiles</PropertyEntry> <PropertyEntry name="fileformatdir">C:\usr\sap\dataprovagent\Datafiles</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:\usr\sap\dataprovagent\gpg_pgp_keys\SECRETkey.asc</PropertyEntry> <PropertyEntry name="thirdparty_public_key_path">C:\usr\sap\dataprovagent\gpg_pgp_keys\publickey.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.