Encrypting file exported in DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Encrypting file exported in DTS

An existing DTS selects rows from various tables and outputs them to a CSV file. I need to use PGP (the command line version) to encrypt that output file. The catch here is that I’m required to do this without ever writing an unencrypted file. So, how can this be done? May I take the output of a stored procedure and send the results directly to PGP with xp_cmdshell? How does that work? Or, could I use one stored procedure to capture the output of a select statement and send all that to PGP with xp_cmdshell? thanks,
Bryan
Probably you need to ask this question in PGP forums, as you want to handle this on PGP side. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
The PGP part is easy using GnuPGP as it can handle input from either a file or from another program. The problem is getting the data from the database to PGP. OSQL works well and sends its output directly to GnuPGP. However, formatting the output columns doesn’t work too well. I couldn’t find a way to output only the data. Normal OSQL output includes the OSQL command, funny characters, fields padded with extra spaces up to the defined length of each field, and a total number of rows at the end. The OSQL command I used is this: osql /S server /U sa /P pwd -d database -Q "exec prc_Export" -h-1 -s "|" -w 65536 BCP works and with some tweaking of the the format file the output looked right. However, BCP only outputs to a file, so instead of encrypting directly I must create an output file and then run GnuPGP on that file. It works, but it’s not as clean as I hoped. The BCP command is this: bcp "exec dbname.dbo.prc_Export" queryout export.txt -f bcp.fmt -t"|" -Usa -Ppwd -S server If anyone comes up with any way to get the OSQL option to work with better formatting abilities, please post it here.
I believe GnuPGP is third party tool and in any caes you have to deploy own code to encrypt such data, otherwise use SQL native encryption method which is better in SQL 2005 than in previous versions. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>