SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS task perform PGP encryption with remote server


SSIS task perform PGP encryption with remote server

Author
Message
cafescott
cafescott
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 733
Hello,

I have an SSIS package with a task that I need to perform PGP encryption. I am running the package both from BIDS and from the package store of a SQL Server. The (GNU) encryption is being performed by a server on our network. I am wondering if someone can help me solve the problem of doing encryption on that "PGP server."

I have a batch file on the PGP server. If I login to this box using my network credentials and double-click the batch file (from Windows Explorer), it works fine.

However, the package won't be doing this. When I attempt to call the batch file from either BIDS or the SQL Server I get an error that there is "no public key." This is because the current directory is not set within the PGP server, and the encryption is being attempted where the package is being run.

I have tried two things to remedy this. First, prior to executing the batch file I attempt to set the current directory to the PGP server. I'm using this VB.NET command:
Directory.SetCurrentDirectory("\\PGP_Server\working_folder\")

It seems to work. However, when I insert a pause statement in my batch file I see that my current directory is not the PGP server. By the way, the batch file to perform encryption more or less resembles:
dir/p
gpg --recipient emailrecipient@somewhere.com --always-trust --encrypt "my_big_zipped_file.zip"
pause

The "dir/p" and "pause" statements are just for debugging.

The other way I've tried to set the current directory to the PGP server is by using the DOS "pushd" command; e.g.,
pushd "\\PGP_Server\working_folder\"
dir/p
gpg --recipient ...
pause

When I do this, the directory listing ("dir/p") shows the files in the PGP server's working directory, but the encryption still fails based on "no public key."

I'm sure that there are SSIS developers who have performed PGP encryption on a server other than where the package resides. How did you accomplish it?

thanks,
cafescott
cafescott
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 733
http://www.sqlservercentral.com/Forums/Topic365090-148-3.aspx
http://codingstuffs.blogspot.com/2007/03/pgp-inside-ssis-package.html

There are plenty of examples of PGP encryption being called from an Execute Process task where the PGP server is on the same box as the package. However, what if you need to perform the PGP encryption on another server besides where the SSIS package is being run?

This is a topic of interest, but somehow it is extremely difficult to get a solution:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e47b7d40-68b0-446a-82f5-b6b16b0a1c15/

In my package I have created two execute process tasks, and I have been disabling and enabling one at a time. The first has these settings:
Executable: c:\temp\PGP\mybat.bat
Working Directory: c:\TEMP\

The batch file is simple:
gpg --recipient EmailAddyWithCertificate --always-trust --encrypt filename.zip

(btw, the "Temp" folder is shared so that 'everyone' has full control.)

If I call the first process task from BIDS it works. Hooray. However, no one wants to have to only work within BIDS.

The second execute process task looks like:
Executable: \\MyPC_Name\Temp\PGP\mybat.bat
Working Directory: \\MyPC_Name\Temp\

If I save the package with the second execute process task enabled to a SQL Server's package store, and then attempt to run the package with a right-click (i.e., not a job), the command window opens and I see these error messages:

"CMD.EXE was started with the above path as the current directory.
UNC paths are not supported. Defaulting to Windows directory"

Then the encryption step is tried but it fails because the file I have specified (using a UNC path) should be encrypted in the batch file is not found; i.e., "No such file or directory."

I can't believe with the number of SSIS developers who need to be able to encrypt a file using a remote server to accomplish the task that there are so few examples of how to do it. Any help is appreciated.
cafescott
cafescott
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 733
A day and a half later, I have a slightly improved solution that is inspired by this site:
http://www.bidn.com/blogs/BradSchacht/ssis/1575/batch-files-command-lines

In my Execute Process task I have removed the value for the Working Directory. In the batch file (on the remote server), the top line is:
pushd \\myPGP_Server\Path\PGP

(The path is what I was using in the Working Directory field of the Execute Process task.)

This give me the expected encrypted file when I right-click on the package in the package store. When I run as a job the process using the SQL Service Account the task just hangs indefinitely. Sigh.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search