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:
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:
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR