August 1, 2016 at 1:45 am
Hi All
Not sure if this is the most appropriate place to post but wondering if someone can assist with this strange issue.
I have several different SSIS packages that need to transfer files utilising SFTP. As such, I installed WinSCP and implemented the .Net Assembly in a script task defined in a separate SSIS package. Let’s call it “WinSCP.dstx”. Parent packages that need to transfer files via SFTP simply run the WinSCP.dstx package and pass across the necessary values required to perform the SFTP transfer. This all works very well.
Recently, we’ve had the need to download some file that are PGP encrypted. I implemented GnuPG and can successfully decrypt the files via the command prompt after they have been downloaded by my WinSCP.dstx package. In order to automate the process I added an Execute Process Task to the WinSCP.dstx package to run GPG.exe to decrypt the files after the transfer has been completed. I installed GnuGP on the SQL Server and transferred all the keys and key-ring to the SQL Server.
I tested the setup and it all works perfectly when the parent package is run from the Visual Studio environment. The files get downloaded and decrypted successfully.
However, the package that needs to SFTP and decrypt the files is scheduled as a SQL Job. For some reason when the package is run from a SQL Job, the decryption fails with an error message “No Secret Key”. This normally indicates that the user executing the decryption can’t find the necessary private keys.
In order to test this, I logged onto the SQL Server machine as the same user that is configured to run all the SQL Server services (including the SQL Agent). I ran the decryption from a command prompt and the decryption worked so clearly the user running the SQL Server services can see the private keys required. Whilst still logged onto the SQL Server, I then ran the package from the Visual Studio environment. It worked. If however I run it from the SQL Job it fails.
To further isolate the issue, I disable the Execute Process Task in the WinSCP.dstx package which performs the decryption and brought it into the parent (calling) package instead. Now it works fine when run from the SQL Job.
So, it only seems to be an issue when trying to execute it from the separate (child) WinSCP.dstx package which is run by the parent package. I would prefer to have the decryption functionality in my WinSCP package as it makes sense to have it there where it is defined only once instead of multiple times in any package that may require it.
Any thoughts or suggestion would be appreciated.
We are still using Visual Studio 2005 with SQL Server 2008.
August 1, 2016 at 8:35 am
My guess: One of your parameters to the encryption process somehow changes value in the child package in certain scenarios.
Have you tried to log all the parameter values both from SSIS and inside the execute process task?
You can change the execute process task to call a bat file and have it print out the values to a text file.
August 1, 2016 at 6:04 pm
Hi Lars
Thanks for taking the time to reply. Yes I did something similar. I added a variable to the StandardErrorVariable property on the Execute Process Task in order to ascertain the error from GPG command. I then write the value of the variable to a text file, which is how I determined it was returning the "No Secret Key" error.
The command for the decryption is pretty simple:- "GPG.exe --decrypt-files "C:\Temp\FileToDecrypt.txt.pgp" (The filename being just an example.) with everything after "GPG.exe" being provided by a expression defined against the Arguments property for the Execute Process Task. The expression is evaluating correctly as it works when run from VS and if it wasn't, then I would be getting a different error related to the file path being incorrect or similar.
The contents of the error file produced are shown below. The first line in error file created contains the ID of the Key with which the file was encrypted and it is correct. So it's definitely finding the file specified in the Argument. The second line of the error file produced shows the error.
gpg: encrypted with RSA key, ID XXXXXXXXXXXXXXX
gpg: decryption failed: No secret key
Cheers
Doug
August 10, 2016 at 6:30 pm
For anyone else that might end up having the same issue, here is a fix. Having said that, we are using fairly old versions of SQL/SSIS (2005) and Windows Server (2008) so this scenario may have been fixed in later versions.
Whilst I haven't discovered the cause of this issue, I managed to circumvent it by telling GPG.exe where to find the keyring folder. As mentioned, by default GPG.exe looks for the keyring folder (gnugp) in the current users profile folder (C:\Users\{Username}\AppData\Roaming\). When running an SSIS package from a SQL Job that would be the user running the SQL Server Agent service.
It would seem that in my specific scenario SSIS was loosing its identity when running the child package which contained my GPG.exe command and therefore could not find the keyring folder. I managed to find a parameter for GPG.exe called "--homedir" in which you can specify the location of the keyring folder. I guess you could parameterise it in SSIS by I just hard coded it for now and it is working fine. I've now moved the folder to a non-user specific location on the server and combined with the --homedir parameter has the benefit of making the solution user independent.
A few tricks I found with the --homedir parameter.
If your folder path has no spaces in it, then you must specify a "\" at the end of the path. e.g.
GPG.exe --homedir C:\Keyring\gnupg\ --decrypt-files C:\Temp\File.txt.pgp
If your folder path has spaces in it then you must enclose the path in quotes AND drop the "\" from the end of the path. e.g
GPG.exe --homedir "C:\Key ring\gnupg" --decrypt-files C:\Temp\File.txt.pgp
There is also length limitation on the path defined as the --homedir but I didn't bother trying to identify what it was precisely. My path is 36 characters long and is working although I had tried a much longer path which failed.
Happy days.
Doug
May 8, 2018 at 9:03 am
I am running into the same problem. I tried your homedir solution and it's actually made things worse. Here is the script I'm running:
--homedir "C:\Users\robinson\AppData\Roaming\gnupg\" --verbose --batch --yes --passphrase "<password>" --output "\\SX035\Concur Files\Incoming\Daily SAE File\cesimport.txt" --decrypt "\\SX035\Concur Files\Incoming\Daily SAE File\cesimport.txt.pgp"
When I run the script, this is the error output:
5/8/2018 9:44:10 AM
Log Job History (Concur - Process Incoming SAE File)
Step ID 1
Server SX035
Job Name Concur - Process Incoming SAE File
Step Name Execute Process Incoming File Package
Duration 00:00:04
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
gpg: keyblock resource `C:/Users/anthony.robinson/AppData/Roaming/gnupg" --verbose --batch --yes --passphrase 1east --output //SX035/Concur/secring.gpg': Invalid argument
gpg: keyblock resource `C:/Users/anthony.robinson/AppData/Roaming/gnupg" --verbose --batch --yes --passphrase 1east --output //SX035/Concur/pubring.gpg': Invalid argument
usage: gpg [options] [filename]
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.7001.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 9:44:10 AM
Error: 2018-05-08 09:44:14.57
Code: 0xC0029151
Source: Decrypt SAE File Execute Process Task
Description: In Executing "gpg2.exe" "--homedir "C:\Users\anthony.robinson\AppData\Roaming\gnupg\" --verbose --batch --yes --passphrase "Magenta*1east" --output "\\SX035\Concur Files\Incoming\Daily SAE File\cesimport.txt" --decrypt "\\SX035\Concur Files\Incoming\Daily SAE File\cesimport.txt.pgp"" at "C:\Program Files (x86)\GNU\GnuPG\", The process exit code was "2" while the expected was "0".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:44:10 AM
Finished: 9:44:14 AM
Elapsed: 3.718 seconds
When I don't include the homedir, the job runs fine under my user. I'm trying to get the job to run properly in SSIS, and it continues to have issues finding the secret keyring. I even placed the keyring in the directory of the account running SQL Server Agent and it still doesn't work properly.
Looking for any insight into this...!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply