June 14, 2011 at 9:56 am
Thomas LeBlanc (6/14/2011)
3rd party backup softwareThomas
I just love short, sweet, and accurate. Thanks, Thomas. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 10:00 am
GSquared (6/14/2011)
"Grand Master at command line use," isn't likely to land a job.
Heh... I agree. For most, it won't. For me, it was a different story... a similar skill listing on my resume was one of the reasons why my resume was picked up by automation and one of the skills that played a key role in my being hired for one of my previous jobs.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 10:08 am
Jeff,
No problem.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
June 14, 2011 at 11:40 am
Jeff Moden (6/14/2011)
GSquared (6/14/2011)
"Grand Master at command line use," isn't likely to land a job.Heh... I agree. For most, it won't. For me, it was a different story... a similar skill listing on my resume was one of the reasons why my resume was picked up by automation and one of the skills that played a key role in my being hired for one of my previous jobs.
Edge cases (does calling you that make you "edgy"?) are why I specifically worded it as "...isn't likely...".
And, yeah, oddball or less popular skillsets can help out. I recently had an offer that would have been based around the "leet Access skillz" I used to have. Would have been almost half as much as I currently make, even! But I don't count on that. Heck, there was a time when I used to be able to rock the .BAT files! I don't really list that on my resume anymore. It's right up there with my Extreme Guru level of Word Perfect macro programming that I used to do.
To answer your original question, late yesterday I had to get a list of all the csv files in a set of subdirectories on a server, and e-mail that list to someone. Did I do anything fancy? Nope, I fired up xp_CmdShell, ran the dir command with the switches I needed, and copy-and-pasted the results into the e-mail. Took seconds to get done. Could be done through other means, and I'd turn it into a CLR proc if I needed it at all regularly. But for a one-off, heck yeah, I turned on xp_CmdShell, ran it, turned it off, and was done. (Could have done it directly in the command shell, but I already had SSMS open, so why not?)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2011 at 1:30 pm
No.
How's that for short, sweet and accurate? 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 5:19 pm
opc.three (6/16/2011)
No.How's that for short, sweet and accurate? 😉
That absolutely works for me. 😀 It would be very cool if you told me what you use instead. I know you're getting into PS but how or from where do you execute the PS script?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2011 at 8:07 pm
Jeff Moden (6/16/2011)
opc.three (6/16/2011)
No.How's that for short, sweet and accurate? 😉
That absolutely works for me. 😀 It would be very cool if you told me what you use instead. I know you're getting into PS but how or from where do you execute the PS script?
Sorry man, I couldn't resist 😛
To add a bit more...in short, a job scheduler! These days that means SQL Agent exclusively but in the past that has also meant Windows Task Scheduler :sick: and UC4 (worth a look if you need a multi-platform enterprise scheduler).
I do all new scripting in PowerShell (environment permitting)...no JS or VBS (thank goodness) and I will churn out the occasional Windows Batch script and still do support a ton of it.
In theory I want all my non-SQL code acting like "just another unprivileged client" so I try not to break the "SQL programming domain" once I'm in there. SMO, bcp, sqlcmd, C#, PowerShell, Windows Batch and SSIS are all in the toolkit and are fair game to knock down a task. Some of what you can do with SqlClr (regex, aggregates, string manipulation) I would include in the SQL programming domain. Other SqlClr capabilities like file access or calling a web service I would not. I am on the fence about using SqlClr to write to a Windows event log...I need to research that one a bit more but it seems there is some value to be gained from the approach. Other things that go outside the SQL domain that I try to avoid: xp_CmdShell (naturally), xp_'s that talk to the registry, OLE Automation procs, Linked Servers, BULK INSERT, OPENDATSOURCE, OPENROWSET and more hooey...the list is long.
:Wow: I just set myself up big time huh?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 4:26 am
June 17, 2011 at 4:50 am
opc.three (6/16/2011)
:Wow: I just set myself up big time huh?
Nah... considering the number of hacker attacks designed specifically to exploit OLE DB Automation, I can see your point especially in versions prior to SQL Server 2005 (before they enhanced the security). Considering how easy it is for an attacker to be able to make it possible for someone (usually, themselves) who can use OPENROWSET to run CMD shell utilites, I'm right there with you on the OLE DB Automation lock down.
Rumor (I'm admittedly NOT a security expert) has it that there are just as many hacker attacks specifically designed to exploit SQL Agent and the real key to security turns out to be a great firewall and very, very strong passwords (and I'm not talking about stupid ones like the company name spelled in "leet"). Of course, that (the firewall) won't help much if the attack is spawned by an internal source. :sick:
Part of the reason for my questioning about whether or not there's a reason to use xp_CmdShell is that it's always amazed me that folks will give developers and users privs to do just about anything they want even with "company sacred data" in tools like SSIS but then start making all sorts of sucking sounds when you tell them they could run xp_CmdShell in a super secure manner. Of course, it's also always amazed me that some companies allow developers to have "SA" privs on production data. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2011 at 6:41 am
Jeff Moden (6/17/2011)
opc.three (6/16/2011)
:Wow: I just set myself up big time huh?Nah... considering the number of hacker attacks designed specifically to exploit OLE DB Automation, I can see your point especially in versions prior to SQL Server 2005 (before they enhanced the security). Considering how easy it is for an attacker to be able to make it possible for someone (usually, themselves) who can use OPENROWSET to run CMD shell utilites, I'm right there with you on the OLE DB Automation lock down.
Rumor (I'm admittedly NOT a security expert) has it that there are just as many hacker attacks specifically designed to exploit SQL Agent and the real key to security turns out to be a great firewall and very, very strong passwords (and I'm not talking about stupid ones like the company name spelled in "leet"). Of course, that (the firewall) won't help much if the attack is spawned by an internal source. :sick:
Part of the reason for my questioning about whether or not there's a reason to use xp_CmdShell is that it's always amazed me that folks will give developers and users privs to do just about anything they want even with "company sacred data" in tools like SSIS but then start making all sorts of sucking sounds when you tell them they could run xp_CmdShell in a super secure manner. Of course, it's also always amazed me that some companies allow developers to have "SA" privs on production data. 😀
Most security policies in most companies are apparently based on "I read a headline and the first sentence of an article one time..." or "I heard a rumor ..." or "The prior DBA, the one we had to fire because of incomptence, told me that xp_CmdShell is the biggest security hole in the world...".
The problem with xp_CmdShell isn't xp_CmdShell itself. The problem is the combination of it and other vectors. Security is all about making it more effort than it's worth to crack your system. That's all it is. Having xp_CmdShell already enabled removes one level of effort towards getting commands to run outside the scope of T-SQL DML. That's all. But most managers, et al, even many DBAs, don't understand that. They think there's some magical way to have "a perfectly secure server", or they think "security is overdone and it's getting in the way of doing my job". One or the other, not the infinity of shades in between.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2011 at 6:47 am
GSquared (6/17/2011)
Jeff Moden (6/17/2011)
opc.three (6/16/2011)
:Wow: I just set myself up big time huh?Nah... considering the number of hacker attacks designed specifically to exploit OLE DB Automation, I can see your point especially in versions prior to SQL Server 2005 (before they enhanced the security). Considering how easy it is for an attacker to be able to make it possible for someone (usually, themselves) who can use OPENROWSET to run CMD shell utilites, I'm right there with you on the OLE DB Automation lock down.
Rumor (I'm admittedly NOT a security expert) has it that there are just as many hacker attacks specifically designed to exploit SQL Agent and the real key to security turns out to be a great firewall and very, very strong passwords (and I'm not talking about stupid ones like the company name spelled in "leet"). Of course, that (the firewall) won't help much if the attack is spawned by an internal source. :sick:
Part of the reason for my questioning about whether or not there's a reason to use xp_CmdShell is that it's always amazed me that folks will give developers and users privs to do just about anything they want even with "company sacred data" in tools like SSIS but then start making all sorts of sucking sounds when you tell them they could run xp_CmdShell in a super secure manner. Of course, it's also always amazed me that some companies allow developers to have "SA" privs on production data. 😀
Most security policies in most companies are apparently based on "I read a headline and the first sentence of an article one time..." or "I heard a rumor ..." or "The prior DBA, the one we had to fire because of incomptence, told me that xp_CmdShell is the biggest security hole in the world...".
The problem with xp_CmdShell isn't xp_CmdShell itself. The problem is the combination of it and other vectors. Security is all about making it more effort than it's worth to crack your system. That's all it is. Having xp_CmdShell already enabled removes one level of effort towards getting commands to run outside the scope of T-SQL DML. That's all. But most managers, et al, even many DBAs, don't understand that. They think there's some magical way to have "a perfectly secure server", or they think "security is overdone and it's getting in the way of doing my job". One or the other, not the infinity of shades in between.
I don't disagree. My stance on it is part security and part "developers, stay away from the servers hosting my SQL Server instances". If I open up xp_CmdShell then I start getting questions like "what drive letter is SQL on? and "can we setup a share on the SQL box so I can drop files there from an FTP server".... NO!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 7:28 am
We've stopped using xp_cmdshell on all new instances (nearly all 2008 and better).
It's still available on SQL 2005 and 2000 though, mostly for all the maintenance jobs and procedure.
We only had a limited budget for SQL 2008 initial deployment, so we decided to spend it on rewriting an all new bunch of maintenance procedures that works with SQL 2008 and more but no longer support SQL 2000, while we were at it we did it all without using xp_cmdshell, which in the end is way easier than expected.
Powershell is mostly used for the backup/restore procedures and monitoring the instances.
For the rest we found out xp_cmdshell is no longer needed.
We developed a few CLR proc in a toolkit and we manage to do anything we want with it, even quicker than before with xp_cmdshell.
Did we reinvent the wheel? For some stuffs yes, but we went from an old wooden cart wheel to a nice run_on_flat wheel with a pressure monitoring system.
Did we really need to get rid of xp_cmdshell? Honestly no, if someone manages to have the rights needed to exploit xp_cmdshell, we've already lost.
BUT it's a very nice way to ask your management for a bit more budget you'll spend on improving things for yourself.
June 17, 2011 at 3:48 pm
To be able use bcp to generate text files from within SQL code/stored procedure, such as for reporting (or even logging).
Also, to execute administrative batch files such as drive maintenance (managing command line encryption software, for one), or the ubiquitous robocopy.
I understand that in 2005 there may be better ways to output column to a fixed or delimited text file than there were in 2000; I haven't researched them yet.
June 17, 2011 at 4:38 pm
opc.three (6/17/2011)
I don't disagree. My stance on it is part security and part "developers, stay away from the servers hosting my SQL Server instances". If I open up xp_CmdShell then I start getting questions like "what drive letter is SQL on? and "can we setup a share on the SQL box so I can drop files there from an FTP server".... NO!
Yet you have no problem with such things if it's done through SSIS or through SQL Agent and a script or through a CLR?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2011 at 4:44 pm
Jayanth_Kurup (6/17/2011)
I have a report which i use to check the health of my database servers, as a part of it i use xp_cmdshell to execute pings to the db server
Thanks, Jayanth. I'm curious... why didn't you do this via a scheduled job and a script or some sort? Why did you decide to use xp_CmdShell, instead?
And, no... no need to get defensive if that came across the wrong way. I'm really trying to find out the reasons why some folks stick with xp_CmdShell instead of using some of the other methods.
Thanks... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 107 total)
You must be logged in to reply to this topic. Login to reply