Tuesday, March 6, 2007

PGP inside a SSIS Package

So, you've got an SSIS package that moves data from one source, let's say an FTP site, and move it into your database. Because the data you're grabbing from the FTP is "sensitive" it's encrypted... well kinda. It's encrypted using PGP (Pretty Good Privacy). PGP is great for stuff that's pretty important, but not worth being too hardward intensive.

So Download This file

This will give you the GNU's PGP instal file.

This link will walk you through setting it up, adding needed bits to the PATH system variable, creating a key, exporting that key, and then sending the new key to someone who's going to be encyrpting data with this key.

Now that it's installed, you've got a key, and you know the steps you need to take to encrypt or decrypt the file.

Step One. Create an Execute Process Task.Drag one of these puppies onto the work area.

A double click will bring up the General Details.
A click on the Process Item on the left, below the highlighted General.
Here we find some things that are actually interesting.
It should look as follows.
The fields we're going to be looking at are as follows:
Executable - the gpg.exe executable, can be hard coded, default location is
C:\Program Files\GNU\GnuPG\gpg.exe

Arguments - this will the the command line args that you would use if you were running this
command in the DOS prompt. This will follow this general form.
--passphrase-fd 0 --decrypt-files F:\filepath\file.asc
"--passphrase-fd 0" indicates that the passphrase is going to be coming in as
an input variable.
"--decrypt-files" obviously tells us that we're going to decrypt a file at the following
location.
StandardInputVariable - this is your password. Feel free to make this a variable to allow
the password to be passed in from a config file.
FailTaskIfReturnCodeIsNotSuccess - I recomend setting this to true, it makes things slightly
easier to debug.
SuccessValue - I kinda cheat here and set this to 2. This is because the process returns a
warning. The warning is issued because gpg cannot check the signature, and because
the message was not integrity protected. (If anyone out there has any thoughts on this
I'm open to suggestions)
WindowStyle - this can stay normal or you can make it hidden, I don't know when this will
actually be seen by a person, but who knows...

Your Process section Should look as follows:
One last thing to investigate is the Expressions section. In my case the ftp file that I was downloading every day had a changingfilename such as "filename20070101". Where 1/1/2007 was today's date. This meant I needed some logic to change that filename, so I used expressions here. I also could have altered this variable from the location it was called in the code. But I chose to do everything in the SSIS package.

I defined the Arguments expression as follows:

In this case I've got an ugly variable called @FormattedDate which is defined in an abnoxous way as follows:

DATEPART("weekday", GETDATE()) == 2 ? (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) + ( DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) ) + ( DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) ) : (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) + (DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )) + ( DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) )

if it's January 1st, 2007 the variable would return: 20070101 (it does...just trust me...)

At this point, we've got a ready to roll decryption task. Enjoy.

11 comments:

Unknown said...

Hmmm, so you do have some kind of formal education- I assumed you were a third-grader from the inane post you dropped like an errant turd on my blog.

Oh, and four sentences may be all I need to make my point, you pinhead.

Unknown said...

Hi Pete,

Not sure what Im doing wrong here.
In the argument,is this what should be passed.

Arguments : XXX -encrypt C:\A_20081023.zip

(XXX refers to the User id for the public key given)

While excuting it excutes with no error but I dont see any .gpg file created.

hav2thnk said...

IMHO, I think that setting the SuccessValue to 2 simply tells SSIS to consider the error code a success; only 0 is truly a success.

In my case, I noticed that I am able to setup the arguments using an expression similar to what has been discussed. This runs fine in Visual Studio. However, when I store the package on SQL Server and invoke using VB.NET, the same Execute Process task fails with return code 2. It appears that the arguments are wrapped in double quotes, but even a simplified argument seems to fail. For example, I tried to run using "--list-keys" as the argument and get the following error only when running on SQL Server:
[-1073573551] In Executing "C:\Program Files\GNU\GnuPG\gpg.exe" "--list-keys" at "", The process exit code was "2" while the expected was "0".

Yet I can post the following into a command prompt and it runs fine:
"C:\Program Files\GNU\GnuPG\gpg.exe" "--list-keys"

Since it is GPG compaining about the parameters, I didn't think it was a permissions issue. Any thoughts?

hav2thnk said...

FYI, I resolved the issue. After writing an SSIS script to execute the GPG command using VB, I was able to capture the error stream. The issue was that the GPG keyring was not found; it was looking under the Default User application data directory.

So, although I thought the SSIS process was running under a separate admin account (under which I setup the GPG keys); I needed to copy the keyring folder from the admin account directory to the default user directory.

Error was...
ERROR:[gpg: keyblock resource `C:/Documents and Settings/Default User/Application Data/gnupg\secring.gpg': file open error gpg: keyblock resource `C:/Documents and Settings/Default User/Application Data/gnupg\pubring.gpg': file open error gpg

Unknown said...

Hi

i designed the task as you said,i asked other user to create a encrypted text file. Now i tried to decrypt it by executing package, a command prompt window opens and asks for file descriptor 0.what is this pass phrase and what value does the other user has to provide for this passphrase.

Thaks

Jason Y said...

Thanks for the article and taking the time to write it, but I've a comment on the date variable...It could be formatted this way...cleaner and smaller.

(DT_WSTR,4)YEAR(GETDATE()) + SUBSTRING("0" + (DT_STR, 2, 1252) DATEPART( "MM", GETDATE()) , LEN( "0" + (DT_STR, 2, 1252) DATEPART( "MM", GETDATE()) ) - 1, 2 )+ SUBSTRING("0" + (DT_STR, 2, 1252) DATEPART( "DD", GETDATE()) , LEN( "0" + (DT_STR, 2, 1252) DATEPART( "DD", GETDATE()) ) - 1, 2 )

JPSINGH said...

Still simpler expression is:
(DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "MM", GETDATE()), 2)+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "DD", GETDATE()),2)

MrBoston said...

Still Simpler Date: Replace(convert(char(10), getdate(), 101),'/','')

SilverHawk said...

MrBoston, you're using SQL, SSIS syntax is a little more complicated, JP's is the best you can do in the expression builder. My favorite for sql is Convert(varchar,GetDate(),112)

Elena said...

you can use "--trust-model always" to stop the warnings like this: --trust-model always -r @PassPhrase --output @EncryptedFile --encrypt @OriginalFile

Elstel said...

Informative article, just what I was looking for. gpg sign key