Post reply

Using SSIS to automate PGP encryption of text file

  • June 16, 2010 at 12:15 pm

    #1181220

    the ssis job is not running under the agent account. it is running under a proxy account based on a credential i created using my AD account. It's basically doing an "Execute As" and running as me on my machine. But there still may be an issue with security since this is the first time we have done anything like this (encryption) in ssis.

  • June 16, 2010 at 12:29 pm

    #1181225

    Got it !!!

    OK, apparently running under the proxy does not matter. I just changed the agent account to run under my AD account and it worked. Apparently the profile that is in use when using the proxy is still the agent's and not the proxy's.

  • January 11, 2011 at 1:26 pm

    #1272070

    I fought this for several days before I finally got it right. We are allowing clients to use out public key, then we decrypt with out private key. And we are using version 6.5.8, the last freeware version. Everything has to be right and it will work. I am doing this in a for-each loop, because we get several files each day and we process them at night.

    1. Define a variable to store the filename (I am using @FileToProc)

    2. Put a file process task inside the for-each container. You have to define where PGP is as the SQL Server sees it, by default it is c:\program files (x86)etwork associates\pgpcmdln\pgp.exe

    3. Optional - Define your working directory. UNC is fine \\server\directory

    4. Under the Expressions, define your argument as:

    <for-each variable> + " -z <your private passphrase>"

    For instance, if our pass phrase to our private key is UnlockThatSOB then our argument is:

    @[User::FileToProc] + " -z UnlockThatSOB"

    And it works and I get a success code of 0. I'll let you handle the errors when people send you files encrypted with the wrong key or encrypted then zipped or unencrypted or any of the other funny things users do just to mess with your heads.

  • January 12, 2011 at 2:14 am

    #1272230

    thanks

  • February 15, 2011 at 6:43 am

    #1287017

    Hi all,

    I read the whole topic and I need your help.

    I want to use this for encrypt my file.

    --recipient "YOUR-NAME" --output "C:\A_20081023.zip.gpg" --encrypt "C:\A_20081023.zip"

    So I use the control flow --> Execute process task.

    Properties :

    - Executable : I have the path of gpg.

    - Standard Variable Input : User::FileName (my variable with the name and location of my file)

    I use expression for complete the propertie "Argument"

    In recipient I put the password for encrypt and decrypt the file?

    In ouptout I put my variable + .gpg ?

    In encrypt, I put only my variable?

    I put this

    "--recipient \"XXX\" --output " + @[User::FileName] + ".gpg\" --encrypt " + @[User::FileName]

    My formula is ok?

    Because I have an error for this step.

    [Execute Process Task] Error: In Executing "C:\Program Files\GNU\GnuPG\gpg.exe" "--recipient "XXX" --output C:\Documents and Settings\A2MV3ZZ\My Documents\Visual Studio 2005\Projects\Call_Center_QA\Data\import_3M_20110215.csv.gpg" --encrypt C:\Documents and Settings\A2MV3ZZ\My Documents\Visual Studio 2005\Projects\Call_Center_QA\Data\import_3M_20110215.csv" at "", The process exit code was "2" while the expected was "0".

  • December 6, 2011 at 2:49 pm

    #1417687

    I have a script task in an SSIS package that I wrote to PGP encrypt files using a public key from client A. Among other things, it writes a temporary bat file with the pgp command in it, executes it, and goes on... Works great.

    Now client B wants the same thing. B sent me his public key. I had the agent add B's key to her public keyring and got back the key id just like I did for client A. I modified B's package to use B's keyid and ran it. No encrypted file was produced!

    So now I am debugging this in DOS (I added A's and B's keys to my own keyring)

    This works:

    C:\> pgp -e D:\myfile.txt 0xclientBkeyid

    This does NOT work:

    C:\> pgp +batchmode -e D:\myfile.txt 0xclientBkeyid

    But this does!

    C:\> pgp +batchmode -e D:\myfile.txt 0xclientAkeyid

    What could be the difference between A's key and B's key such that this is the case?

    [font="Courier New"]ZenDada[/font]

  • December 7, 2011 at 12:25 pm

    #1418334

    Should anyone wish to know the solution...

    I had to sign this public key with my secret key in order to execute the command in batch mode.

    [font="Courier New"]ZenDada[/font]

  • March 21, 2012 at 10:20 am

    #1462273

    I was able to achieve this using the arguments and setting the working directory to the exe folder

  • December 19, 2012 at 2:38 pm

    #1570317

    it would be very helpfull if you share the screen shots of the execute task, how to pass arguments..as this is a critical task for me which needs to be done by this month end,

  • December 19, 2012 at 2:41 pm

    #1570320

    tim could you please help me out with details....i mean how to configure the execute task,please provide with arguments and variables

    as it is very critical for me...please help me out..thanks in advance

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply