Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»

How to call a batch file to execute from an SP Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 9:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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

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



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
Post #1435750
Posted Tuesday, March 26, 2013 9:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1435752
Posted Tuesday, March 26, 2013 9:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1435753
Posted Tuesday, March 26, 2013 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1435754
Posted Tuesday, March 26, 2013 10:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1435765
Posted Tuesday, March 26, 2013 10:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
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!



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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1435766
Posted Wednesday, March 27, 2013 1:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:27 PM
Points: 1,806, Visits: 5,863
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!



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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1435790
    Posted Wednesday, March 27, 2013 7:52 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 6:02 AM
    Points: 7,135, Visits: 12,744
    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!



    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
    Post #1435915
    Posted Wednesday, March 27, 2013 1:22 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 1:53 AM
    Points: 35,540, Visits: 32,123
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1436125
    Posted Wednesday, March 27, 2013 2:34 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 6:02 AM
    Points: 7,135, Visits: 12,744
    ...removed post...

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1436161
    « Prev Topic | Next Topic »

    Add to briefcase «««678910»»

    Permissions Expand / Collapse