SSIS with PGP Encryption Failed when execute with SQL Server Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS with PGP Encryption Failed when execute...

SSIS with PGP Encryption Failed when execute with SQL Server Job

Rate Topic

Display Mode

Topic Options

Author

Message

hsmanandhar 33101

Posted Thursday, February 21, 2013 10:20 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 5, Visits: 34

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

opc.three

Posted Thursday, February 21, 2013 11:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 6,696, Visits: 11,714

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1422735

hsmanandhar 33101

Posted Thursday, February 21, 2013 8:39 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 5, Visits: 34

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

joeroshan

Posted Thursday, February 21, 2013 9:10 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 572, Visits: 1,157

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

opc.three

Posted Thursday, February 21, 2013 9:26 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 6,696, Visits: 11,714

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1422863

hsmanandhar 33101

Posted Thursday, February 21, 2013 9:54 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 5, Visits: 34

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

hsmanandhar 33101

Posted Thursday, February 21, 2013 9:55 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 5, Visits: 34

When I changed success value from 0 to 2, it runs without any error but no encrypted file is then created.
Thanks.

Post #1422867

joeroshan

Posted Thursday, February 21, 2013 10:08 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:45 AM
Points: 572, Visits: 1,157

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

hsmanandhar 33101

Posted Thursday, February 21, 2013 11:42 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 5, Visits: 34

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

opc.three

Posted Friday, February 22, 2013 7:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 6,696, Visits: 11,714

hsmanandhar 33101 (2/21/2013)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1423044

« Prev Topic | Next Topic »

Permissions