PGP in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

PGP in SSIS

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-22 : 16:50:42

Ok Guys,Im pretty lost here.This is my first time using PGP with SSIS...

My SSIS process creates 2 .zip file at the end of the execution.
I need to encrypt these files before I send them across the ftp to our Vendors.This all is to be done in SSIS.

I downloaded GNU PGP command line version [gnupg-w32cli-1.4.9.exe]
http://enigmail.mozdev.org/documentation/gpgsetup.php#obtain

I ran the gpg --version command to ensure the Home Dir is returned.All is well.I tried creating a key and it worked out well in command prompt.

Here is what I don't get?
1) Do I need to create a key each time I submit the .zip files over.Which is once a week ? [How often are keys created?]
2) Since I have 2 files,Do I create 2 keys.1 for each?
3) If I dont need to create a key each time we send them a new file,I can use the same key and passphrase each time and the creation of this key can be done outside SSIS thru command prompt.
4) The SSIS would be used only to encrypt or decrypt the .zip file with the keys created.Correct ?
5) Now I know I can use the Execute Process Task to do this in SSIS.But how,Do I pass the arguments?I referred to this site but am not clear with the arguments part http://codingstuffs.blogspot.com/2007/03/pgp-inside-ssis-package.html
6) How do I send my Vendor the keys for them to decrypt the files? What is the best way
7) Besides the key do I need to send my Vendor anything else ?

Here is what I have so far

Executable : C:\Program Files\GNU\GnuPG\gpg.exe
Success Value : 2
Arguments : [Im not sure.How I can pass the 2 .zip files names here]

8)Since I have 2 file names,Do I have to have somesort of a loop ?
9)After encrypting Ill need to FTP the files to my vendor,is there an argument that I can pass while encrypting so the files can be ftp'ed too ?

I know I asked way to many questions. I have read most of the online manuals on PGP..but it did not clear my doubts.

Do help me understand the correct way of doing things.Thanks

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-23 : 09:01:25

All,

No one replied me, Im in need for some explanations.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-10-23 : 09:29:22

http://www.sqlservercentral.com/Forums/Topic365090-148-1.aspx

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-23 : 14:08:21

Thanks you for the site.

I tried the following not sure if it is correct.

1) The vendor sent me their public key
2) I imported the public key to my machine
3) Created an SSIS package with an Execute SQL Task
4) Set the following

Executable : C:\Program Files\GNU\GnuPG\gpg.exe
Arguments : XXX -encrypt C:\A_20081023.zip

XXX refers to the User id for the public key given in no 1)

When I execute.No errors are returned but I dont see any changes.What should I expect to see...Does the .zip extension of file change to .pgp.zip ?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-10-23 : 14:20:15

Not sure but maybe try to go to PGP forum and search. I only dealt with self-extraction kinds.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-10-24 : 10:01:40

4. Encrypt and decrypt

After installing everything and configuring everything in the way we want, we can start on encrypting and decrypting.

When encrypting or decrypting it is possible to have more than one private key in use. If this occurs you need to select the active key. This can be done by using the option -u UID or by using the option --local-user UID. This causes the default key to use to be replaced by wanted key.

If you want to change recipient this can be done by the option -r or by the option --recipient.
4.1 Encrypt

The command to encrypt is

gpg -e Recipient [Data]

or

gpg --encrypt Recipient [Data]

To avoid the risk that somebody else claims to be you, it is very useful to sign everything you encrypt, see signatures.

4.2 Decrypt

The command for decrypting is:

gpg [-d] [Data]

or

gpg [--decrypt] [Data]

Also here stdout is preset, but with the -o option you can redirect the output to a file.

From:
http://www.dewinter.com/gnupg_howto/english/GPGMiniHowto-4.html#ss4.1

---
http://www.ssisdude.blogspot.com/

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-24 : 12:04:53

Hi There,

Thanks for the info.I would try this out and update my results.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-24 : 16:03:28

I tried to add the below in arguments for my Execute Script Task.

It gets executed with no errors but It did not create a.gpg

Here is what I am passing,this line works well in command prompt !

Arguments
--recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt " C:\Testing.zip"

XXX -name of public key

Any idea why this does not work?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-10-24 : 16:25:18

Can't help but notice - is it gpg or pgp

pgp --decrypt --passphrase "my password" "*.pgp"

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-24 : 16:49:20

Im using

GNU PGP command line version [gnupg-w32cli-1.4.9.exe]

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-27 : 11:53:08

I have tried changing the arguments execte sql task to be

gpg --recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip"

It executes,no erors but no gpg file created.The same syntax works in command line and file gets created.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 12:10:36

The path to the file is relative SQL Server, not your local client.

E 12°55'05.63"
N 56°04'39.26"

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-27 : 12:24:59

Peso,

I have installed GPG on my local machine.And the file to be encrypted is also on my local.The SSIS package is being excuted locally.

Do you know if the arguments being passed is correct?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-27 : 15:08:56

Changing to this argument and including some space,got it to work

--recipient "XXX" --output "C:\Testing.zip.gpg" --encrypt"C:\Testing.zip".

How can I pass the file names as variable's.I have 2 files to be processed with following format
A_20081027.zip & B_20081027.zip

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-27 : 16:43:45

I got it to work.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-10-28 : 01:15:02

Can you tell me what was your solution?

---
http://www.ssisdude.blogspot.com/

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-10-28 : 09:02:48

I used a For Each Loop,pass the arguments to only pick files with todays date of format "YYYYMMDD".

Save file names to a variable.[User::FileOne]

Place the Execute Process Task in the For Each Loop.

Arguments :--recipient "XXX" --output "C:\.gpg" --encrypt "C:\"
Std Input Variable :[User::FileOne]
Expression > Arguments : "--recipient "
+ "\""
+ @[User::Key]
+ "\" "
+ "--output "
+ "\""
+ @[User::FileOne] + ".gpg"
+ "\" "
+ "--encrypt "
+ "\""
+ @[User::FileOne]
+ "\""

That's about It.

mayevski
Starting Member

1 Post

Posted - 2008-12-21 : 11:37:42

Check PGP task for SSIS on http://www.eldos.com/sbbsrv/ssis-pgp-task.php . This task will let you avoid using external applications.

Sincerely yours,
Eugene Mayevski

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources