We help IT Professionals succeed at work.
Troubleshooting Question

PGP Encryption Failing in SQL Agent Job When User Not Logged In

26 Views
Last Modified: 2020-08-24
Having issues with running an SSIS job with SQL Server Agent. The part that is failing is a simple file encryption process but that part will only work when the user that the job is running under is logged into the server. The SQL server is 2017 and the PGP flavor is GnuPG. I can run the job on the server from a command line when logged in. I can run the job from Visual Studio as any user. I am thinking the system cannot access the keyring if the user is not logged in.

I have tried using both a process execution task and writing to the cmd.exe in a script task. Each of these works fine if the user is logged in. I did see somewhere that GnuPG was supposed to be supporting --recipient-file and I tried exporting the .asc file out of the ring, but I get an error that the --recipient-file option is invalid.

Any ideas for a different PGP installation that might be less profile dependent? Lots of flavors out there. Thanks.
Comment
Watch Question

Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
Try to create a proxy account with that specified user for SSIS packages:
then in job definition select this account.

Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence Analyst

Author

Commented:
Thanks, Máté . I did just that. The problem is that this only works if the user in the proxy is logged in to the server. If no one is logged into the server or another administrative account is logged in, which is usually the case because the proxy account is not a "real" user, then the process fails. I am not sure but I believe this is because the key ring is under the user profile and that is not available unless the user is logged in. I am looking for a solution to have a key ring available outside of the user's profile and then the code for using that instead of the default key ring. Thanks.
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
I don't know what is the purpose of this encription but if it cannot be used when the user is not logged in then you should use another method.
You should inplement it in .net in a Script Task component or implement it outside the SSIS.
Sr. Business Intelligence Analyst
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.