Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

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?
0
TeknikDev
Asked:
TeknikDev
  • 5
  • 4
1 Solution
 
Ryan McCauleyDatabase and Reporting 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.
0
 
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.
0
 
Ryan McCauleyDatabase and Reporting 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:

http://www.gnupg.org/documentation/manuals/gnupg/Operational-GPG-Commands.html

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?
0
Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

 
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 .
0
 
Ryan McCauleyDatabase and Reporting 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.
0
 
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.
0
 
Ryan McCauleyDatabase and Reporting 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?

http://support.microsoft.com/kb/918760

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.
0
 
TeknikDevAuthor 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.
0
 
TeknikDevAuthor Commented:
This was the answer
0

Featured Post

The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now