How to call a batch file to execute from an SP

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 8:42 am

  #1600844

  Jeff Moden (3/26/2013)

  ---

  opc.three (3/26/2013)

  ---

  Not only are you crass in your delivery but your advice is irresponsible and dangerous. Please keep the mischaracterizations to yourself. You're doing a disservice to the community and are putting data at risk by endorsing a tool that allows for the possibility of not only permission elevation, but also obfuscation that could impact auditing and detection of a wrongdoing. Inform people correctly so they can make up their own mind and stop polluting the well of information with your incorrect portrayal of what enabling xp_cmdshell means.

  Heh, our disagreement crystallized once again, in yet one more form.

  Not so oddly, I find that it's not much different than what you've been endorsing, Orlando.

  For example... this is pretty rude and arrogant.

  opc.three (3/25/2013)

  ---

  *yawn*

  Are you kidding? ...on second thought, you are right, I should not have stooped to his level in my response but he deserved it and the amount of effort it took to type those 6 letters was all it warranted.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 8:49 am

  #1600846

  Jeff Moden (3/26/2013)

  ---

  opc.three (3/25/2013)

  ---

  Jeff Moden (3/25/2013)

  ---

  opc.three (3/24/2013)

  ---

  You're still hung up on 'external attackers.' The point is, xp_cmdshell is a blunt tool that cannot be audited and allows people to run commands as someone else, possibly with more permissions than their own, without the possibility of being detected or tracked. That is not something to be taken lightly and is certainly something most people making decisions about the security of their environment and data would object too if it was fully explained.

  You need to read the question I posed again. I said nothing about 'external attackers'. In fact, I specifically stated that "None of those 'individuals' are actually externally outside SQL server". Here's my post, again.

  Fine. Support your words as I have supported mine. If only few (let's say, 2 DBAs) very trusted individuals have "SA" privs and none of those "individuals" are actually externally outside SQL Server) facing apps (an important point that you've left out that I've emphasized time and again), what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off?

  So tell us all, "what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off"? If the answer is only "logging", please drive through because an "SA" can do just about anything without it being logged and where it is logged, (s)he can actually delete.

  Maybe so, but all of that leaves an audit trail, and holes in the audit trail are an audit trail of their own, and can be grounds for termination. I do not need to make my point any clearer. Like I said to Sergiy, if you want to be in denial about the risks and exposure that leaving xp_cmdshell enabled creates that's your prerogative. But peddling it on these forums as if it is "as safe as a SELECT statement" is simply irresponsible, and I won't let it stand if I run into it.

  Ok. I'll be more specific and simplify my question. You are the DBA for a company. Being a good DBA, you've given no one and no thing "SA" privs except yourself and maintenance tasks in the form of jobs running on SQL Server. You've ensured that the "SA" login password is very long and you don't use it in your daily duties. You only login as yourself.

  xp_CmdShell is turned on because you have stored procedures that have been enabled to use it for your maintenance tasks.

  Whether it be an internal or external user, name all of the users that can use xp_CmdShell.

  Now explain how having xp_CmdShell turned on causes a security hole.

  It doesn't.

  It does if having access to the cmd prompt on the server as the SQL Server service account affords them access to anything they would not normally have access too. Are you sure that is not the case?

  The funny thing your example above is that it is not even close to an honest representation of how we got here, on this thread or on the others where you tout its use and how safe it is. If it were just DBAs doing admin work with it I doubt there would ever be a dust-up. The people I am mostly trying to steer away from using xp_cmdshell are developers. This is where the floodgates open in terms of file share access and poor design patterns that effectively pain people into corners and cost tons of money to refactor later, the definition of an anti-pattern.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 8:50 am

  #1600849

  Michael L John (3/26/2013)

  ---

  Wow. I started a war.

  Do all of you realize that you are saying the same things, but in different contexts?

  Security is not something simple, and to do it correctly requires a lot of work and preparation. There are different steps required to secure SQL from internal attacks as opposed to external attacks.

  Specifically to xp_cmdshell, I say disable it. The analogy is that locking a door keeps honest people honest. That being said, it's not the only thing that needs to be done to secure your system.

  I also said in my original post that T-SQL and batch files are different beasts. By disabling xp_cmdshell, people (developrs!!!) are less inclined to come up with really great ideas.

  No, this is not a complete solution. But it at least makes internal people stop and think.

  And if DBA's are misled into thinking that disabling this completly secures their systems, then they need a lot more education.

  Thank you for your well reasoned, thoughtful and sober comments.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 8:55 am

  #1600853

  Jeff Moden (3/26/2013)

  ---

  Michael L John (3/26/2013)

  ---

  Wow. I started a war.

  Do all of you realize that you are saying the same things, but in different contexts?

  Security is not something simple, and to do it correctly requires a lot of work and preparation. There are different steps required to secure SQL from internal attacks as opposed to external attacks.

  Specifically to xp_cmdshell, I say disable it. The analogy is that locking a door keeps honest people honest. That being said, it's not the only thing that needs to be done to secure your system.

  I also said in my original post that T-SQL and batch files are different beasts. By disabling xp_cmdshell, people (developrs!!!) are less inclined to come up with really great ideas.

  No, this is not a complete solution. But it at least makes internal people stop and think.

  And if DBA's are misled into thinking that disabling this completly secures their systems, then they need a lot more education.

  That's the whole thing. Disabling it does nothing for security even if security is bad. A lot of people who say "disable it" just aren't understanding that.

  It does something. It is a deterrent and causes attempts to enable it to be logged somewhere. Yes those can be subverted, but it makes it that much harder to go on undetected and for less-skilled attackers it may be the difference between them doing damage.

  I have cited several reasons and scenarios where xp_cmdshell could be a security threat. What about the people that changed from having it enabled by default after install to disabled by default after install, i.e. Microsoft between SQL 2000 and SQL 2005? What about all the security Best Practices papers out there that recommend leaving it disabled? Maybe I should be asking why you are so attached to using xp_cmdshell and why you are defending it to the end'th degree. What is your stake in seeing xp_cmdshell be enabled and used on so many people's systems? There are so many more robust tools with less explicit and latent vulnerabilities that in today's world there is simply no reason to use xp_cmdshell, it simply is not worth it.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Michael L John

  One Orange Chip

  Points: 27056

  More actions

  March 26, 2013 at 11:41 am

  #1600925

  I have an idea.

  This debate can go on for another 20000 pages of posts, and it will still be confusing, misleading, and certainly not productive.

  Remember the pointof this website, and these forums: To share information with memebers of the SQL community.

  We are not doing that very well.

  How about an article about securing SQL server? Jeff, you can do a section on the "misinformation", opc.three, you can do a section on the steps for internal and external, and Sergiy, you can do a best practices section.

  I will proof-read and point out all of the mistakes!:-P

  Instead of endlessly debating, we can actually collaborate. Maybe even be productive.

  Anyone up for it?

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 12:35 pm

  #1600937

  I'm game.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  March 26, 2013 at 2:26 pm

  #1600964

  Michael L John (3/26/2013)

  ---

  Specifically to xp_cmdshell, I say disable it. The analogy is that locking a door keeps honest people honest.

  The analogy is - you're locking the door and leave the key in the door.

  It's right there - in the lock. Whoever who could open the door can turn the key as well. Any time.

  It's even worse than leaving the door unlocked.

  Because you get honest people tempting.

  It's a human nature - to test the boundaries. You add another boundary - and you draw people to push it, even those who would not think about it before.

  Logic is simple - if they start to lock the door there must be something valuable behind it.

  I better go check.

  And I'll put some effort into making sure you can never know about my "visit".

  Some could even consider doing some damage to the system just to show stupid is the attempt to lock the system this way and prove the incompetence of the one who suggested it.

  I'd say even honest people could find this reason good enough to go where you do not want them (and they did not want themselves) to go.

  And for rogue employees - it's just a gold mine!

  Best part - they can lock the door after doing the deed - so you won't be looking there to find out what's happened.

  OK, enough about the negative outcomes.

  Can point on a positive side of disabling xp_cmdshell?

  _____________
  Code for TallyGenerator

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 26, 2013 at 7:02 pm

  #1600995

  opc.three (3/26/2013)

  ---

  I'm game.

  On second thought, count me out. How ridiculous...

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  March 26, 2013 at 7:11 pm

  #1600997

  How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?

  (I know the user running the job will have been configured to have minimal permissions, but it still may have access to resources the attacker wouldn't normally have access to)

  And SSIS packages that can FTP / email / perform file operations / run ad-hoc .net code - are they ok ?

  Don't they also provide the opportunity for an "attacker" known or unknown to perform tasks with permissions other than their own?

  Or how about someone gaining access to your workstation or the server and using SQLCMD mode in SSMS to run operating system commands? (assuming you have already locked down the dos prompt and the windows Run command and the "Run..." command on the windows task manager and the File...Open dialogs in Office)...

  Oh hold on, while I was typing this, someone stole my server...damn it!

  :hehe:

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]