How to Automate PGP Decryption with SSIS

Recently, I have come across an integration project that require source file to be encrypted/decrypted with PGP when it is pass over from one end to the other for integration. Well,  this is something new to me. After spending some times Google around and trial and error with numerous approach, finally I have manage to work out a solution that meet my project requirement. I am sharing this little guide hope it is helpful to some of you out there that could facing the same business requirement as me.

As what I have did for several integration projects earlier, I am using SQL Server Integration Services (SSIS) to automate the integration process. Now the challenging part is automate the decryption process with PGP that is encrypted by the vendor that generating the output file.

In the process of looking for answer on internet, i realized there are some third party component that is ready for plug-in as part of the component/tools in SSIS. I was so happy initially that i though i have found the solution. While read into detail then only i found out this come with a cost that i am not affordable (quite expensive in fact). How i wish these are open source component that i can just plug-and-play. I carried on the research and finally i come across GnuPG that provide command line executable. For Windows version GnuPG, please visit here.

It was quite hard to understand how it works by looking into the manual/guide provided by the GnuPG site. After rounds of experiment and testing, revisiting the guide and some other online resources, finally i manage to work this out in my SSIS program.

Here i will illustrate my finding with a simple SSIS program that calls GnuPG command line executable to do the decryption works.

1. Generate key pairs

In any cryptography algorithm, it is always involve key for encryption and decryption, just like safety box and a key. Well, i am not really expert in cryptography so i will not comment much the logic behind it. The general idea for the whole encryption and decryption process to take place is as following

• Both sender and receiver hold the same key pair where receiver hold the private key, and sender hold the public key. Normally these keys are generated by receiver
• Sender want to pass information to the intended receiver, and will use the public key to encrypt the information
• Receiver received the encrypted information, and will use the private key to decrypt the information

There are numerous freeware available on the web that can be used to generate the key pairs including GnuPG that i mentioned earlier. However, to ensure the PGP standard really work across all of these freeware with the same algorithm behind, i have chosen another PGP freeware called PortablePGP to experiment in illustration. I will use PortablePGP to generate the keys and act as sender to encrypt the source file, this part i will just done manually.

First, download and installed PortablePGP, then launch the program. For 1st time use, it will prompt whether you would like to generate the key pair, now we don’t have the pair of key yet, so click on the “This is the first time i use PGP. Please generate a new private/public key pair” button. Alternatively you can close this prompt message and proceed to generate the keys later in the program interface.

You will be prompt for another interface to input key details as following. Important things to take note here is the passphrase, please keep the passphrase safe and secret as you will need this for decryption later. Go on and generate the keys.

After the keys are generated, you will come to the program main interface. You will see the generated keys with the detail you input earlier. Feel free to play around with the ‘Encrypt’ and ‘Decrypt’ option just for experience. Now, export the Private Key and Public Key and name accordingly as following (export 1 at a time),

As a result, you will have 2 key files (1)PrivateKey.txt, (2)PublicKey.txt. Observe the content of the key files, it shall looks like the following. Keep these files.

2. Manual Encryption/Decryption

Once the keys are in place, we will proceed to encrypt a sample file. I create a data.txt file for testing. By using PortablePGP, click on the ‘Encrypt’ button, choose the data.txt file and hit the ‘Encrypt’ button. Save the encrypted file into desired location. Observe the sample file before and after decryption. Take this opportunity to try the ‘Decrypt’ option as well, and observe the content of the original file, encrypted file as well as the decrypted file (for the sake of experience)

Now, we will use GnuPG to decrypt PortablePGP encrypted file. To start with GnuPG, download  the installer from from its official site, then launch and install it. Once installed, launch the gpa.exe from its installed path.

The Gnu Privacy Assistant – Key Manager screen will be prompt. Let’s import the PrivateKey.txt that generated by PortablePGP earlier.

This is the control panel for you to manage keys. You can manage multiple key for multiple different encryption/decryption purposes. Once the key is imported into GnuPG key manager, we are ready to test with command line decryption feature provided by GnuPG.

Before we plug this feature into SSIS, lets try to decrypt it manually in Windows command line. Bring up the cmd and point to GnuPG installation folder, for my environment, it is as following,

Now, lets decrypt a single file we encrypted earlier. Input the following command line and hit enter

gpg2.exe --output C:\PortablePGP\Data-Decrypted-byGnuPG.txt -d C:\PortablePGP\Data-Encrypted.txt

This line simply translate as — invoke gpg2.exe, decrypt the file C:\PortablePGP\Data-Encrypted.txt and place the decrypted output file as C:\PortablePGP\Data-Decrypted-byGnuPG.txt. If it is 1st time run, you will prompted to input the passphrase (remember the passphrase that i mentioned earlier during key creation?).  One thing to take note here is, after the passphrase is entered, it will cached in the program, then the subsequent decryption will not prompt for passphrase anymore. However this cache will be clear after a while. At the moment, I am still not sure  by when and how the cache will be cleared, but one thing for sure is, after workstation restarted, it will prompt for input again. Later we are going to explore an option to put the passphrase in the command itself so that the passphrase input prompt can be avoided.

After putting the correct passphrase, hit Ok to let decryption proceed. Observe the expected output file and its content after decryption. That’s it, we are getting the original file content back.

3. Automate Decryption with SSIS

Now realize that the manual command line is work-able, we can almost assure that calling it from SSIS shouldn’t have any issues. I will demonstrate this with 1 simple SSIS package.

The package that i am going to create will perform the following in my environment

• There are 2 folders in my environment, one is named ‘Encrypted’ where all the encrypted files will place in here, another one is named ‘Decrypted’ where all decrypted output files will place here
• when the package execute, it will look for all files in ‘Encrypted’ folder, decrypt it and place it in ‘Decrypted’ folder. That’s all.

I will create a new integration project named ‘TestDecryption’ with package name ‘PKG_TEST_DECRYPT.dtsx, then drag in a Foreach Loop Container, and point to the ‘Encrypted’ folder, as following,

Create 2 variables,

• varFullFilePath – to hold the full file path returned by the Foreach Loop Container whenever it reads a file in folder
• varFileName – to hold the file name.  We need this later to name the decrypted file dynamically

Map the collection value to varFullFilePath as following. Now this variable will be populated with the full file path whenever the Foreach Loop Container access a file in the folder

Next we are going to drag in a Scripts Task from Toolbox. This scripts task basically will extract the file name only from the full file path and store it into varFileName variable.

Put varFullFilePath as ReadOnlyVariable, this indicate input to the scripts task, and place varFileName as ReadWriteVariables, this indicate the scripts task going to have write access and write to this variable

Here I am going to use VB scripts to extract the file name. Hit the ‘Edit Scripts’ button to bring up the scripts pane and scripts the following,

Next, is our main character of the show, we are going to need the Execute Process Task to help us invoke the GnuPG executable in command line. So drag one in and link it with Success Precedence Constraints (the green line).

Double click the Execute Process Task, point the Executable and Working Directory with GnuPG exe and installation folder as following,

Switch to ‘Expression’ sheet, now this is the part that need pay extra attention. We need to provide the execute argument as expression so that file could be dynamically decrypted with file name same as encrypted file, as following,

“–batch –passphrase MyPassphrase –output D:\\IntegrationProject\\AutomatePGPDecryption\\Decrypted\\” +  @[User::varFileName] + ” -d ” +  @[User::varFullFilePath]

i elaborate a little bit more detail of the expression pass in. Remember we need to eliminate the passphrase entry prompt during automation run? And it is the reason we added 2 extra argument

"--batch --passphrase MyPassphrase"

These will pass in the passphrase into the command line itself and run it as batch. Obviously, the passphrase i used in the whole demonstration is ‘MyPassphrase’. With all the above set, now, lets throw in some encrypted file and test the automation (i simply duplicate the earlier encrypted file and rename).

Then, go back to the package and hit ‘F5’. Observe the program loop and decrypt the files one at a time.

Check the ‘Decrypted’ folder and review the output files

That’s it. We have completed a simple SSIS Automated PGP Decryption program. Take note that there are still plenty of GnuPG command option to be explored and experiment. For complete list of command option, please refer to GnuPG site

Lastly, I hope this guide is helpful and informative enough, i will share more if i manage to work out more interesting stuff. Forgive me if my English is poor and with grammar mistake.

Thanks for reading and happy programming!