SSIS - Decrypting PGP files from a child package

  • 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.

  • 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.

  • 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

  • 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

  • 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