0

I am currently working at one of my clients SQL-Server environment. A year ago I was asked to implement a .csv report generation in a Maintenance Plan I was working on. To do so, I have enabled xp_cmdshell option on the server and used bcp utility. However, a programmer from the other company recently added a stored procedure in which he enables xp_cmdshell option at the beginning of procedure and disables it (sets to 0) at its end. This feature have caused my Maintenance Plan to fail at the report generation step.

Since I'm not allowed to change his code I was asked to include similar enabling/disabling feature in my scripts. Here is example of code I found and used:

declare @prevAdvancedOptions int;
declare @prevXpCmdshell int;

select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options';
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell';

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 1;
    reconfigure;
end;

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 1;
    reconfigure;
end;


--- doing some work here ---


if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 0;
    reconfigure;
end;

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 0;
    reconfigure;
end;

The thing is I'm not completly sure about the safety of this solution but I don't want to redesign the code I wrote. I am also using xp_cmdshell feature in some other scripts.

The question is: Is it possible that execution of Script A (used by my maintenance plan) can be affected with disabling xp_cmdshell in Script B (used in the stored procedure) when they are executed at the same time?

I have done some research about SQL-Server Query Processor to understand how it works and executes the queries but could not find an answer to this question.

I will appreciate any of your suggestions!

Wojciech Czerniawski
  • 1
  • 2
  • *Enabling* can be harmful. Period. Why do it at all? Why use `bcp` from *inside* the server? Why not use SQL Server Agent, or SSIS? You don't need bcp to *export* data to a file – Panagiotis Kanavos Jun 27 '17 at 13:10
  • `xp_cmdshell` is not an *additional* security risk if properly managed. Twiddling the option selectively, based on whether a particular piece of code happens to be running, adds complexity for no increased security -- any security risk posed by `xp_cmdshell` could be exploited by an attacker by waiting for the moment when it's enabled. I realize it's not your code to change, but the other guy is still Wrong. This is applying a global solution to a local problem, with all the trouble that invites. – Jeroen Mostert Jun 27 '17 at 13:13
  • To answer your question directly: yes, if multiple pieces of code are toggling it on and off and expecting one or the other to be the case, you *will* run into problems. This is a simple consequence of the option being a *server-level option*. At any given point in time, it's on or off, for everyone. You don't need to know the internals of the query processor to realize this is problematic. – Jeroen Mostert Jun 27 '17 at 13:15
  • If `xp_cmdshell` is really necessary (and that should be established first) then managing its privileges should be done with the proxy account. If you need temporary access, this can be arranged locally by temporarily granting SQL accounts permission to execute `xp_cmdshell` (and revoking this permission afterwards). Event the accounts themselves can be temporary, though that's more work to set up. These security layers are far more effective than toggling the global option, and they won't cause scripts to clash with each other. – Jeroen Mostert Jun 27 '17 at 13:38
  • There are a lot of ways to export data without weakening security. You can add a query step to an agent job and direct the output to a file. You can use the Export wizard to quickly create an SSIS package that you can reuse. Or you can use SSDT to transform data and export it to anything from a CSV to another database to Hadoop – Panagiotis Kanavos Jun 27 '17 at 13:42
  • I worked for a company that used Lawson (ERP and financial accounting software) who had native xp_cmdshell code baked in. At first I was like everyone else regurgitating msdn and all...but they seemed to manage it OK. The guy flipping it off and on in code doesn't seem like a very smart idea if it hacks up other processes - what if one, or either script fails? Or one runs concurrently and then shuts it off? – Lee Oct 13 '17 at 21:02

0 Answers0