March 13, 2013 at 7:07 am
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,
March 13, 2013 at 1:23 pm
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:
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.
March 13, 2013 at 2:26 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply