Solved

GNP and SSIS on different machines

Posted on 2012-04-11
10
781 Views
Last Modified: 2012-04-21
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
Comment
Question by:TeknikDev
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37838815
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
 

Author Comment

by:TeknikDev
ID: 37838869
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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37838966
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
 

Author Comment

by:TeknikDev
ID: 37839002
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37839216
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
 

Author Comment

by:TeknikDev
ID: 37839361
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
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37840463
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
 

Accepted Solution

by:
TeknikDev earned 0 total points
ID: 37852523
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
 

Author Closing Comment

by:TeknikDev
ID: 37875068
This was the answer
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now