I have used Kleopatra to generate a pgp key.
In SQL Server I am running an SSIS package that should decrypt files using the above key. The SSIS packages runs as ImportFiles which has the identity of the windows user NetworkSQLUser because it has to talk to another server to get the file.
When I first ran the package I got the following error:
Executed as user: VS2\NetworkSQLUser. gpg: encrypted with ELG key, ID XXXXXXXX gpg: decryption failed: No secret key Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:50:29 p.m. Error: 2014-01-23 15:50:29.95 Code: 0xC0029151 Source: Decrypt Transaction File Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg2.exe" "--batch --passphrase-fd 0 --decrypt-files \vs3\FTP\Outgoing\20140122_001.TRN.gpg" at "", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:50:29 p.m. Finished: 3:50:29 p.m. Elapsed: 0.782 seconds. The package execution failed. The step failed.
I read an article that says in order to fix this problem do the following:
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.
I added the User-ID of NetworkSQLUser to the key in Kleopatra but I'm not 100% sure that's right!
I then ran the package again and got the following error:
Executed as user: VS2\NetworkSQLUser. gpg: keyring
c:/gnupg/secring.gpg' created gpg: keyringc:/gnupg/pubring.gpg' created gpg: encrypted with ELG key, ID XXXXXXXX gpg: decryption failed: No secret key Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:17:16 p.m. Error: 2014-01-23 17:17:17.40 Code: 0xC0029151 Source: Decrypt Transaction File Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg2.exe" "--batch --passphrase-fd 0 --decrypt-files \vs3\FTP\Outgoing\20140122_001.TRN.gpg" at "", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:17:16 p.m. Finished: 5:17:17 p.m. Elapsed: 0.735 seconds. The package execution failed. The step failed.
I have also tried running the dtsx package from the GnuPG folder.
I'm at a loss as to where to go from here.
If I have missed any information (my first time with SSIS, PGP and certs) then let me know.
