Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to call a batch file to execute from an SP


How to call a batch file to execute from an SP

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
Sergiy (3/24/2013)
opc.three (3/24/2013)
[quote]Sergiy (3/24/2013)
Version control. Change management processes. Code review. Layers...


What all these words have to do with stealing data by launching an ad-hoc query using SA privilages?

Or you really believe someone with such intentions would submit such code for peer review before committing???
w00t

:-P I thought it was a silly comment too, but you said script I went with it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
Sergiy (3/24/2013)
opc.three (3/24/2013)
But consider the employee in the sysadmin Role looking to steal data without being detected.


And?
How adding an "sp_configure" command to a script used for stealing data will help to detect who's behind the SA user?

Care to clarify what you meant? Since it is clear now that you did not say "script" to mean something submitted for normal review and deployment.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5822 Visits: 11394
opc.three (3/25/2013)
Care to clarify what you meant? Since it is clear now that you did not say "script" to mean something submitted for normal review and deployment.


Not sure what needs to be clarified here.
How do you imagine a process of stealing data?
I'd see it as someone running a commang against database to retrieve some data and pass it somewhere.
I would expect it to be an SQL statement, most likely more than 1.
Which makes it a script.
http://oxforddictionaries.com/definition/english/script?q=script:
Definition of script
noun
...
Computing: an automated series of instructions carried out in a specific order.

Adding sp_configure command activating xp_cmdshell on top of such script won't create any trouble or hold the process.
What makes disabling xp_cmdshell absolutely useless.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44973 Visits: 39865
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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
Sergiy (3/25/2013)
opc.three (3/25/2013)
Care to clarify what you meant? Since it is clear now that you did not say "script" to mean something submitted for normal review and deployment.


Not sure what needs to be clarified here.
How do you imagine a process of stealing data?
I'd see it as someone running a commang against database to retrieve some data and pass it somewhere.
I would expect it to be an SQL statement, most likely more than 1.
Which makes it a script.
http://oxforddictionaries.com/definition/english/script?q=script:
Definition of script
noun
...
Computing: an automated series of instructions carried out in a specific order.

Adding sp_configure command activating xp_cmdshell on top of such script won't create any trouble or hold the process.
What makes disabling xp_cmdshell absolutely useless.

Look, you do not need to become a jerk. "Script" is not an unambiguous term in the world of SQL Server. I took it to mean "a saved file submitted by a developer for review by a peer and eventual execution by a DBA."

The point is, when you run something as xp_cmdshell you are taking on the identity of the SQL Server service account, which in some environments could mean an elevation of your own privileges, e.g. being able to reach file share you yourself could not reach. I am not going to argue with you. It's clear you do not want to see the point, so I cannot do more.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
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.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5822 Visits: 11394
When one does not want to admit of being wrong he's resotring to personal attacks.

I can clearly see the point. And I spent most of my working time in environments with badly managed security restrictions.
And I used this back door not once.

I just do not see how having xp_cmdshell will stop me from doing exactly what you are trying to prevent - reading from folders which I cannot read and SQL Server can.
The only thing I need to do to overcome your "barrier". or "layer" is to run sp_configure command+reconfigure.
As Jeff pointed it will take less than 3 ms to complete.
If you wish, I could disable it back, to prevent raising an alarm by somebody who's checking the system settings.

So, what do you achive with disabling xp_cmdshell?
Except, of course, false sense of security.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
Sergiy (3/25/2013)
When one does not want to admit of being wrong he's resotring to personal attacks.

:-P You just made my point for me. I think we're done here :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5822 Visits: 11394
opc.three (3/25/2013)
Sergiy (3/25/2013)
When one does not want to admit of being wrong he's resotring to personal attacks.

:-P You just made my point for me. I think we're done here :-)


So, you agree that disabling xp_cmdshell does not have any point.
Do I understand you right?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
Sergiy (3/25/2013)
opc.three (3/25/2013)
Sergiy (3/25/2013)
When one does not want to admit of being wrong he's resotring to personal attacks.

:-P You just made my point for me. I think we're done here :-)


So, you agree that disabling xp_cmdshell does not have any point.
Do I understand you right?

What gave you that impression? Seriously, where are you going with it Sergiy? We have said what we're going to say and we disagree. Have a good evening, I'll see you around :-)

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search