Alternative to xp_cmdshell

Question

Alternative to xp_cmdshell

nikhil.desai1

SSCommitted

Points: 1948
More actions
September 19, 2014 at 10:29 pm

#290098

Hi,
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.
I have used SSIS instead of xp_cmdshell(disabled status) to generate text , csv etc files. But it takes more time to generate the text files as compared to xp_cmdshell.
If there any alternative to xp_cmdshell then please let me know
Thanks and Regards,
Nikhil P Desai

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

xp_cmdshell does not generate any text file in itself, but you run something from xp_cmdshell that generates a text file.

You could just as well as run the same thing from a client program. If this is a stored procedure that you run from a job, you can instead run BCP or whatever it is from a CmdExec job.

If you want to keep the stored procedure, because it prepares some data for the export, you can write a CLR stored procedure which spawns a process and executes the command. The advantage over xp_cmdshell is that the CLR procedure runs a specific command which is hardcoded into it; it cannot be used for attacks.

But even better may be to write the file directly from C#. This takes a little longer time to develop, but gives you more flexibility if you need a format that BCP cannot offer.

In both these cases, the assembly needs to have EXTERNAL_ACCESS permission, which requires some red tape with signing and server-level permissions. But once you have set up, you have something which is better than today.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 2

nikhil.desai1 (9/19/2014)
Hi,
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.
I have used SSIS instead of xp_cmdshell(disabled status) to generate text , csv etc files. But it takes more time to generate the text files as compared to xp_cmdshell.
If there any alternative to xp_cmdshell then please let me know
Thanks and Regards,
Nikhil P Desai

Out of curiosity, when you say that SSIS takes more time than xp_cmdshell, what kind of difference are you talking about, seconds or minutes or hours? If the SSIS is running on the same host as the SQL Server, then there is the option of using shared memory access, should be as fast/quick as the xp_cmdshell script.

My thought is not to reinvent the wheel, SSIS is a quite stable, very configurable and secure platform for data extraction, it would take a very strong argument for not using it in this kind of scenario unless it's something that can be achieved using bcp (bulk copy program).

😎

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

xp_cmdshell should be disabled from the security risk aspect. When you have xp_cmdshell enabled you're allowing processes outside sql server to do changes to databases.

I usually do that for fast actions.

What I practice to do is when I need xp_cmdshell I enable the option, use it, and then disable. Your user must have the sysadmin privileges in order to do changes to sys.sp_configure, though.

If you need it to have enabled for a longer period, then you'd better use CLR or another alternative.

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Igor Micev (9/20/2014)
xp_cmdshell should be disabled from the security risk aspect. When you have xp_cmdshell enabled you're allowing processes outside sql server to do changes to databases.
I usually do that for fast actions.
What I practice to do is when I need xp_cmdshell I enable the option, use it, and then disable. Your user must have the sysadmin privileges in order to do changes to sys.sp_configure, though.
If you need it to have enabled for a longer period, then you'd better use CLR or another alternative.

Gosh... I hear a lot of people saying that and some are downright militant about it. But what does turning off xp_CmdShell buy you security wise or cost you if you turn it on? N-o-t-h-i-n-g. 😛 Yet it cost's you plenty if you don't turn it on because it's a great tool for trusted DBAs.

Unless you've made the horrible mistake of setting up a proxy so that non-SA-prived users can use it directly (and you must Never do that!), only those with SA privs can turn it on or off or use it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

Jeff Moden (11/5/2014)
Igor Micev (9/20/2014)
xp_cmdshell should be disabled from the security risk aspect. When you have xp_cmdshell enabled you're allowing processes outside sql server to do changes to databases.
I usually do that for fast actions.
What I practice to do is when I need xp_cmdshell I enable the option, use it, and then disable. Your user must have the sysadmin privileges in order to do changes to sys.sp_configure, though.
If you need it to have enabled for a longer period, then you'd better use CLR or another alternative.
Gosh... I hear a lot of people saying that and some are downright militant about it. But what does turning off xp_CmdShell buy you security wise or cost you if you turn it on? N-o-t-h-i-n-g. 😛 Yet it cost's you plenty if you don't turn it on because it's a great tool for trusted DBAs.
Unless you've made the horrible mistake of setting up a proxy so that non-SA-prived users can use it directly (and you must Never do that!), only those with SA privs can turn it on or off or use it.

Agree for the non-SA prived, and also for cases when the application is using one (or restricted number) SA user, in those cases not allowing the developers to possibly use it.

Igor Micev,My blog: www.igormicev.com

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

Powershell sounds good for this. You'll need to install SQLPS module, and then simply (NB - pseudocode):

Invoke-SqlCmd -ServerInstance servername -Database dbname -Query "EXEC proc" | Export-Csv filename.csv

or

Invoke-SqlCmd -ServerInstance servername -Database dbname -Query "EXEC proc" | Out-File filename.txt

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Gazareth (11/6/2014)
Powershell sounds good for this. You'll need to install SQLPS module, and then simply (NB - pseudocode):
Invoke-SqlCmd -ServerInstance servername -Database dbname -Query "EXEC proc" | Export-Csv filename.csv
or
Invoke-SqlCmd -ServerInstance servername -Database dbname -Query "EXEC proc" | Out-File filename.txt

Just a caution... Export-CSV puts double quotes around every field in the file and I don't believe it takes a parameter that can change that (though I could certainly be wrong there). Although I personally prefer such consistency in delimiters, it may not be what's needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

Another possibility is to use a Cmd Exec step in a job to do this. Technically, it's not xp_CmdShell and makes most folks feel much better.

BTW, this is one of the many reasons why I say that turning of xp_CmdShell does nothing for security. There are other ways that an attacker that gets in with SA privs can exploit your system. A self deleting job that also deletes the job history is really tough to spot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)