-1

I am trying to use gpg2 directly from sql to encrypt a xml file that I will be sending to an outside vendor. I can create the xml file and ftp without errors, but I am running into an issue when trying to use the gpg2 commands to encrypt the file.

I have a stored procedure that echo's commands into a .txt file and then I execute the file with xp_cmdshell. I designed it this way so that I could create the command dynamically depending on what the file name is that you wish to encrypt.

This is the command that I am writing to the text file so that I can execute it. This command works fine when calling from the command line on the SQL box, but it does not work within xp_cmdshell.

"C:\Program Files (x86)\GNU\GnuPG\gpg2" --recipient FTPUser --output C:\FTP\Outgoing\test.gpg --batch --encrypt C:\FTP\Outgoing\SomeFile.xml

Here are the messages:

gpg: FTPUser: skipped: No public key gpg:

C:\FTP\Outgoing\SomeFile.xml: encryption failed: No public key

The gpg4win(2.2.5) program is installed directly on my SQL 2008R2 box and I have gone through the process of generating keys, and then signing them using the FTPUser account. I was able to get the messages dumped into a temp table while executing the xp_cmdshell command in SQL. The results are the two messages above.

Is there another gpg2 command that I need to be using when calling from SQL? I have tried searching the interwebs, but it has proved futile.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
MikeMc
  • 1
  • Just guessing, maybe when executed with `xp_cmdshell` it can't reach required environment variables. Check manual what environment variables GPG2 needs (like PATH or so on) and provide them `SET name=...` before executing script. – Lukasz Szozda Aug 31 '15 at 13:01
  • Run `SET | more` from command line and cmdshell and compare – Lukasz Szozda Aug 31 '15 at 13:12

1 Answers1

0

I have figured out the issue. We have a separate windows account that runs our SQL Server service. Since that account was the one that was initiating the command, I had to make sure that I set up the keys on the db server under that account. When I first set it up, I did it under the administrator account. Since that is not the same account that xp_cmdshell was using to initiate the command, it could not find the public key under the account. After adding the keys to the proper account, everything functioned properly.

MikeMc
  • 1