Post reply

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS)

  • June 12, 2013 at 11:45 am

    #1623451

    opc.three (6/12/2013)

    As for MS supposedly "coming to their senses with xp_CmdShell" goes, just because you disagree with something, doesn't make it bad. It just makes it bad for you.

    It's not about like. It's about empirical knowledge. I have literally logged hundreds, if not into the thousands of hours working with applications built around xp_cmdshell going back to the SQL 7 days. That is not even mentioning DBA solutions. I support an app today that uses ECHO and > to log to a file from T-SQL using xp_cmdshell. What a waste of time.

    Anyway, it has been more than enough to learn and experience all the pros and cons of using it. From this I know it's worth steering people away from it. It's simply a shitty tool from a security perspective, an application design perspective, a maintenance perspective, a system stability perspective, from an interface perspective, the list goes on and on.

    Me too (hundreds, if not thousands of hours). Fortunately, I've not run into stupidity like using ECHO in the manner you've described. Such stupidity, however, isn't limted to xp_CmdShell. It permeates every facet of code in the wrong hands.

    As you might guess, I'll continue to disagree about it being a "shitty" tool from a security perspective. You can do just as much damage with SSIS that goes out to scripts or uses WMI blocks. You've apparently had as many bad experiences with crappy developers that use xp_CmdShell as I've had with crappy developers that write scripts for SSIS and the like. You and I have had identical problems, just with different products. I guess that's why that even though I totally disagree with your stance on xp_CmdShell and you totally disagree with my stance on other tools, we haven't actually tried to remove each other's heads... yet. 😀

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2013 at 11:55 am

    #1623457

    opc.three (6/12/2013)

    sneumersky (6/12/2013)

    Can SQL_Enthusiast's issue be solved with a WMI task or data reader? I have not used it in ages, nor am I a WQL expert 🙁

    A WMI Data Reader, maybe, since you can issue an "select" from the file system and the WQL supports the order by. I'll try it out and post back. Would that qualify as "native" SSIS for you Jeff since there is a "WMI Data Reader Task" built into SSIS?

    If it works correctly, it doesn't matter if it's "native" SSIS or not.

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2013 at 12:29 pm

    #1623465

    Jeff Moden (6/12/2013)

    opc.three (6/12/2013)

    As for MS supposedly "coming to their senses with xp_CmdShell" goes, just because you disagree with something, doesn't make it bad. It just makes it bad for you.

    It's not about like. It's about empirical knowledge. I have literally logged hundreds, if not into the thousands of hours working with applications built around xp_cmdshell going back to the SQL 7 days. That is not even mentioning DBA solutions. I support an app today that uses ECHO and > to log to a file from T-SQL using xp_cmdshell. What a waste of time.

    Anyway, it has been more than enough to learn and experience all the pros and cons of using it. From this I know it's worth steering people away from it. It's simply a shitty tool from a security perspective, an application design perspective, a maintenance perspective, a system stability perspective, from an interface perspective, the list goes on and on.

    Me too (hundreds, if not thousands of hours). Fortunately, I've not run into stupidity like using ECHO in the manner you've described. Such stupidity, however, isn't limted to xp_CmdShell. It permeates every facet of code in the wrong hands.

    A fair point, could be said of anything though. The goal of any piece of software (and framework) should be to guide developers into good ways of doing things that benefit not only the environment and the people working elbow to elbow with each other, but also those that will eventually come after them in that environment. In my estimation, and from seeing what developers typically produce and have left behind when left to their own devices when using it, xp_cmdshell guides developers into, practically promotes, very poor implementations. This is primarily due to the intrinsic properties of it as a piece of software. For these reasons, I could never, ever, recommend using it, ever, for anything, ever.

    As you might guess, I'll continue to disagree about it being a "shitty" tool from a security perspective. You can do just as much damage with SSIS that goes out to scripts or uses WMI blocks.

    No you can't Jeff. You can lock an SSIS Package execution into running under a service account that is not comingled with the SQL Server service account. You cannot do that with xp_cmdshell. It's a blunt tool that paints you into a corner, namely a corner of running everything under one account where a sysadmin member gets to potentially elevate and definitely obfuscate their identity. For goodness sake man, you have to see this by now. Just accept it already.

    You've apparently had as many bad experiences with crappy developers that use xp_CmdShell as I've had with crappy developers that write scripts for SSIS and the like. You and I have had identical problems, just with different products. I guess that's why that even though I totally disagree with your stance on xp_CmdShell and you totally disagree with my stance on other tools, we haven't actually tried to remove each other's heads... yet. 😀

    Security-wise it's awful. Design-wise it's awful. Stability-wise it's awful. It's shitty man, and it ain't that serious to go after your head...unless you meant figuratively to try to win over your mind on the matter. That my friend has become a passive hobby of sorts for me at this point 😉

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

  • June 12, 2013 at 12:31 pm

    #1623467

    Jeff Moden (6/12/2013)

    opc.three (6/12/2013)

    sneumersky (6/12/2013)

    Can SQL_Enthusiast's issue be solved with a WMI task or data reader? I have not used it in ages, nor am I a WQL expert 🙁

    A WMI Data Reader, maybe, since you can issue an "select" from the file system and the WQL supports the order by. I'll try it out and post back. Would that qualify as "native" SSIS for you Jeff since there is a "WMI Data Reader Task" built into SSIS?

    If it works correctly, it doesn't matter if it's "native" SSIS or not.

    Well, you pointing out using a Script Task as SSIS "needing a little help" seemed to imply that you were drawing a parallel between T-SQL needing xp_cmdshell to accomplish certain things non-natively, and SSIS needing .NET to do the same, but that parallel cannot be drawn.

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

  • June 12, 2013 at 2:43 pm

    #1623507

    opc.three (6/12/2013)

    In my estimation, and from seeing what developers typically produce and have left behind when left to their own devices when using it, xp_cmdshell guides developers into, practically promotes, very poor implementations. This is primarily due to the intrinsic properties of it as a piece of software. For these reasons, I could never, ever, recommend using it, ever, for anything, ever.

    My turn to say that's a fair point. I'll also say the same thing of many developers that use any tool included but certainly not limited to PowerShell. Look at some of the articles that fall into the category of "Look What I Can Do with PowerShell" and I believe you'll agree. It's not just xp_CmdShell that provides the lure to the side of idiocy.

    As you might guess, I'll continue to disagree about it being a "shitty" tool from a security perspective. You can do just as much damage with SSIS that goes out to scripts or uses WMI blocks.

    No you can't Jeff. You can lock an SSIS Package execution into running under a service account that is not comingled with the SQL Server service account. You cannot do that with xp_cmdshell. It's a blunt tool that paints you into a corner, namely a corner of running everything under one account where a sysadmin member gets to potentially elevate and definitely obfuscate their identity. For goodness sake man, you have to see this by now. Just accept it already.

    Fine. Show me an absolutely guaranteed way to keep an "SA" attacker from getting to the command line in SQL Server (not just through xp_CmdShell) and perhaps I'll reconsider it. I say "perhaps" because I actually do like xp_CmdShell and the fact that I don't have to go anywhere near an SSIS installation to do ETL or my job as a DBA.

    You've apparently had as many bad experiences with crappy developers that use xp_CmdShell as I've had with crappy developers that write scripts for SSIS and the like. You and I have had identical problems, just with different products. I guess that's why that even though I totally disagree with your stance on xp_CmdShell and you totally disagree with my stance on other tools, we haven't actually tried to remove each other's heads... yet. 😀

    Security-wise it's awful. Design-wise it's awful. Stability-wise it's awful. It's shitty man, and it ain't that serious to go after your head...unless you meant figuratively to try to win over your mind on the matter. That my friend has become a passive hobby of sorts for me at this point 😉

    And I disagree with all of that especially when I've seen some of the alternatives that people have come up with for what should be very simple tasks.

    I guess we'll have to continue to continue to disagree.

    Getting back to the subject at hand, I haven't actually written any code on this thread that uses xp_CmdShell because it's not needed.

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2013 at 3:38 pm

    #1623520

    Jeff Moden (6/12/2013)

    opc.three (6/12/2013)

    In my estimation, and from seeing what developers typically produce and have left behind when left to their own devices when using it, xp_cmdshell guides developers into, practically promotes, very poor implementations. This is primarily due to the intrinsic properties of it as a piece of software. For these reasons, I could never, ever, recommend using it, ever, for anything, ever.

    My turn to say that's a fair point. I'll also say the same thing of many developers that use any tool included but certainly not limited to PowerShell. Look at some of the articles that fall into the category of "Look What I Can Do with PowerShell" and I believe you'll agree. It's not just xp_CmdShell that provides the lure to the side of idiocy.

    As you might guess, I'll continue to disagree about it being a "shitty" tool from a security perspective. You can do just as much damage with SSIS that goes out to scripts or uses WMI blocks.

    No you can't Jeff. You can lock an SSIS Package execution into running under a service account that is not comingled with the SQL Server service account. You cannot do that with xp_cmdshell. It's a blunt tool that paints you into a corner, namely a corner of running everything under one account where a sysadmin member gets to potentially elevate and definitely obfuscate their identity. For goodness sake man, you have to see this by now. Just accept it already.

    Fine. Show me an absolutely guaranteed way to keep an "SA" attacker from getting to the command line in SQL Server (not just through xp_CmdShell) and perhaps I'll reconsider it. I say "perhaps" because I actually do like xp_CmdShell and the fact that I don't have to go anywhere near an SSIS installation to do ETL or my job as a DBA.

    I already did.

    http://www.sqlservercentral.com/Forums/FindPost1450182.aspx

    I showed how to recognize that a call to the cmd shell was being made within Windows using WMI. Granted, that exercise only detected the occurrence to report it, but intercepting the call and turning it away based on whether the parent process was the sqlsrvr.exe is no doubt possible. These types of hooks can be added to Windows, and I am confident third party software exists to do this and that they are running it in my current environment because I get calls about cmd shells running on my servers and have to file security exceptions so I am not bothered about it.

    You've apparently had as many bad experiences with crappy developers that use xp_CmdShell as I've had with crappy developers that write scripts for SSIS and the like. You and I have had identical problems, just with different products. I guess that's why that even though I totally disagree with your stance on xp_CmdShell and you totally disagree with my stance on other tools, we haven't actually tried to remove each other's heads... yet. 😀

    Security-wise it's awful. Design-wise it's awful. Stability-wise it's awful. It's shitty man, and it ain't that serious to go after your head...unless you meant figuratively to try to win over your mind on the matter. That my friend has become a passive hobby of sorts for me at this point 😉

    And I disagree with all of that especially when I've seen some of the alternatives that people have come up with for what should be very simple tasks.

    I guess we'll have to continue to continue to disagree.

    That's fine. You were the one that brought it up this time. All I was pointing out was that xp_dirtree was undocumented, which it is, and some code to solve the OP's problem in the technology that was asked about.

    Getting back to the subject at hand, I haven't actually written any code on this thread that uses xp_CmdShell because it's not needed.

    You're right, an SSIS Script Task will do the job just fine.

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

  • June 12, 2013 at 4:19 pm

    #1623528

    opc.three (6/12/2013)

    I already did.

    http://www.sqlservercentral.com/Forums/FindPost1450182.aspx

    I showed how to recognize that a call to the cmd shell was being made within Windows using WMI. Granted, that exercise only detected the occurrence to report it, but intercepting the call and turning it away based on whether the parent process was the sqlsrvr.exe is no doubt possible.

    My apologies. This is the first I've seen that post. I flat out missed it because of all the emails I get. Thanks for taking the time.

    Still, we're in the should-be-possible mode. It would be nice to see and test the code that actually does the job and then see if things like backups from SQL Server will still work.

    It would also be nice if someone finished the VB script example.

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2013 at 4:36 pm

    #1623530

    Might have finally found a method to prevent execution of Cmd.exe.

    http://technet.microsoft.com/en-us/library/cc975912.aspx

    I'll have to give this a try.

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2013 at 9:03 pm

    #1623552

    Ah, crud. As soon as I find a method to prevent usage of cmd.exe, I find someone with easy methods to blow through the prevention. I've updated the thread we started earlier.

    http://www.sqlservercentral.com/Forums/FindPost1462882.aspx

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 13, 2013 at 2:29 am

    #1623592

    Jeff Moden (6/12/2013)

    Ah, crud. As soon as I find a method to prevent usage of cmd.exe, I find someone with easy methods to blow through the prevention. I've updated the thread we started earlier.

    http://www.sqlservercentral.com/Forums/FindPost1462882.aspx

    Me too:

    http://www.sqlservercentral.com/Forums/FindPost1462934.aspx

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

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply