SQL Server Performance

Encrypting file exported in DTS

Discussion in 'SQL Server DTS-Related Questions' started by bryan42, Jun 27, 2007.

  1. bryan42 New Member

    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
  2. satya Moderator

    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.
  3. bryan42 New Member

    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.
  4. satya Moderator

    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.

Share This Page