xp_cmdshell security risk: Any other option

Question

xp_cmdshell security risk: Any other option

nikhil.desai1

SSCommitted

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

#104245

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 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 1

There's the SP_OA procedures, but they would contain similar risks. Is this SQL 2000? If so, then the risk is that someone will run some malicious code on the Windows host. If it's later versions, then a) watch where you post, and b)there's a proxy you can set to limit permissions in the shell.

The risk is debatable and we have had some debates here, though I can't find it now.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

nikhil.desai1 (9/19/2014)
Hi,
As per SQL sever hardening xp_cmdshell is a security risk.

Actually, it's not a security risk if you do it correctly.

First, you should never give an individual proxy privs to it. The only proper way to use it for automation purposes is to write a stored procedure that uses it and have the stored procedure do all the work using deloused parameters.

Shifting gears, a lot of people consider just turning on xp_CmdShell to be a security risk and that's the wrong thing to worry about. What you really need to concern yourself with is keeping unauthorized people from logging in with SysAdmin or ControlServer privs because even if you have xp_CmdShell turned off, someone with those privs can turn it on and grab their payload before you even know it. Someone with those privs can also use other methods to do the same thing even without turning on xp_CmdShell. As part of "hardening", it's also very helpful to limit what the SQL Server and SQL Server Agent logins can see.

For more information on hardening SQL Server, please download the following white paper.

http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx

There's a lot of pre-2005 FUD surrounding xp_CmdShell that still permeates the community. It was a well deserved fear before then. Since 2005, it's a fear because people don't know how to do it correctly and make the heinous error of giving low-prived users privs to run it directly through a proxy. Note that nowhere in that white paper does it say to turn off xp_CmdShell and never turn it on. In fact, it clearly states that if you need to use it, use it. I'll add that you must use it properly.

That being said, I do endorse the idea of using SQLCLR for this type of thing not because it brings extra security to the table but because it gives the impression that it does. Just don't relax your guard on things like password policy and keeping unauthorized people (they'll usually try to get in as an existing login) from getting in with the previously mentioned privs because having stuff in SQLCLR will not prevent you from seeing your company in the news the morning after you're breached from not having the right kind of security.

--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)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

Jeff Moden (9/25/2015)
There's a lot of pre-2005 FUD surrounding xp_CmdShell that still permeates the community. It was a well deserved fear before then. Since 2005, it's a fear because people don't know how to do it correctly and make the heinous error of giving low-prived users privs to run it directly through a proxy. Note that nowhere in that white paper does it say to turn off xp_CmdShell and never turn it on. In fact, it clearly states that if you need to use it, use it. I'll add that you must use it properly.

No argument there. That is not to mean that, even if properly understood and secured, it would be an appropriate means of creating a CSV file. However, in terms of addressing the statement of it being insecure on its own, I definitely agree.

That being said, I do endorse the idea of using SQLCLR for this type of thing not because it brings extra security to the table but because it gives the impression that it does.

To clarify something here, there is a difference in the security options between SQLCLR and xp_cmdshell that should not be overlooked, even if not always applicable: SQLCLR code has the ability to access external resources using Impersonation, which changes the security context of the process to the Windows Login that is executing the T-SQL that points to the SQLCLR object. This does not work for SQL Server Logins. But whereas xp_cmdshell will always use the security context of the Log On account of the SQL Server process for those in the sysadmin server role, and if configured, the xp_cmdshell proxy account for all others, this ability of SQLCLR to Impersonate the Login running the code means that network and file system security can be more finely tuned.

I have a walk-through of this in the following article: Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)[/url].

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

Sergiy SSC Guru Points: 110208 More actions · Answer 5

The thing is - the security risk coming from inappropriate setup of privileges for correlated accounts is there all the time, no matter if you're using xp_cmdshell or not.

Not using it for routine tasks does not improve security by a tiniest bit.

Disabling xp_cmdshell is a hoax. It is impossible to actually disable it.

It is always enabled. ALWAYS.

Therefore there is no point (in terms of security) in replacing xp_cmdshell with another utility. It's only wasting time and effort.

Just make sure that Windows accounts for SQL Server and Agent have appropriate privileges within the domain: sufficient for the job they are doing and not any more.

_____________
Code for TallyGenerator