Encrypting Files in SSIS

Recently I was working with a client who wanted to pull some information from their database, insert it into an excel file, encrypt that file, and send it to the client via FTP. They wanted an automated process that could run on a schedule, so I wrote an SQL Server Integration Services (SSIS) project to do this.

I created this package and it was running perfectly from BIDS and from the SSIS catalog. I scheduled the job and let it run. A few months later, the client came back and told me that the job had stopped running successfully. I logged in and reran the job from BIDS – that worked. When I tried to run the job from the catalog I ran into this error:

Encrypt the file: Error: In executing “C:\gpg2\gpg2.exe” “–trust -model always –output *Destination File* -r *Encryption Key Name* –encrypt *Source File*” at “C:\gpg2”, The process exit code was “2” while the expected was “0”.

*actual file names/key name removed

I tested the package again within BIDS, and again it ran without a problem. I tried redeploying it and testing again. Again it failed. So, I googled it. I tried a few suggestions: running from a command line instead of the catalog; I changed from using variables for file names to hard-coding them; and I thought about changing the success value to 2. I didn’t do this last part because it wasn’t actually doing what I wanted it to do – encrypting the file. And also because that’s just a bad idea in general.

And then, I found a reply to thisĀ post that mentioned something about permissions. I asked the client if anything had changed recently with permissions and found they had recently removed some old accounts to firm up security – that’s a great thing! However, it means that I needed to check the permissions of the SQLServerAgent on the machine.

After checking, (and working with Dad) I found that SQLServerAgent did not have any permissions to the gpg2 folder in the file system. I added read/write permissions to the directory and ran the job again. It failed again. I then updated the permissions on the folder to “Full”. This allowed the job to complete successfully!

Moral of the story: locking down permissions is great and should be done – however, keep in mind the ripple affects. If you’re getting a similar error from a job in SSIS, check the permissions for SQLServerAgent.

Leave a Reply

Your email address will not be published. Required fields are marked *