October 5, 2011 at 3:41 pm
I'm a huge fan on locking down my SQL Servers to the point where sometimes it hurts. Not just at the SQL instance level, but also the OS, registry, etc. My local server admins can't even login to my SQL instances unless it's through a configured front end.
I despise the thing and after seeing a bunch of posts sprinkled all over the 2k5+ forums with "...use xp_cmdshell..." as a way to do a billion different things I got curious and decided to see what everybody else thinks about it.
Please answer the poll and then discuss away.
Thanks.
October 5, 2011 at 3:47 pm
There are good reasons that it comes disabled by default.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 5, 2011 at 3:58 pm
I would consider myself a crusader against enabling and using xp_cmdshell. I have posted many times on these forums advising against the use of it. All too often it is offered up casually as a solution to a problem with no mention that the feature is disabled by default and that enabling it increases the attackable surface area of the instance and hosting server let alone how to secure it.
Let's take a simple task like writing to a text file from within a stored procedure. There are those that will say (you know who you are ;-)) that it offers a decided advantage from a code management perspective to have all your code within T-SQL, including the code that writes to a file, so it is backed up with the data and other code objects when backups are taken. I say this is creating a dependency between the database instance and the underlying file system of the hosting server. In addition it muddles application design by mixing data access with file management tasks. Application languages (e.g. PowerShell, C# and SSIS) are much better suited to handle file management tasks and bridge the gap between multiple system domains much more elegantly than T-SQL ever will. It is no accident the application languages I mentioned are managed.
PS I cannot wait to see the examples of when one would HAVE to use it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2011 at 4:01 pm
opc.three (10/5/2011)
PS I cannot wait to see the examples of when one would HAVE to use it.
SQL Express with an on-demand file bulk load and archiving.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 4:08 pm
Evil Kraig F (10/5/2011)
opc.three (10/5/2011)
PS I cannot wait to see the examples of when one would HAVE to use it.SQL Express with an on-demand file bulk load and archiving.
My guess is there are plenty of alternatives but am not sure what you mean. Please expound Craig.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2011 at 4:24 pm
opc.three (10/5/2011)
Evil Kraig F (10/5/2011)
opc.three (10/5/2011)
PS I cannot wait to see the examples of when one would HAVE to use it.SQL Express with an on-demand file bulk load and archiving.
My guess is there are plenty of alternatives but am not sure what you mean. Please expound Craig.
Depends on if you want to initiate the call from SQL Server, but there's always some other way.
In particular, if you're running SQL Express (thus no SQL Agent) and want to do a BCP/Bulk Insert and then archive the file you've done it from, you'd need xp_cmdshell.
The key there really is that it's SQL Express.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 4:38 pm
Evil Kraig F (10/5/2011)
opc.three (10/5/2011)
Evil Kraig F (10/5/2011)
opc.three (10/5/2011)
PS I cannot wait to see the examples of when one would HAVE to use it.SQL Express with an on-demand file bulk load and archiving.
My guess is there are plenty of alternatives but am not sure what you mean. Please expound Craig.
Depends on if you want to initiate the call from SQL Server, but there's always some other way.
That was my only point. You know what I am getting at. Employing xp_cmdshell is a choice. I'm not saying it cannot be implemented securely, but it rarely is, and in my experience is usually employed over other more secure and robust options out of laze.
In particular, if you're running SQL Express (thus no SQL Agent) and want to do a BCP/Bulk Insert and then archive the file you've done it from, you'd need xp_cmdshell.
The key there really is that it's SQL Express.
To my knowledge you can use Service Broker with External Activation with SQL Server Express, as long as the message is not coming from another Express instance. Adding a row to a queue from a client would be like inserting a row into a table. I think you could eliminate the use of xp_cmdshell here. The externally activated executable or script could load the file using bcp or whatever and then archive the files.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2011 at 4:55 pm
opc.three (10/5/2011)
In particular, if you're running SQL Express (thus no SQL Agent) and want to do a BCP/Bulk Insert and then archive the file you've done it from, you'd need xp_cmdshell.
The key there really is that it's SQL Express.
To my knowledge you can use Service Broker with External Activation with SQL Server Express, as long as the message is not coming from another Express instance. Adding a row to a queue from a client would be like inserting a row into a table. I think you could eliminate the use of xp_cmdshell here. The externally activated executable or script could load the file using bcp or whatever and then archive the files.
Heh, you're stretching an already stretched point there Orlando. Most people who are looking to figure out Express implementations don't have the core server available, they're using it instead of MySQL as a free choice... and have you seen what happens when most people try to get Service Broker under a standard implementation? Now, even if you can do an external activation on SQL Express with no Core... I'd rather try to teach someone certificate signing on procs before that and just show them better security then the open-door policy.
You could also create a repeated ping process loaded in Windows Scheduler to run once a minute, or write some insanely nasty front end code to fire up SQLCMD and send that down to the DB server.
It's not always a question of what CAN be done, but what's also reasonable to expect. I could, in theory, code a Linux competitor. I'm not going to do that every time I need to use a less than optimal workaround in Windows.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 5:15 pm
Evil Kraig F (10/5/2011)
opc.three (10/5/2011)
In particular, if you're running SQL Express (thus no SQL Agent) and want to do a BCP/Bulk Insert and then archive the file you've done it from, you'd need xp_cmdshell.
The key there really is that it's SQL Express.
To my knowledge you can use Service Broker with External Activation with SQL Server Express, as long as the message is not coming from another Express instance. Adding a row to a queue from a client would be like inserting a row into a table. I think you could eliminate the use of xp_cmdshell here. The externally activated executable or script could load the file using bcp or whatever and then archive the files.
Heh, you're stretching an already stretched point there Orlando. Most people who are looking to figure out Express implementations don't have the core server available, they're using it instead of MySQL as a free choice... and have you seen what happens when most people try to get Service Broker under a standard implementation? Now, even if you can do an external activation on SQL Express with no Core... I'd rather try to teach someone certificate signing on procs before that and just show them better security then the open-door policy.
You could also create a repeated ping process loaded in Windows Scheduler to run once a minute, or write some insanely nasty front end code to fire up SQLCMD and send that down to the DB server.
It's not always a question of what CAN be done, but what's also reasonable to expect. I could, in theory, code a Linux competitor. I'm not going to do that every time I need to use a less than optimal workaround in Windows.
I follow the reasoning there, but in the end there are still other ways to accomplish the task. Thinking that because one may not know enough about SQL Server to implement it in any other way is another pet peeve of mine. EDIT => Which leads me to the following:
This should definitely be discussed in another thread, but I here I say: The best thing that ever happened to SQL Server was Microsoft making it easy enough for anyone to use it, while the worst thing that ever happened to SQL Server was Microsoft making it easy enough for anyone to use it. In other words, 99.9% of the headaches I get from working with SQL Server is a result of people who should have never had a chance to even find out about SQL Server. And don't even get me going about MS Access (for the same reasons). But I've digressed enough already...
October 5, 2011 at 10:31 pm
Evil Kraig F (10/5/2011)
Heh, you're stretching an already stretched point there Orlando.
In the scenario where SQL Express is leveraged to create an on-demand file loader and archiving application SQL Server is what is being stretched. Implementing Service Broker to handle such a troubling assignment is no more or less absurd than using xp_cmdshell. Service Broker offers an asynchronous queue, an Application Server feature nonetheless, but still a more desirable way to handle this problem than with a synchronous call generated from a front-end user (pushing a button on a UI?). Feeding into the temptation to turn SQL Server into an Application Server by exposing on-demand file loads to a front-end is, in my humble opinion, the real issue that troubles me. The way to solve it best is to not have the issue in the first place. Design your way out of it. If you must use SQL Server to field that file loading request then so be it, the least we can do is defer the work by employing a queue of sorts (SB or homegrown) and have an application better suited to do this kind of work handle it. Sorry mendesm to follow along on the tangent, it is not my intention to take over the thread. I suppose I started it with my PS comment.
The initial point of this thread (I think) was to discuss whether you think designing an application that uses xp_cmdshell is acceptable and talk about why. I do not think it acceptable in any circumstance for the reasons in my initial response. The folks who are choosing the "HAVE to use it" response intrigue and puzzle me at the same time, that's all.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 6, 2011 at 8:55 am
The discussion is good so far.
I actually believe, there is no circumstance in which xp_cmdshell is required or the only way of doing something.
Whether it can be implemented securely, for me is not a discussion at all, because the moment you allow it's use, more if its use will follow instead of using other, more proper ways to accomplish something. Very similar to the "broken window" theory.
October 7, 2011 at 11:39 am
Sorry, work ran me over, lost track of this thread...
opc.three (10/5/2011)
Evil Kraig F (10/5/2011)
Heh, you're stretching an already stretched point there Orlando.In the scenario where SQL Express is leveraged to create an on-demand file loader and archiving application SQL Server is what is being stretched.
Not really, that was personal experience. Cheap pita's... Though, to be honest, I talked them into Express. They were planning on Access.
Implementing Service Broker to handle such a troubling assignment is no more or less absurd than using xp_cmdshell. Service Broker offers an asynchronous queue, an Application Server feature nonetheless, but still a more desirable way to handle this problem than with a synchronous call generated from a front-end user (pushing a button on a UI?).
Well, according to this: http://msdn.microsoft.com/en-us/library/ms165704(v=sql.90).aspx Service Broker won't activate from an Express only instance. In either case, you still need a way to get it queued, but that's easily dealt with via a proc. I don't have a copy of express to deal with here at the office, but I'll see if I can goof off with it over the weekend.
And ayup, it was button push. Primarily so it could get loaded to a staging table for review. Not the happiest design, I know, but I was able to build in a few additional safeguards for concurrency.
Feeding into the temptation to turn SQL Server into an Application Server by exposing on-demand file loads to a front-end is, in my humble opinion, the real issue that troubles me. The way to solve it best is to not have the issue in the first place. Design your way out of it. If you must use SQL Server to field that file loading request then so be it, the least we can do is defer the work by employing a queue of sorts (SB or homegrown) and have an application better suited to do this kind of work handle it. Sorry mendesm to follow along on the tangent, it is not my intention to take over the thread. I suppose I started it with my PS comment.
Well, it also started since I was one of the people who said there are occassions you HAVE to use it. And whether or not Service Broker is a good idea (I'm good with it but I find most avoid it like the plague), it's a moot point if you don't have at least a paid version available.
The initial point of this thread (I think) was to discuss whether you think designing an application that uses xp_cmdshell is acceptable and talk about why. I do not think it acceptable in any circumstance for the reasons in my initial response. The folks who are choosing the "HAVE to use it" response intrigue and puzzle me at the same time, that's all.[/quote]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 7, 2011 at 12:27 pm
I am happy you succeeded in steering them towards SQL Server. I am curious how they planned on solving this particular problem using Access.
I get why you would landed on using xp_cmdshell however we know there are other viable options. Given that SQL Express won't do activation when the sender is not a paid version of SQL Server then MSMQ, the message queue baked into Windows, would have been your friend here. There are other polling workarounds you alluded to with Task Scheduler as well.
Did we scare off the other "HAVE to" responders from saying why xp_cmdshell was a must for them?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 7, 2011 at 12:27 pm
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
October 7, 2011 at 12:30 pm
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 76 total)
You must be logged in to reply to this topic. Login to reply