October 7, 2011 at 12:41 pm
opc.three (10/7/2011)
jasona.work (10/7/2011)
I have setup something that uses xp_cmdshell, although now that I've improved my knowledge and skills with SQL I can see other ways to do this without xp_cmdshell. I currently have one SQL 2005 with it enabled, so that when the backup maintenance plan runs, when it completes, xp_cmdshell fires a DOS batch file to compress the BAK files so we can send them over a (slow) VPN WAN link to our off-site backup.NOW if I were to create this, I'd do it all from a DOS batch file, with SQLCMD to backup the databases (possibly by calling a maintenance plan,) then compressing the backups.
Seeing as plans are in the works to retire the server in question, that's how I'll do it on the new server.
So, was this a "have to use?" At the time, yes, it was the only way I could see to do this. Mind, I am an "accidental DBA," and all my "training" has been Google, these forums, and some MS Certification guides (great for the basics of getting a server up and running, not so much for this sort of thing.)
Jason
Please, have a look at PowerShell. Once you ramp up you will never want to open CmdShell again.
+10000000
October 7, 2011 at 3:13 pm
An example of when you "have" to use it would be a requirement from a 3rd party. SAP requires xp_cmdshell be enabled and permission to execute it be granted to the SQL login.
I'm not advocating that it should be, but it is a requirement.
October 7, 2011 at 3:53 pm
JeremyE (10/7/2011)
An example of when you "have" to use it would be a requirement from a 3rd party. SAP requires xp_cmdshell be enabled and permission to execute it be granted to the SQL login.I'm not advocating that it should be, but it is a requirement.
I see where you're going, but that's not how I view the issue, at all. That is not an example of when "you" HAVE to use it. "You" did not choose to use it. The SAP engineers producing the closed-source product would need to answer to that choice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 9, 2011 at 12:46 pm
I agree with this. We now have a policy here at work: we do not purchase software that requires the usage of xp_cmdshell, along with other things too, like any logins with sysadmin membership, etc.
October 9, 2011 at 5:47 pm
opc.three (10/7/2011)
Please, have a look at PowerShell. Once you ramp up you will never want to open CmdShell again.
Well I might especially if it's to fire off PowerShell from a T-SQL Stored Procedure. 😉
Heh... some of the same folks on this very thread that are condemning the use of xp_CmdShell are the very same ones who will call a script from SSIS because they can't figure out how to get SSIS to do it. For a great many of those folks, the reason why they used SSIS for whatever they're doing to begin with is because they couldn't figure out how to do it in T-SQL without using xp_CmdShell.
And that's the real problem here. The reason why they're so afraid of xp_CmdShell is because their production systems aren't properly locked down to begin with. 😉 If you have any users or logins other than the DBA and proxies themselves that have more than simple PUBLIC privs, then your system isn't properly locked down. If any user or login can read directly from a table, then your system isn't properly locked down. If any user or login can do ANYTHING other than execute a given set of procs, then your system isn't properly locked down.
The execution of xp_CmdShell (especially in 2005 and above) can be done through a proc without the user having any more than PUBLIC permissions and that user cannot execute xp_CmdShell directly. Microsoft went through great pains to make such a thing possible so that people could properly lock their production systems and still get things done through stored procedures.
If you're concerned about "problems" with xp_CmdShell, then you have MUCH larger problems to worry about!!! The only reason why anyone worries about xp_CmdShell is because they don't actually have proper control over who does what in the production systems and more than PUBLIC privs have been granted to folks other than the DBA's.
Think I'm wrong? How about those folks who are so paranoid about xp_CmdShell that they've actually deleted the DLL from their server? Wouldn't they be surprised when I showed them how to get to the Cmd prompt without using xp_CmdShell because their system isn't secure? And, no... I'm not going to demonstrate how to do that because it's information too dangerous for the general public. Just know that it can be done unless you're system is locked down properly... only PUBLIC privs for everyone except the DBA's/Proxies and everything is done through stored procs.
Stop spending so much time trying to defame a tool that works so well. Instead, fix you're systems to be secure so you can actually take advantage of it. 😉 If you don't want to do that, then please stop knocking a valuable tool that you don't actually understand. 😀
--Jeff Moden
October 10, 2011 at 8:07 am
opc.three (10/7/2011)
jasona.work (10/7/2011)
I have setup something that uses xp_cmdshell, although now that I've improved my knowledge and skills with SQL I can see other ways to do this without xp_cmdshell. I currently have one SQL 2005 with it enabled, so that when the backup maintenance plan runs, when it completes, xp_cmdshell fires a DOS batch file to compress the BAK files so we can send them over a (slow) VPN WAN link to our off-site backup.NOW if I were to create this, I'd do it all from a DOS batch file, with SQLCMD to backup the databases (possibly by calling a maintenance plan,) then compressing the backups.
Seeing as plans are in the works to retire the server in question, that's how I'll do it on the new server.
So, was this a "have to use?" At the time, yes, it was the only way I could see to do this. Mind, I am an "accidental DBA," and all my "training" has been Google, these forums, and some MS Certification guides (great for the basics of getting a server up and running, not so much for this sort of thing.)
Jason
Please, have a look at PowerShell. Once you ramp up you will never want to open CmdShell again.
Actually, I'm using Powershell for some other (non-SQL) server stuff around the office. Mostly monitoring running processes that were developed in-house, that will on occasion fail.
As I say, the server on which I'm using XP_cmdshell is getting moved to a new box, so when it does, the Maintenance Plan can (and will) be re-written, and I'll avoid XP_cmdshell. Most likely, as you suggested, a short and simple Powershell script called from the plan, or even just use Powershell to handle the backup entirely.
Jason
October 10, 2011 at 10:15 am
Jeff Moden (10/9/2011)
opc.three (10/7/2011)
Please, have a look at PowerShell. Once you ramp up you will never want to open CmdShell again.Well I might especially if it's to fire off PowerShell from a T-SQL Stored Procedure. 😉
Yes, because what I really aim for when designing applications is to involve as many programming domains as possible in a single call stack. If only all my processes could start in the client domain language, then move to a T-SQL domain, then jump into a CmdShell domain, and then into PowerShell domain when I could have just as easily started in PowerShell 😛
Heh... some of the same folks on this very thread that are condemning the use of xp_CmdShell are the very same ones who will call a script from SSIS because they can't figure out how to get SSIS to do it.
Apples and Watermelons...an SSIS script task does not run in a different domain than the parent SSIS package, both are managed .NET domains. xp_CmdShell spawns a cmd shell which is a different programming domain than the T-SQL process that created it.
For a great many of those folks, the reason why they used SSIS for whatever they're doing to begin with is because they couldn't figure out how to do it in T-SQL without using xp_CmdShell.
If you're referring strictly to the functionality offered by the different tools then you would be hard pressed to convince me that people choose SSIS solely because they do not know how to get the same thing done using xp_CmdShell. Just curious, how would you propose calling a web service using xp_CmdShell? How about doing anything asynchronously?
People choose SSIS because it is easy to get started, it is multi-threaded and it has a managed programming aspect in the Script Task & Components which let them code custom solutions easily, not because they could not figure out how to turn SQL Server into an app server using xp_CmdShell.
Stop spending so much time trying to defame a tool that works so well. Instead, fix you're systems to be secure so you can actually take advantage of it. 😉 If you don't want to do that, then please stop knocking a valuable tool that you don't actually understand. 😀
Jeff, your comment is nothing short of condescending. I do not know if the whole post was for me, or just the response after the quoted comment of mine, but either way the comment was uncalled for. If the last statement was for me, then it was inaccurate. If my responses seem spirited, your comment above is the reason.
<opinion>It's not about whether xp_CmdShell can be locked down, it's about reducing risk by keeping it disabled, and whether using it makes for good application design. Using xp_CmdShell is like painting yourself into a corner. Granted, there are many things that xp_CmdShell can do, and so many more that would be simpler, cleaner, faster, more secure, more flexible, more robust and more maintainable if done in a managed programming domain.</opinion>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 10, 2011 at 5:05 pm
Nope... my comment wasn't directed specifically at you, Orlando. If it was, I'd have said, "Orlando, stop....". 😉
I do have a question for you though... why would you want to call a Web Service from SSIS or did I misread that?
And, no... I don't believe my comment was uncalled for. It's no more uncalled for than people bashing the tool and people that use it even if it's not directed at individuals.
--Jeff Moden
October 11, 2011 at 11:16 am
Jeff Moden (10/10/2011)
Nope... my comment wasn't directed specifically at you, Orlando. If it was, I'd have said, "Orlando, stop....". 😉
Good to know...if you did say that I would stop, and then I would listen. I am only expressing my opinion and disposition towards implementing the tool. I am not bashing the tool nor the people that use it, that take is pointless. I just choose not to use it and I steer people building a solution away from using it when and where I can. Mainly because I know there are other options, options which I think have more upside. The peeve here is when people looking for answers on a forum are blindly fed "just use xp_cmdshell" with no eye towards how to implement it securely or what it can mean when you need to do things like call a web service or do something async or any number of other things that would be easier if everything was done within a single programming domain.
Back in the SQL 7.0 days I went down the road of using xp_CmdShell. My alternatives at the time were DTS, VB Script, VB6 Windows apps masquerading as Console Apps, and a few others, but none of them offered a compelling reason not to use xp_CmdShell. I am not sure of what became of that code but I have since inherited many code-bases that looked just like it and have looked to rewrite all of them. In my opinion we have better tools now.
I do have a question for you though... why would you want to call a Web Service for SSIS or did I misread that?
Not for, but from within SSIS. An example might be calling a web service to cleanse, verify and standardize personal information like name, address and phone number before moving the cleansed data through the pipeline into a staging table in SQL Server for eventually updating a table containing address data.
And, no... I don't believe my comment was uncalled for. It's no more uncalled for than people bashing the tool and people that use it even if it's not directed at individuals.
OK. I was taken aback by the "that you don't actually understand" comment but I see where you're coming from. I try to be objective but I fail at times. Thank you for re-balancing my view on the topic. I appreciate you're influence immensely Jeff.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 11, 2011 at 12:18 pm
Jeff Moden (10/9/2011)... ...If you don't want to do that, then please stop knocking a valuable tool that you don't actually understand. 😀
I agree with most of what you said there.
From the times that I've seen xp_cmdshell deployed, it appeared, to me, to be very clear that it was used because the developer knew about it and thought it would be a quick and easy way to get something done without much thought, if any, as to how to accomplish the same thing by other means, and without thinking about any consequences, other than security.
Here's one example: This was already implemented before I was hired at my current job. We have a third party financial app that the rest of the company depends on to run other stuff during nightly processing. Some of those receive data directly from SQL via linked servers, others will consume text files exported to particular folders, etc. One of those particular feeds is done via FTP. So the developer decided that xp_cmdshell was good enough to execute http://ftp.exe. In coding that functionality, the app runs a stored procedure that builds a command line on the fly and uses xp_cmdshell to bcp.exe the data to the OS. Next it used xp_cmdshell again with another command built on the fly to FTP that file to a vendor. Aside from the fact that user names and passwords were coded into the procedure that built the FTP code, the developer never thought about what might happen when an error occurred during the FTP part. Many were the nights I got paged because the app admin needed me to kill the FTP the process in the OS because it was holding up all subsequent night jobs. This was because whatever the error, it was popping up a dialog box in the server waiting for a user or another to click cancel/close/ok/whatever else. But since the command was running without a console, no one could ever troubleshoot the damn thing to figure out what was the actual error. All attempts to try to log the command to a log file failed to capture the error, and nothing would be logged to the sql log or the event viewer.
It took almost a year to get them to allow me to change this code, mostly because they were completely unfamiliar with how else it could be done and kept on coming back with "if this other thing breaks, who's going to deal with it? We don't know this/that and just don't have the time to learn something new" and the like.
In the end I was able to go around their fears because people above them were getting pretty tired of delayed nightly jobs. The resulting code puts all of the information in their hands when there is an FTP error. After my code was put in place, they finally figured out that the problem is usually that the FTP server on the other end does not respond.
No mind you that said app, has the ability to execute shell commands and batch files directly from its front end, by simply selecting the type of command from a drop down box (much like you choose the type of step in a SQL job). So it was obvious to me that a better approach would have been to have the app run it's SQL pieces first, then execute BCP followed by the FTP from it's interface. If nothing else, they would've been able to at least catch the errors from the FTP process, never mind being able to start/kill/stop/restart it themselves at will. When asked why they hand't used that functionality, the answer was the same as before "we don't have the time to learn something new".
Edit: corrected some grammar errors, but surely not all 😉
October 12, 2011 at 1:24 am
opc.three (10/11/2011)
The peeve here is when people looking for answers on a forum are blindly fed "just use xp_cmdshell" with no eye towards how to implement it securely...
Now, THAT, you and I totally agree on!
An example might be calling a web service to cleanse, verify and standardize personal information like name, address and phone number before moving the cleansed data through the pipeline into a staging table in SQL Server for eventually updating a table containing address data.
Thanks for the info, Orlando... I have to admit, thought, that I probably wouldn't use the Web Service for such a thing... I'd do all of that through a stored procedure.
OK. I was taken aback by the "that you don't actually understand" comment but I see where you're coming from. I try to be objective but I fail at times. Thank you for re-balancing my view on the topic. I appreciate you're influence immensely Jeff.
Again, my apologies. I can see how you thought that I had directed all of this at you because I quoted you and didn't specifically say that I was shifting gears.
I have two great angers on the subject of xp_CmdShell and you've correctly cited the one that I didn't properly address. The two angers are the people who bash the use of xp_CmdShell without knowing how to use it correctly and, perhaps surprising to many, my greatest anger is directed at those people who recommend its use with, as you so aptly stated, "no eye towards how to implement it securely'. Those folks actually do more harm than good because 1) without the proper security aspects, their code is flat out dangerous and permeable to attack and 2) because of those dangerous recommendations, xp_CmdShell continues to receive a black-eye that it really doesn't deserve.
What I'd like to see in the future is for well meaning folks to qualify their bashings... Properly used, xp_CmdShell is no more dangerous that using a properly formed SELECT (actually, it can be even less dangerous). Improperly used and xp_CmdShell is like giving potential attackers "SA" privs. That's not the particular fault of xp_CmdShell... it's the fault of the people that are misusing it and THAT is the true problem that needs to be bashed.
--Jeff Moden
October 12, 2011 at 11:20 am
Jeff Moden (10/12/2011)
opc.three (10/11/2011)
An example might be calling a web service to cleanse, verify and standardize personal information like name, address and phone number before moving the cleansed data through the pipeline into a staging table in SQL Server for eventually updating a table containing address data.
Thanks for the info, Orlando... I have to admit, thought, that I probably wouldn't use the Web Service for such a thing... I'd do all of that through a stored procedure.
I used address data as an example since it's a common use case, but there are local address data solutions too, so maybe it was a bad example. Think third-party web service. A company that offers a value-add service to enrich data that does not reside locally, i.e. data not reachable via standard T-SQL methods.
OK. I was taken aback by the "that you don't actually understand" comment but I see where you're coming from. I try to be objective but I fail at times. Thank you for re-balancing my view on the topic. I appreciate you're influence immensely Jeff.
Again, my apologies. I can see how you thought that I had directed all of this at you because I quoted you and didn't specifically say that I was shifting gears.
I must have been having a bad morning, you deserved the benefit of the doubt re: "directed at me". Sorry about that. The written-word format strikes again.
...because of those dangerous recommendations, xp_CmdShell continues to receive a black-eye that it really doesn't deserve. ...
I'll walk elbow-to-elbow with you on this idea any day. The results of a poor implementation does not a bad tool make. Even when equipped with knowledge of one way to secure xp_CmdShell you showed me the error of my ways in the past (long before this thread) in terms of blindly bashing xp_CmdShell, and then proceeded to further educate me as to additional ways to secure xp_CmdShell. The result is that I now offer a balanced appraisal of xp_CmdShell, at least closer to balanced than before. That said, while my tone is more objective, the end result of steering folks away from it in favor of a managed language solution remains the same for me. The effort required to properly educate folks, plus the upside of moving to a managed language solution lands me on "do not enable xp_CmdShell, it's not worth it and you'll eventually hit a glass ceiling, here, look at this".
Edit: grammar
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 12, 2011 at 12:41 pm
Post SQL 2000 I am fundamentally opposed to using xp_cmdshell, there really is no reason. Within the forums I see it being thrown out as a solution periodically, in all cases there is a better way. Now I know some of you are afraid of SQLCLR, but it really does a good job and I recommend it. With that recommendation I also say that you need to be able to review the code before it ever goes to production. Policy for me is NO SQLCLR code will go into production without a review, there are no exceptions, and they must provide the code with sufficient lead time to actually review it.
I saw some comments from Jeff about using the Script Task in SSIS. I believe there is a right tool for each job and many times I can accomplish a great deal with a lot more manageable and maintainable code with a Script, however it is always important to consider the right tool for the right job.
I saw someone mentioning SQL Express as a justification to use xp_cmdshell. The lack of SQL Agent and SSIS, while troublesome is surmountable with SQLCLR in almost all cases. If you are trying to move a bunch of data around I have to question whether he use of Express is the correct choice.
My policy (written and otherwise) is to disable and keep it disabled. When possible I add a policy to verify that it stays that way. My developers are instructed not to use it. In cases where we have legacy code athat uses xp_cmdshell it is planned to fix it, and no new code is permitted to use xp_cmdshell. This is verified periodically through code searches.
Bottom line for me, don't use xp_cmdshell, period.
CEWII
October 13, 2011 at 9:17 am
opc.three (10/12/2011)
Think third-party web service. A company that offers a value-add service to enrich data that does not reside locally, i.e. data not reachable via standard T-SQL methods.
Actually, that's a great example and I've been through that with 3rd party verification for telephony. They did it all through managed code, though. Nothing even came close to the database until that verification was resolved.
I must have been having a bad morning, you deserved the benefit of the doubt re: "directed at me". Sorry about that. The written-word format strikes again.
The written-word format is definitely a bugger but the fault is all mine in this case. It's absolutely natural to think that something is directed at you when someone quotes you and the post doesn't clearly identify when shifting comments back out to the general subject at hand. I very much appreciate your feedback on this and it would be a good thing if I remembered to do such a thing in the future. Thanks, Orlando.
...because of those dangerous recommendations, xp_CmdShell continues to receive a black-eye that it really doesn't deserve. ...
I'll walk elbow-to-elbow with you on this idea any day. The results of a poor implementation does not a bad tool make. Even when equipped with knowledge of one way to secure xp_CmdShell you showed me the error of my ways in the past (long before this thread) in terms of blindly bashing xp_CmdShell, and then proceeded to further educate me as to additional ways to secure xp_CmdShell. The result is that I now offer a balanced appraisal of xp_CmdShell, at least closer to balanced than before. That said, while my tone is more objective, the end result of steering folks away from it in favor of a managed language solution remains the same for me. The effort required to properly educate folks, plus the upside of moving to a managed language solution lands me on "do not enable xp_CmdShell, it's not worth it and you'll eventually hit a glass ceiling, here, look at this".
Now that's an argument that I agree with 100%. Well done. In fact, that would make for a good introduction to the pair of articles we previously discussed and I'm going to include something to the effect of there being a "lot of better ways to accomplish the same thing but, if you're somehow backed into a corner with no choice but to use it, here's one of the correct ways to use it."
Shift gears slightly, how did that one project where you were compelled to use xp_CmdShell because of large amounts of legacy code go? Where you able to lock the system down as we discussed or where there caveats that we didn't know about when we first discussed the problem?
--Jeff Moden
October 13, 2011 at 9:58 am
On the "poll" section of this thread, I see that 9 people voted as "[font="Arial Black"]Yes, but I can give you a good example of when you HAVE to use it[/font]" but I don't see 9 such posts on this thread... now that we have calmed the bashing a bit, would you good folks please come out and give some examples? Yes, I'd expect a lot of other folks to give some counter-suggestions but I think we have the outright bashing under control now.
To try to encourage you good folks to come out and explain, I'll explain the reasons why I use xp_CmdShell...
The first reason is simply legacy code. Some of the companies that I've worked at have had a large amount of legacy code that used xp_CmdShell. These same companies also didn't understand the huge security holes they had by giving most users/logins either "SA" or "DBO" privs whether or not they had public-facing code or not. Some of their "database code" was also in the form of SQL embedded in the "managed code" of their applications. The "lock down" I helped them do was to move that embedded code to stored procedures and then to lock down the system by giving the stored procedures the correct privs and revoking all but execution privs from all but the designated DBA's. Since these companies also had no one that had sufficiently learned such alternate methods as SSIS or simply wanted "all the code in one place", it was very easy to correctly implement the use of xp_CmdShell because of the lock down that was done.
Some of those companies didn't want to go through the full lock down process as the ones above. Still, they didn't want the public-facing applications' logins to have privs to execute xp_CmdShell directly but still wanted those logins to be able to execute stored procdures which contained calls to xp_CmdShell. The same methods used for the strict lock downs where used in those cases except that instead of reducinng the privs for those logins to mere PUBLIC privs, we were able to reduce the privs from "SA" to those of "DBO" and still prevent direct-execution privs of xp_CmdShell. As you can imagine, this greatly reduced certain possible "attack" methods across the board (although it still meant that an attacker could blow away or alter data and, of course, I was still mortified by that prospect).
At another company and right or wrong, they simply tired of having to deploy changes to such things as SSIS and elected to use xp_CmdShell so that they could "have their code all in one place". They didn't have sophisticated requirements but they did want to export data using BCP on a massive basis and automatically upload the data to various FTP sites. They also wanted to move the files out of that staging directory once the data had been successfully upload. As many will attest to, there are alternate methods to accomplish all of this but the mantra at this particular company was to do it though stored procedures only.
To be sure, I didn't make it so that xp_CmdShell could be executed by stored procedures only for all of these different instances. In some case, we did take the route of using scheduled jobs that, in a manner similar to using stored procedures, could be started by a low-priv user but could not be viewed or modified by such users.
I'd sure like to hear where any of you other folks had a reason where you thought you HAD to use xp_CmdShell and, if the bashing can be kept under control, suggestions from others who have alternative methods for those folks.
Thanks everyone...
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply