October 25, 2011 at 8:07 am
So far there are 43 responses to the poll with a breakdown as follows:
60.47% of responses were I do too. There are so many different ways to get things done in a more secure way.
27.91% responded Yes, but I can give you a good example of when you HAVE to use it.
9.3% responded What's wrong with it?
and 1 person, 2.33%, responded What's xp_cmdshell? 2.33%
I would love to hear from more of the folks included in the 27.91% who say they must use xp_CmdShell. What are you doing in your database that requires you to use xp_CmdShell?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 25, 2011 at 8:35 am
opc.three (10/25/2011)
So far there are 43 responses to the poll with a breakdown as follows:60.47% of responses were I do too. There are so many different ways to get things done in a more secure way.
27.91% responded Yes, but I can give you a good example of when you HAVE to use it.
9.3% responded What's wrong with it?
and 1 person, 2.33%, responded What's xp_cmdshell? 2.33%
I would love to hear from more of the folks included in the 27.91% who say they must use xp_CmdShell. What are you doing in your database that requires you to use xp_CmdShell?
I don't have to use xp_cmdshell, I could find other ways to get stuff done without using it. I just think its pretty handy and doesn't seem to cause problems. I could certainly avoid its use if given a compelling reason. Are there any compelling reasons not to use xp_cmdshell?
October 25, 2011 at 9:14 am
patrickmcginnis59 (10/25/2011)
opc.three (10/25/2011)
So far there are 43 responses to the poll with a breakdown as follows:60.47% of responses were I do too. There are so many different ways to get things done in a more secure way.
27.91% responded Yes, but I can give you a good example of when you HAVE to use it.
9.3% responded What's wrong with it?
and 1 person, 2.33%, responded What's xp_cmdshell? 2.33%
I would love to hear from more of the folks included in the 27.91% who say they must use xp_CmdShell. What are you doing in your database that requires you to use xp_CmdShell?
I don't have to use xp_cmdshell, I could find other ways to get stuff done without using it. I just think its pretty handy and doesn't seem to cause problems. I could certainly avoid its use if given a compelling reason. Are there any compelling reasons not to use xp_cmdshell?
How did you vote in the poll?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 25, 2011 at 9:35 am
The poll does seem a bit skewed towards the positive,
because the only answer if you 'do not cringe' is to pick 'whats wrong with it'
where people's answer will be SECURITY... though this has been proven to be a non-issue if you have you server setup securely..
so it would be better if there was simply a 'no' answer..
October 25, 2011 at 9:46 am
opc.three (10/25/2011)
patrickmcginnis59 (10/25/2011)
opc.three (10/25/2011)
So far there are 43 responses to the poll with a breakdown as follows:60.47% of responses were I do too. There are so many different ways to get things done in a more secure way.
27.91% responded Yes, but I can give you a good example of when you HAVE to use it.
9.3% responded What's wrong with it?
and 1 person, 2.33%, responded What's xp_cmdshell? 2.33%
I would love to hear from more of the folks included in the 27.91% who say they must use xp_CmdShell. What are you doing in your database that requires you to use xp_CmdShell?
I don't have to use xp_cmdshell, I could find other ways to get stuff done without using it. I just think its pretty handy and doesn't seem to cause problems. I could certainly avoid its use if given a compelling reason. Are there any compelling reasons not to use xp_cmdshell?
How did you vote in the poll?
Ok I guess I would be one of the "whats wrong with it" folks so I shouldn't have replied. Sorry about that!
October 25, 2011 at 9:51 am
steveb. (10/25/2011)
The poll does seem a bit skewed towards the positive,because the only answer if you 'do not cringe' is to pick 'whats wrong with it'
where people's answer will be SECURITY... though this has been proven to be a non-issue if you have you server setup securely..
so it would be better if there was simply a 'no' answer..
That may be so, the OP has definitely shown where their preferences lie.
I contend that if you think it through there is never a reason you must use it. There is always an alternative, and to that end, I believe you end up with a better application design with less dependencies built into your system if you avoid xp_CmdShell. Maybe it is me who is not thinking it all the way through...I will concede that, however no one has shown comprehensive reasoning for picking Yes, but I can give you a good example of when you HAVE to use it. that justifies they had no other option. They being the primary application designer, not the implementer of a third-party tool as in the SAP example.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 25, 2011 at 10:22 am
patrickmcginnis59 (10/25/2011)
...Ok I guess I would be one of the "whats wrong with it" folks so I shouldn't have replied. Sorry about that!
No need to apologize. It's just a discussion, and a good one at that! From building systems that use it (pre-SQL2005), inheriting and supporting systems that use it, talking with people that have built systems that use it and reading peoples experiences online, my opinion is that the majority of people who chose xp_CmdShell chose it because they did not stay in design long enough discover alternatives before setting out to use it in code. To be fair, some did perform due diligence. Of those some decided they did not have the skillset to implement one of the alternatives and did not want to invest the time to venture outside of T-SQL to get there. And further still, some did all of the above, and possessed a skillset necessary to implement one of the alternatives and still chose xp_CmdShell.
Many people think they HAVE to use xp_CmdShell, but I HAVE to disagree. Using it or not using is most certainly a choice. Security is a concern, in practice, because many who choose to use it don't bother investing the time to secure their systems, and that is a shame in and of itself. It indirectly contributes to giving xp_CmdShell a bad name and feeds myths about it containing inherent security vulnerabilities, which it does not. Is that xp_CmdShell's fault? No, but it's my responsibility to pay attention to that aspect and it does shape my position. I have many reasons why I choose to design away from it just like the people who have reason to choose to design directly towards it, and I'll leave it at that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 9, 2012 at 5:12 am
Once you've gone through the trouble of securing xp_cmdshell, setting up a proxy etc, in my view, one of its biggest benefits, is plain old simplicity.
In our case, we use SSRS to generate reports, which we want automate from batch. The flat out simplest way to do this is running a parameterized rs.exe call. This makes sense to wrap in an xp_cmdshell call.
Now I realise all this can be done via managed code, etc, but frankly, when things go wrong, I can go to the command line myself, run reports command manually, and see what's happening.
Best of all, it's a single line in SQL.
Is this an example of all that is wrong with xp_cmdshell?
Mike
November 9, 2012 at 7:34 am
mike.renwick-894639 (11/9/2012)
Once you've gone through the trouble of securing xp_cmdshell, setting up a proxy etc, in my view, one of its biggest benefits, is plain old simplicity.In our case, we use SSRS to generate reports, which we want automate from batch. The flat out simplest way to do this is running a parameterized rs.exe call. This makes sense to wrap in an xp_cmdshell call.
Now I realise all this can be done via managed code, etc, but frankly, when things go wrong, I can go to the command line myself, run reports command manually, and see what's happening.
Best of all, it's a single line in SQL.
Is this an example of all that is wrong with xp_cmdshell?
Mike
In my humble opinion, yes. You have essentially turned your SQL Server into a command line terminal, for which it was not built, and are obfuscating security to boot. Resist the temptation to turn your SQL Server into an application server by using it as a conduit to get to a command line. When you, or anyone else, uses xp_cmdshell to execute a command on the OS it becomes impossible to know who actually ran that command using traditional Windows monitoring software. The Windows activity would need to be correlated to a SQL Server event, and even then the command issued to the OS via xp_cmdshell can be hidden.
A better option would be to setup PowerShell Remoting and initiate a shell that way, from your local machine, where you can issue commands on the remote server.
When you feel like you want to log into the server to run rs.exe open PowerShell, not SQL Server.
Just reading the above statement as I was typing it, I think it reads very clearly as to which option makes more sense when you consider what each technology was designed to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 9, 2012 at 7:50 am
Okay, that's interesting. So literally instead of using a cmdshell, initiate a powershell, even for running an executable off the command line? I have used powershell for a few things, but clearly need to read up on what other differences there are on the auditing side.
We don't have a separate reporting or application server in this instance - just the one server.
We have the databases setup, and want to, as simply as possible, automate the running of reports on that server.
November 9, 2012 at 7:59 am
mike.renwick-894639 (11/9/2012)
Okay, that's interesting. So literally instead of using a cmdshell, initiate a powershell, even for running an executable off the command line?
Precisely, and a Remote PowerShell session at that. Think of it as a shell prompt open in a window on your local machine, where the prompt is executing commands for you on the remote server. If you have ever used telnet or ssh to access a remote server you'll be very familiar with the concept and the shift in thinking that goes along with it.
I have used powershell for a few things, but clearly need to read up on what other differences there are on the auditing side.
We don't have a separate reporting or application server in this instance - just the one server.
We have the databases setup, and want to, as simply as possible, automate the running of reports on that server.
xp_cmdshell is tempting to implement however barring non-technical barriers (like stubborn managers or entrenched admins) in every case I have actually encountered there are more secure, more appropriate tools available to get the job done. I look to leave xp_cmdshell disabled on all new instances I provision and diligently work with stakeholders to disable xp_cmdshell on instances I inherit where it is already enabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 9, 2012 at 8:10 am
Ok, some further reading has left me a bit confused here. Basically running a batch, which can be done by a user, just involves running the single stored procedure he has rights to run.
It then runs the processing, loads feeds, and does whatever it needs to do, before spitting out reports to a known location.
To retool this to stop using xp_cmdshell, presumably it would need to be initiated/handled from powershell, i.e. there isn't a way to run powershell from a stored proc (without resorting to xp_cmdshell !!)
The only other thing I could think of would be to wrap it in a sql server agent job, and then wire up the user to be able to kick off that job? I'm guessing that's the right approach?
November 9, 2012 at 8:22 am
Heh, well, if the developer baked the use of xp_cmdshell directly into the T-SQL process then yes, it will take a bit of work to refactor the call to the executable out of the T-SQL. This is another example of how insidious xp_cmdshell can be, and it can be very difficult to get to a place where it can be disabled again, but worth the effort.
I would start by rewriting the process as a PowerShell script. Have PowerShell play the role of the 'controller' in the process, not a T-SQL module. You can easily call stored procs from PowerShell and obviously executables as well. That will alleviate the need for xp_cmdshell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 9, 2012 at 8:40 am
Well thanks for the hints!
"Powershell for a better pie!"
November 9, 2012 at 8:44 am
mike.renwick-894639 (11/9/2012)
Once you've gone through the trouble of securing xp_cmdshell, setting up a proxy etc, in my view, one of its biggest benefits, is plain old simplicity.In our case, we use SSRS to generate reports, which we want automate from batch. The flat out simplest way to do this is running a parameterized rs.exe call. This makes sense to wrap in an xp_cmdshell call.
Now I realise all this can be done via managed code, etc, but frankly, when things go wrong, I can go to the command line myself, run reports command manually, and see what's happening.
Best of all, it's a single line in SQL.
Is this an example of all that is wrong with xp_cmdshell?
Mike
We're, apparently, cut from the same cloth. In fact, I call PowerShell from T-SQL for the very same reasons. 😀
The other take on it, that Orlando and I disagree about (and, to be sure, he makes some good points that do need to be paid attention to), is that of security. If an attacker get's in as an "SA", it's a simple matter for them to turn on xp_CmdShell and game over. It, perhaps, the most ironic of all security matters, if you have a properly locked down system to prevent "SA" level attacks, have no users with "SA" privs, and haven't made the mistake of granting an individual login or user privs to run xp_CmdShell directly (they should only have privs to execute a proc that contains a call to xp_CmdShell), then there's really no problem with using xp_CmdShell. You won't actually ever need to trace who did what because no individual (except DBAs with "SA" privs who can turn it on at the drop of a hat) can ever use it directly.
--Jeff Moden
Viewing 15 posts - 61 through 75 (of 76 total)
You must be logged in to reply to this topic. Login to reply