Decrypting PGP files from a SSIS 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.
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.