Previously I wrote about my efforts to automate the decryption of files with SSIS using the gpg2.exe which comes as part of the GnuPG package. The original article is here
However after deploying the working solution into production, to be run as a scheduled task, I found out that this package and solution still had some issues. I found that it was behaving rather differently when it was deployed into the production environment as opposed to running in the BIDS environment. When executing the exact same code in production which worked processing the exact same files in development (and I mean the exact same, same account, same files, same everything) I got error which looked like this (sanitised for security)
Error: 2012-03-13 11:16:07.10 Code: 0xC0029151 Source: run decrypt Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg2.exe" "--batch --passphrase-fd 0 --decrypt-files [myfilename]" at "", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:16:02 Finished: 11:16:07 Elapsed: 4.609 seconds. The package execution failed. The step failed.
So I was again getting error code 2 which I had previously, for which the –batch switch had previously resolved the issue in development. So the error code was the same, but the reason obviously had to be different now. This required a little more investigation to get to the bottom of. Firstly I ran Process Monitor, which is often my first port of call in such scenarios, to check whether I was hitting some obscure permissions errors when running in live with the SQL Agent. It turned out totally clean (As an aisde I had done the same when initially installing GnuPG to resolve and issue that it couldn’t access a temp directory it required to do decryption).
A bit of research through the web and the full documentation of GnuPG left me using a further switch:
which allowed me to look at some of the status messages from the output which were previously being swallowed by the SSIS task when run in production. There was SSIS logging enabled but it wasn’t getting anything back from the gpg2 executable beyond the status code.
I used a couple of different versions of this switch which looked like this
gpg2 --batch --status-fd 2 c:\gk\test\output.txt --decrypt-files test.gpg
which outputs the status messages to c:\gk\test\output.txt, or you can do this
gpg2 --batch --status-fd 2 --decrypt-files test.gpg
which outputs the messages to the console
Either way you end up with the following output (again slightly sanitised)
[GNUPG:] FILE_START 3 test.gpg [GNUPG:] ENC_TO [hexstring] 1 0 [GNUPG:] USERID_HINT [hexstring] [mykeyname] (mykeyname) <email@example.com> [GNUPG:] NEED_PASSPHRASE [hexstring] [hexstring] 1 0 [GNUPG:] GOOD_PASSPHRASE [GNUPG:] BEGIN_DECRYPTION [GNUPG:] PLAINTEXT 62 [integer] test_.txt [GNUPG:] PLAINTEXT_LENGTH 901226 [GNUPG:] DECRYPTION_OKAY [GNUPG:] GOODMDC [GNUPG:] END_DECRYPTION [GNUPG:] FILE_DONE
but unfortunately this still didn’t give me anything to go on, as it still worked in the test environment, but not in the production one. Eventually by playing with the logging levels and these switches in production I got the details out in the SSIS log which contained this ket string
gpg: decryption failed: No secret key
I then realised that I was being an idiot and that the service account that I was running the SQL Agent under did not have the certificate registered under that userid. I had only imported the certificate into Kleopatra for the development userid I logged in with and not for the service account. I simply imported the certificate to the service account profile and then everything worked. This meant that the original instructions and code were valid, but I thought I’d put this post up in case anyone did the same stupid thing as me. It’s worth remembering that the certificates are by default imported at a user level into Kleopatra.