Using SSIS to automate PGP encryption of text file

Question

Using SSIS to automate PGP encryption of text file

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

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

TangoVictor SSChampion Points: 10975 More actions · Answer 1

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.

TangoVictor SSChampion Points: 10975 More actions · Answer 2

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.

g_u_u_bush SSC Enthusiast Points: 132 More actions · Answer 3

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.

s.kadambari Valued Member Points: 61 More actions · Answer 4

s.kadambari

Valued Member

Points: 61

January 12, 2011 at 2:14 am

#1272230

thanks

mklein2 SSChasing Mays Points: 609 More actions · Answer 5

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".

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 6

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]

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 7

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]

suri.yalamanchili SSCommitted Points: 1645 More actions · Answer 8

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

harikrishna796 SSC Enthusiast Points: 112 More actions · Answer 9

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,

harikrishna796 SSC Enthusiast Points: 112 More actions · Answer 10

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