- 1 Introduction
- 2 Requirements
- 3 Getting started
- 3.1 GPG command line example
- 3.2 PGP encryption using SSIS introduction
- 3.3 Execute GPG / PGP commands directly in SSIS Execute Process Task (Pass Arguments)
- 3.4 Generate public key in PGP encryption using SSIS
- 3.5 How to encrypt a file in PGP encryption using SSIS
- 3.6 How to decrypt a file in PGP encryption using SSIS
- 3.7 How to import a public key in PGP encryption using SSIS
- 3.8 How to encrypt multiple files in PGP encryption using SSIS
- 3.9 Troubleshooting PGP encryption using SSIS
- 4 References
In this new article, we will show you how to perform PGP encryption using SSIS (encrypt / decrypt files using public / private key). Our previous article was about SFTP using our SFTP task for SSIS. Now we will show how to encrypt the information.
- First of all, you will need SSDT for Business Intelligence for SSIS projects.
- Download and install FREE PGP Command line gnupg for Windows.
- Optionally you can install SSIS PowerPack.
What is PGP / GPG (GnuPG) / OpenPGP ?
PGP stands for Pretty Good Privacy. It was created by Phil Zimmermann. PGP is the most popular email encryption in the world. It is an Encryption that requires a public key, a private key. You can encrypt, decrypt and sign documents, emails. PGP Tools now acquired by Symantec. In sort PGP Tools developed by Phil are not Open Source any more so use other on if you really need FREE one.
GPG (i.e. GnuPG)) stands for GNU Privacy Guard. This software is for multiple platforms like Linux and Windows. In this article, we will focus on Windows because we are using SSIS that is mainly installed in Windows (at least by the moment). This tool is free, so we will focus on this one instead of trying other licensed versions.
OpenPGP is standard around Public / Private Key Cryptography (RFC4880) . Check this page. Both GPG and PGP follow OpenPGP Standard however there will be a case when they are not 100% compatible with each other.
In this article we will use GPG only which is essentially same features as PGP but GPG is Open Source around OpenPGP Standard and PGP is not open source.
So let’s get started. First of all, we will start with a simple the command line.
GPG command line example
- First of all, check the requirements and open the command line.
- Secondly, in the command line write gpg –help this will list all the commands available:
- Also, in order to generate your personal key pair, write the following command:
- Finally, the command previously provided will ask for a name and email, write an O to press OK and a Passphrase. You will be able to create your key pair with that information.
PGP encryption using SSIS introduction
It is possible to perform PGP encryption using SSIS with the GPG command line which is free. We can use native execute process task that comes with SSIS. In this article, we will show some commands of gpg.
- Open a new SSIS Package.
- Drag Execute Process Task from SSIS Toolbox and double click to configure (see next step).
- Using SSIS Execute Process Task, we will invoke the gpg commands. There are different ways to invoke the gpg commands. One way is to execute command stored in a .bat file or .cmd file
- To invoke command from a file create a file called gpg.cmd and type gpg --version , save file to path like c:\sql\gpg.cmd for this demo.
- Configure Execute Process Task something like this if you invoke command from batch file:
- Also, note that the windows style is hidden. That will hide the command line when running the package.
- In addition, the StandardOutputVariable will create an object variable in the output.
- To verify the output, we will use the ZS Logging task
- Click OK to save Execute Process Task UI.
- Now drag ZS Logging task from toolbox and configure like below. This will Log output captured by gpg command. This step is optional but will help you a lot if you want to know what happened in previous step (e.g. capture error message)
Execute GPG / PGP commands directly in SSIS Execute Process Task (Pass Arguments)
1. In the previous example, we used a cmd file with the commands. Sometimes we need to use SSIS variables and pass information dynamically. In this new example, we will invoke the gpg command line directly and send the arguments in the arguments property like this:
Generate public key in PGP encryption using SSIS
Now lets look at common task with PGP cryptography which is generate Public / Private Key pair.
- In order to generate a public key, you need to run the following arguments in SSIS:
1--armor --output "c:\sql\ssiskey.txt" --export "Name"
- In addition, the key name is the name of the pair key created and the
output contains the path of the stored key.
How to encrypt a file in PGP encryption using SSIS
- First of all, we will encrypt a file named ssiskey.txt:
- The argument used is the following:
1--recipient "Name" --output "c:\sql\ssiskey.txt.gpg" --encrypt "c:\sql\ssiskey.txt"
- We are creating an encrypted file named ssiskey.txt.pgp
How to decrypt a file in PGP encryption using SSIS
- In order to start, we will decrypt the file created previously:
- Also, we will use the following argument:
How to import a public key in PGP encryption using SSIS
- In order to send a message and decrypt it, you need a public key. If you publish a public key, the recipient needs to import the public key. To import a key use the following argument:
1gpg --import "c:\sql\publickey.txt".
- Secondly, in the Execute Process Task, will look like this:
How to encrypt multiple files in PGP encryption using SSIS
- If you want to encrypt multiple files, you can use the ZS Advanced File System combined with the Foreach loop container to filter and select the files to encrypt.
- In addition, the package will look like this:
- Also, in the Advanced File System, we will list the files as ADO.net DataTable:
- In the next step, we will use the Foreach ADO Enumerator in the Foreach Loop container:
- Next, we will select the first column (column 0) to get the file path from the list.
- Finally, we will use the following expression for the argument property to encrypt all the txt files of the ADO list:
12"--recipient \"Daniel Calbimonte\" --output \""+ @[User::filepath]+".gpg\""+"--encrypt \""+@[User::filepath]+"\""
Troubleshooting PGP encryption using SSIS
- First of all, if your package works in SSDT or VS and fails in the agent, verify that you are using a proxy account with permissions to run the gpg command line. For more information about SQL Agent proxies, refer to this link.
For more information, refer to these links: