Home Forums SQL Server 7,2000 Security xp_cmdshell security risk: Any other option RE: xp_cmdshell security risk: Any other option

  • nikhil.desai1 (9/19/2014)


    I our SQL server project we are using xp_cmdshell to generate .txt or .csv file. As per SQL sever hardening xp_cmdshell is a security risk. Is there any alternative to xp_cmdshell to generate .csv or text file.

    Hi there. How are you using xp_cmdshell to create csv / txt files? Are you concatenating the result fields into a string variable and then calling xp_cmdshell to create or append a file with that?

    Yes, there is a safe alternative: SQLCLR. You can create a function or stored procedure (if the query is writing to a temp table then it is probably better to go with a stored procedure) that will:

    • execute the query via SqlCommand
    • IF you have A LOT of rows, then use FileStream to open a file and cycle through SqlDataReader to write each row of the result set to the file as you fetch it (very scalable), or
    • IF it is not many rows, then cycle through SqlDataReader, fetching each row into an ArrayList of strings, and then write all of it at once to a file using File.WriteAllLines(string array) (less complex but also less scalable)

    This only requires that you 1) enable "CLR Integration", and 2) set the Assembly to EXTERNAL_ACCESS. You do that by:

    EXEC sp_configure 'clr enabled', 1;

    RECONFIGURE;

    And then:

    • Sign the Assembly
    • in [master], create an Asymmetric Key from the DLL / Assembly
    • in [master], create a Login based on that Asymmetric Key
    • GRANT the new Login the EXTERNAL ACCESS ASSEMBLY permission
    • either CREATE or ALTER the Assembly WITH PERMISSION_SET = EXTERNAL_ACCESS

    For more information on SQLCLR (including walk-throughs of the security levels), please see the series I am writing here on SQL Server Central: Stairway to SQLCLR[/url].

    Also, if you are interested in this functionality but not doing the coding, etc to get it, then there is a stored procedure named DB_BulkExport in the SQL# library that does exactly this. Please note that I am the creator of SQL#, and that while there is a Free version, the DB_BulkExport stored procedure is only available in the Full version.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR