GNP and SSIS on different machines

Im struggling to get this to work. Currently trying to use GNU Privacy Guard  and SQL Server SSIS.

Currently I create an SSIS package to extract data into an excel file on Machine A. Which is encrypted and posted to an FTP site for retrieval.

On Machine B has the GNU Privacy Guard application installed.

On Machine C has the SSIS installed and calls the application on Machine B. However, the key I believe is sitting somewhere on Machine B. The SSIS fails on encryption saying invalid or missing key.

The logic of why there are three separate machines is due to company policy, restriction as to what can be installed on certain machines. For instance, Machine C is a dedicated SQL Server box.

Anyways, does anyone have any idea how I can make this work? Could I copy the key from Machine B and paste it somewhere on Machine C so that it has the key to encrypt the file?
Who is Participating?
TeknikDevConnect With a Mentor Author Commented:
Ok figured out the problem. The main key is making sure the GNU.exe file should be set to your local hard drive in the argument and not using any UNC.

To encrypt, this is the argument for the Execute Process Task Editor in SSIS.

Arguments: --yes --always-trust --recipient "name of public key" --output "File_name.csv.gpg" --encrypt "\\unc path\File_name.csv"

Executable: C:\Program Files\GNU\GnuPG\gpg2.exe  

This was the fix since the server had a high security setting for unknown apps. So it would prompt the user to either run the application or not, but if you are automating it through SSIS, then this would not work.
Ryan McCauleyData and Analytics ManagerCommented:
If you're able to log in to Machine B and manually decrypt the file, then you're right that the key is on machine B. It depends on how it's being stored, but you can likely move a copy of it to Machine C (where SSIS is running), which you'll need to do in order to have SSIS decrypt the file. Even though Machine C is running a program from Machine B, it still runs in the context of Machine C, so that's where the key needs to be.

Since you're decrypting, you'll be using the private key from the pair, so make sure you keep it safe. Also, make sure the user running SSIS (likely the network service account, though possibly a domain proxy) has rights to access it.
TeknikDevAuthor Commented:
Hi Ryan, the file is extracted and needs to be encrypted. Sorry for the confusion. So if I have the public key to encrypt, where should I place the key on Machine C (SSIS located w/ NO GNU GP application)???

The logic is:
So the application will be kicked off from MACHINE B using SSIS on MACHINE C and the public key is located on Machine C is used to ENCRYPT the file.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Ryan McCauleyData and Analytics ManagerCommented:
Ideally, the application will be configured on the same server as SSIS. However, since you can't install GPG on that machine, can you (and noted that this is a bit sketchy) use something like psexec to run GPG from the command line on the other machine? pxexec is a tool that lets you spawn a process on a remote machine, not just from the remote machine, running in the context of that remote machine. In this case, you'd be running it remotely, where it presumably has access to the needed key file. It looks like GPG has pretty complete support for the command line:

Also, there's no reason you couldn't keep your key in a public place - since it's the public key used to encrypt, there's no reason to protect it in any way, so secure storage isn't necessary. In that case, why couldn't the public key just be in a text file that you direct GPG to use when you encrypt the file?
TeknikDevAuthor Commented:
Ok I got approval to install GPG on the machine that has the SSIS. I was able to run the SSIS and it worked successfully. HOWEVER, when I try to schedule a job using SQL SERVER AGENT, it fails. I imported the public key using SQLSERVERAGENT USA login id also. To make sure the job doesnt login using this id to kick off GNU GP.

Why is this? Anyone know how I can get this to work?

So now, the SSIS and GNU GP application is sitting on MACHINE C .
Ryan McCauleyData and Analytics ManagerCommented:
You say the job fails - can you provide any more details? Anything in the job history log or the Windows event log that's relevant? It could be a number of things, including permissions or an improperly stored key, but without more detail it's hard to tell.

When you issue the command line to run GPG, can you pipe it to a file so you can see what's happening? Something like:

gpg --whatever --somethingelse > c:\temp\pgplog.txt

Open in new window

That way, you can see what error GPG is actually throwing, in case you don't get the detail you need in the SQL Agent log.
TeknikDevAuthor Commented:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

This is the error I get.
Ryan McCauleyData and Analytics ManagerCommented:
That sounds like it might not be an error with GPG at all, but rather an error with an encrypted connection string in your configuration. That would also explain why you're able to test run it as the developer, but once you publish it, it doesn't work. Have you tried the suggestions in this MSKB?

If you've set the sensitive details of the SSIS package to be encrypted using your user key, then they can't be decrypted by another user or by the SQL Agent account. Try using another selection, as seen in the linked article.
TeknikDevAuthor Commented:
This was the answer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.