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: 8233 Visits: 14368
Sergiy (3/26/2013)
opc.three (3/26/2013)
[quote]Sergiy (3/26/2013)
He was not referring to sp_configure at all.

Oh, really?
What was that about then?

http://www.galileowaswrong.com/galileowaswrong
You're a clown, that's funny :-P

It's sad you don't even realise how pathetic you are...
Ermm


This from a guy that argues about the optimizer with Paul White...get a clue troll.

__________________________________________________________________________________________________
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: 8233 Visits: 14368
Jeff, I respect your position, however vehemently I might disagree with it. You make valid points but for me, xp_cmdshell is not and never will be cast in a positive light and hope it is eventually dropped from the product like some of the other Extended Stored Procedures that have started to get picked off as the releases roll by.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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: 44985 Visits: 39872
opc.three (3/26/2013)
Jeff, I respect your position, however vehemently I might disagree with it. You make valid points but for me, xp_cmdshell is not and never will be cast in a positive light and hope it is eventually dropped from the product like some of the other Extended Stored Procedures that have started to get picked off as the releases roll by.


I also respect your personal position on the subject but you're still missing the point. I don't mind you casting a bad light on xp_CmdShell and recommending that people don't use it even if I disagree with that position. Tell them about the alternatives that you use. I just don't want people to think that turning xp_CmdShell off provides them with any form of protection from command line usage because it doesn't. Any user with "SA" privs can get to the command line in a totally undetectable fashion even if xp_CmdShell is turned off and bad security is the real problem.

--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 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
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: 44985 Visits: 39872
opc.three (3/26/2013)
Sergiy (3/26/2013)
opc.three (3/26/2013)

Securing SQL Server by Denny Cherry:
- page 153 recommends to "disable xp_cmdshell"
- page 161 recommends "removing the extended stored proc xp_cmdshell" but goes on to say that (paraphrased) "you may need to add them back before doing system upgrades and they can be re-added by a crafty attacker with the right level of permissions and knowledge of the system"


OK, another one fallen into the same misconception.
Not really surprising.
Jeff pointed out that it's a very common one.

Denny left the back door open for him to escape though.
Still not sure that knowing how to use "sp_configure" makes you some kind of crafty one.

He was not referring to sp_configure at all.

http://www.galileowaswrong.com/galileowaswrong

You're a clown, that's funny :-P


What Sergiy is saying (or at least the way I took it) is that it's actually super easy to add xp_CmdShell back into the system and use it (I just got done reading about a tool that hackers {both internal and external} can use that actually does it automatically) if you have "SA" privs. Then all you have to do is use sp_Configure to turn it on or use the self-deleting Job hack.

He's also saying that just because the "church" of SQL says it isn't so, they're not always right even if there are millions of people of the same opinion.

--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 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: 8233 Visits: 14368
Jeff, you're a gentleman and a scholar, and it distresses me that much more that I cannot make my point clearly. You have seen countless of my posts. I absolutely do recommend alternatives to xp_cmdshell, namely PowerShell, SSIS, .NET, anything but xp_cmdshell. One more item to clear up, I absolutely alert folks to keep control of their list of sysadmin members, but again that's only one part if the story.

I think you are ignoring a significant point which is that security must be layered throughout an environment. Leaving xp_cmdshell enabled, and not protecting a change in its configuration with PBM and possibly even removing the xsp altogether depending on what else the system is tasked with doing, is leaving an available layer out of the mix. If you say that an extremely skilled DBA can get around those roadblocks in a short amount of time and therefore it is not worth adding them, that's apathy in my opinion and does not make for a security strategy. Consider that things don't always go the way you want them to. What if there is an AD group that is in the sysadmin Role because that's how the Enterprise does things and you do not always know or have control over the people who are in the sysadmin Role. It happens. You know what else happens, people leave passwords unprotected in notebooks, whiteboards and other places like in the comments of a website page served publicly.

It takes no time at all to add some additional roadblocks in an "instance setup" script that can have a net positive effect on the security of your data. You are a reasonable person, will you not concede that taking those steps would improve security of the data and improve auditability of the environment, however miniscule you think the improvement might be? I am saying that enabling xp_cmdshell has a negative net effect on security and taking these steps I mentioned has a positive net effect, so there really is no choice. xp_cmdshell has no place in an environment. Yes, there are other areas of weakness in an environment, but why put out a welcome mat?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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: 44985 Visits: 39872
mister.magoo (3/26/2013)
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


I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. :-)

--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 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
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7824
Jeff Moden (3/26/2013)
mister.magoo (3/26/2013)
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


I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. :-)


No, but to be fair (because tension is high enough in here) no-one has suggested it would, but my unasked question there is "would opc.three also disable these tools?" I doubt it.

MM


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




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

  • 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: 8233 Visits: 14368
    mister.magoo (3/27/2013)
    Jeff Moden (3/26/2013)
    mister.magoo (3/26/2013)
    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


    I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. :-)


    No, but to be fair (because tension is high enough in here) no-one has suggested it would, but my unasked question there is "would opc.three also disable these tools?" I doubt it.

    Actually what I am saying is that if you disable xp_cmdshell on all your instances great riches will magically appear in your bank account, great friendship will find you, world peace will descend from the heavens all over the globe, and your instances will automatically be impenetrable from all attackers, internal and external.

    xp_cmdshell is but one attack vector. The others you mentioned also need to be considered and locked down to the best of one's ability in the context of the given environment and the policies and procedures in place. They are actually no less important to do, but it is far less straightforward than simply avoiding xp_cmdshell and taking some of the additional steps I mentioned, and far less of a topic on these forums I might add when it comes to discussing how to get data from instanceA to instanceB with possibly some light transforms in between.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    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: 44985 Visits: 39872
    Or, you could do what I've done. Work with the "team" to properly lock down the system and then use all of those tools as they were intended to be used.

    --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 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: 8233 Visits: 14368
    ...removed post...

    __________________________________________________________________________________________________
    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