Search code examples
sql-server-2012sqlcmd

gpg encryption in xp_cmdshell

I am trying to encrypt a .csv file using gpg in xp_cmdshell command in sql server 2012. When I use gpg through xp_cmdshell, it says gpg is not recognized as an internal or external command. But this works perfectly on windows cmd. How should I configure my sql server to accept this command. Please advice. 

select @encrypt = 'gpg -e -r ' + @sEncryptionKey+ ' '+ @sPath + @tempdataFolder+'\'+ @sFileName 

exec master..xp_cmdshell @encrypt
Solution

  • To remedy the error you are indicating above, simply add the full path to gpg.exe in your code.

    select @encrypt = 'c:\gpg\gpg.exe -e -r ' + @sEncryptionKey+ ' '+ @sPath + @tempdataFolder+'\'+ @sFileName 

exec master..xp_cmdshell @encrypt

    For comparison, this is how I have done this in the past via SSIS; be sure you provide the exact full path to your executable... we are using gpg4win (gpg2.exe) in this example.

    You may also want to wrap each parameter in " " to handle file names/paths with spaces better.

    DECLARE @sKeyEmail VARCHAR(100), @sInFile VARCHAR(100), @sOutFile VARCHAR(100), @SQL VARCHAR(1000)

SET @sInFile = 'c:\temp\somefile.xls'
SET @sOutFile = 'c:\temp\somefile.xls.gpg'
SET @sKeyEmail = 'user@domain.com'


SET @SQL = 'EXEC master.dbo.xp_cmdshell ''C:\Progra~1\GNU\GnuPG\gpg2 -o ' +@sOutFile+ ' -e -r ' +@sKeyEmail + ' ' +@sInFile+ ''''

PRINT @SQL
-- EXEC sp_execute @SQL