Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS with PGP Encryption Failed when execute with SQL Server Job Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 7:37 PM
Points: 5, Visits: 49
I developed SSIS Package with Execute Process Task which encrypt the text file into PGP encrypted file.
For PGP encryption with Public Key I processed following steps:
Installed gnupg software for gpg encryption.
Import the public key using command :
C:\Program Files (x86)\GNU\GnuPG\gpg --import test.asc(public key file).
Giving trust to ultimate

Setup following properties in Execute Process Task's Process Properties :
--Executable : C:\Program Files (x86)\GNU\GnuPG\gpg.exe
--Arguments : "--recipient KeyRing --armor --output "+ @[User::FileOutDir] + @[User::EncryptedFileName] +" --encrypt "+ @[User::FullFilePath]
Where -- KeyRing is user id of the Public Key

This works fine when I run the package from Bids and created encrypted file as desired.

But when I tried to run this SSIS package from SQL Server Job, It gave the following error:

Source: Execute Process Task Execute Process Task Description: In Executing "C:\Program Files (x86)\GNU\GnuPG\gpg.exe" "--recipient KeyRing --armor --output D:\SSIS_EXPORT_IMPORT_FILES\\Test_Export_SSIS\File_Out\Test_File_130221094201.gpg --encrypt D:\SSIS_EXPORT_IMPORT_FILES\Test_Export_SSIS\File_Out_BackUp\Test_File_130221094201.txt" at "D:\SSIS_EXPORT_IMPORT_FILES\Test_Export_SSIS\File_Out", The process exit code was "2" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:41:59 PM Finished: 9:42:02 PM Elapsed: 3.167 seconds. The package execution failed. The step failed.

Can anyone give me suggestion how can I perform the task successfully in SQL Server Job??
Post #1422704
Posted Thursday, February 21, 2013 11:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
1. Make sure gpg is correctly installed on the server in the location you're referencing.
2. Log into the server as the SQL Agent service account and accept the key as 'trusted' from the command line, i.e. run the command line the SSIS package tried to run and then answer any interactive prompts gpg presents.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422735
Posted Thursday, February 21, 2013 8:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 7:37 PM
Points: 5, Visits: 49
thanks you for the post.

As the package successfully execute and create encrypted file when runs from BIDS, the gpg installation and path is correct.
Only the problem is it gives error when scheduled to run the package from SQL Job.
Post #1422854
Posted Thursday, February 21, 2013 9:10 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
The successvalu property foe execute process task is configurable. Even though the default is 0, you should change this according to the process you are using.

You should refer the manual for your application to see the expected exit code for succes and configure that in your Execute process task


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1422861
Posted Thursday, February 21, 2013 9:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
Are you saying that you are developing in BIDS on the same machine where SQL Server Agent is running? What account is the SQL Agent service running as? Log into the machine as that account and run the gpg command line to ensure the account can use gpg properly.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422863
Posted Thursday, February 21, 2013 9:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 7:37 PM
Points: 5, Visits: 49
Thnaks,

I run the command line for gpg with user1 and it creates encryption file and no error.
I run the JOb with the same user but it gave out error.
Post #1422866
Posted Thursday, February 21, 2013 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 7:37 PM
Points: 5, Visits: 49
When I changed success value from 0 to 2, it runs without any error but no encrypted file is then created.
Thanks.
Post #1422867
Posted Thursday, February 21, 2013 10:08 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
can this help?

http://stackoverflow.com/questions/6350442/gpg-error-code-2


Also make sure the succes value is the correct one. Most process has 0 for success. I gave you that suggestion only because you said the file was getting created


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1422871
Posted Thursday, February 21, 2013 11:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 31, 2014 7:37 PM
Points: 5, Visits: 49
I solved my issue.
Steps I made for Job run successful :
Create a Credential in SQL Server with the windows User Name.
Create Proxy with this Credential.
Run SSIS with Proxy.

This solved my issue and now generate encrypted file with SQL Server Job.

Thank you all who have help me a lot.
Post #1422890
Posted Friday, February 22, 2013 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
hsmanandhar 33101 (2/21/2013)
I solved my issue.
Steps I made for Job run successful :
Create a Credential in SQL Server with the windows User Name.
Create Proxy with this Credential.
Run SSIS with Proxy.

This solved my issue and now generate encrypted file with SQL Server Job.

Thank you all who have help me a lot.

That sounds about right. What is the SQL Server Agent service account and what account did you use as the proxy?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1423044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse