{"id":7161,"date":"2019-06-07T02:42:20","date_gmt":"2019-06-07T02:42:20","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7161"},"modified":"2022-03-25T14:44:23","modified_gmt":"2022-03-25T14:44:23","slug":"ssis-pgp-encryption-decryption","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-pgp-encryption-decryption\/","title":{"rendered":"SSIS PGP Encryption \/ Decryption (Using FREE GPG Tool)"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>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\u00a0SFTP using our SFTP task\u00a0for SSIS<\/a>. Now we will show how to encrypt the information.<\/p>\n

Requirements<\/h2>\n
    \n
  1. First of all, you will need SSDT for\u00a0Business Intelligence for SSIS<\/a> projects.<\/li>\n
  2. Download and install FREE PGP Command line\u00a0gnupg<\/a> for Windows.<\/li>\n
  3. Optionally<\/strong> you can install SSIS PowerPack<\/a>.<\/li>\n<\/ol>\n
    Note: This article is for the information purpose only.<\/div><\/div>\n

    What is PGP \/ GPG (GnuPG) \/ OpenPGP ?<\/h3>\n

    PGP<\/strong> stands for Pretty Good Privacy. It was created by Phil Zimmermann. PGP is\u00a0the most popular email encryption in the world. It is an Encryption that\u00a0requires a public key, a private key. You can encrypt, decrypt and sign\u00a0documents, 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.<\/p>\n

    GPG<\/strong> (i.e. GnuPG)) stands for GNU Privacy Guard. This software<\/a> is for multiple platforms\u00a0like Linux and Windows. In this article, we will focus on Windows because we are\u00a0using SSIS that is mainly installed in Windows (at least by the moment). This\u00a0tool is free, so we will focus on this one instead of trying other licensed\u00a0versions.<\/p>\n

    OpenPGP<\/strong> is standard around Public \/ Private Key Cryptography (RFC4880<\/a>) . Check this page<\/a>. Both GPG and PGP follow OpenPGP Standard however there will be a case when they are not 100% compatible with each other.<\/p>\n

    For more information read this link<\/a> and this link<\/a>.<\/p>\n

    Getting started<\/h2>\n

    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.<\/p>\n

    So let’s get started. First of all, we will start with a simple the command line.<\/p>\n

    GPG command line example<\/h3>\n

    After you install gpg command line we can open it to get started. There are many more examples explained here<\/a>\u00a0and here<\/a>.<\/p>\n

      \n
    1. First of all, check the requirements and open the command line.<\/li>\n
    2. Secondly, in the command line write gpg –help this will list all the\u00a0commands available:\n
    3. Also, in order to generate your personal key pair, write the following command:\n
      gpg --gen-key<\/pre>\n<\/li>\n
    4. Finally, the command previously provided will ask for a name and email, write\u00a0an O to press OK and a Passphrase. You will be able to create your key pair\u00a0with that information.<\/li>\n<\/ol>\n
      PGP encryption using SSIS introduction<\/h3>\n
      It is possible to perform PGP encryption using SSIS with the GPG command line which is free. We can use\u00a0 native execute process task<\/a> that comes with SSIS. In this\u00a0article, we will show some commands of gpg.<\/p>\n
        \n
      1. Open a new SSIS Package.<\/li>\n
      2. Drag Execute Process Task from SSIS Toolbox and double click to configure (see next step).<\/li>\n
      3. Using SSIS Execute Process Task, we will invoke the gpg commands. There are different ways to\u00a0invoke the gpg commands. One way is to execute command stored in a\u00a0.bat file or .cmd file<\/li>\n
      4. To invoke command from a file create a file called gpg.cmd and type gpg –version<\/span>\u00a0\u00a0 , save file to path like c:\\sql\\gpg.cmd for this demo.<\/li>\n
      5. Configure Execute Process Task something like\u00a0this if you invoke command from batch file:\n
      6. Also, note that the windows style is hidden<\/strong>. That will hide the command\u00a0line when running the package.<\/li>\n
      7. In addition, the StandardOutputVariable<\/strong> will create an object variable\u00a0in the output.<\/li>\n
      8. To verify the output, we will use the ZS Logging task<\/a>\n
        \"Show<\/a>
        SSIS output results<\/p><\/div><\/li>\n
      9. Click OK to save Execute Process Task UI.<\/li>\n
      10. Now drag ZS Logging task<\/a> 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)\n
        \"SSIS<\/a>
        SSIS Logging task<\/p><\/div><\/li>\n<\/ol>\n
        Execute GPG \/ PGP commands directly in SSIS Execute Process Task (Pass Arguments)<\/h3>\n
        1. In the previous example, we used a cmd file with the commands. Sometimes\u00a0we need to use SSIS variables and pass information dynamically. In this new\u00a0example, we will invoke the gpg command line directly and send the arguments in\u00a0the arguments property like this:<\/p>\n
        \"gpg<\/a>
        SSIS execute gpg encryption<\/p><\/div>\n
        Generate public key in PGP encryption using SSIS<\/h3>\n
        Now lets look at common task with PGP cryptography which is generate Public \/ Private Key pair.<\/p>\n
          \n
        1. In order to generate a public key, you need to run the following\u00a0arguments in SSIS:\n
          \"Generating<\/a>
          SSIS generation of public key<\/p><\/div>\n
          --armor --output \"c:\\sql\\ssiskey.txt\" --export \"Name\"<\/pre>\n<\/li>\n
        2. In addition, the key name is the name of the pair key created and the
          \noutput contains the path of the stored key.<\/li>\n<\/ol>\n
          How to encrypt a file in PGP encryption using SSIS<\/h3>\n
            \n
          1. First of all, we will encrypt a file named ssiskey.txt:<\/li>\n
          2. The argument used is the following:\n
            --recipient \"Name\" --output \"c:\\sql\\ssiskey.txt.gpg\" --encrypt \"c:\\sql\\ssiskey.txt\"<\/pre>\n
            \"How<\/a>
            Encrypt file gpg in SSIS<\/p><\/div><\/li>\n
          3. We are creating an encrypted file named ssiskey.txt.pgp<\/li>\n<\/ol>\n
            How to decrypt a file in PGP encryption using SSIS<\/h3>\n
              \n
            1. In order to start, we will decrypt the file created previously:\n
            2. Also, we will use the following argument:\n
              --decrypt-files \"c:\\sql\\ssiskey.txt.gpg\"<\/pre>\n
               <\/li>\n<\/ol>\n
              How to import a public key in PGP encryption using SSIS<\/h3>\n
                \n
              1. In order to send a message and decrypt it, you need a public key. If you\u00a0publish a public key, the recipient needs to import the public key. To import a\u00a0key use the following argument:\n
                gpg --import \"c:\\sql\\publickey.txt\".<\/pre>\n
                You can download a public key here:<\/p>\n