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 ««1234»»»

Why powershell? Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 5:46 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (4/23/2013)
Jeff Moden (4/23/2013)
opc.three (4/23/2013)
If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you. PowerShell is also very good in a distributed environment where we need to communicate with and manage multiple instances that may not be directly reachable from one another rendering tools like Linked Servers and xp_cmdshell less than useful.

You could use tools like xp_cmdshell, the OLE Automation Procedures, Linked Servers, or other functionality built into T-SQL but you may find that in a security-conscious environment tools that access the file system or network from within T-SQL may not be sanctioned for use or will simply not be able to get the job done.


Gosh... sorry to be an itch but there's nothing about PowerShell that's any more secure than the other methods. There's no natural logging and there's certainly no natural traces on who accessed what or who deleted what using PowerShell. Yes, I absolutely agree that it's a wonderful tool... but not because someone is security conscious because, like the other tools, it offers no natural security.

There is no need to apologize Jeff. I know where you're coming from, but once again I must call you out on the falsehood of one of your many arguments in this area. Employing PowerShell for some tasks does in fact bring with it far fewer barriers in the areas of auditing and securing an environment than do some of the T-SQL methods. Now I'll apologize...sorry, but there really is no denying that fact


Correct me if I'm wrong... If you're sitting at a command prompt and you delete files using either DOS or Powershell, there will be no difference. To log the deletes, you have to turn something else on to log it. PowerShell has no advantage there. It also doesn't trace you activity any more than running a batch file would unless you have some other tool running.

Yep... I agree that if you do it through an SQL Server Job, SSIS, or xp_CmdShell, the only logging it may do is to identify the server/service that did the deletes. No arguments there. But just using PowerShell over something else is no real security enhancement. It's "just" another shell.


--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 #1445730
Posted Tuesday, April 23, 2013 6:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
The point is, using xp_cmdshell limits your options. It paints you into a corner. PowerShell has 100% functionality coverage, and infinitely more, over CmdShell. And when run separate from SQL Server we are afforded so many more options from an auditing and security standpoint it's really no contest, really, no contest. How anyone could continue to argue the contrary is beyond me.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1445738
Posted Wednesday, April 24, 2013 4:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
VBS is out of fashion.
There is a new kid on the block with huge marketing budget attached to it.
Everybody must immediately forget about the old and proven tool.
Post #1445839
Posted Wednesday, April 24, 2013 4:57 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (4/23/2013)
The point is, using xp_cmdshell limits your options. It paints you into a corner. PowerShell has 100% functionality coverage, and infinitely more, over CmdShell. And when run separate from SQL Server we are afforded so many more options from an auditing and security standpoint it's really no contest, really, no contest. How anyone could continue to argue the contrary is beyond me.


Heh... it hasn't painted me into any corner. I can do everything I need to do with files in a flexible and easy to do manner using the "old" ways.

You keep talking about all of the options for auditing for PowerShell. What are they, how do you implement them, and why are they any different that auditing DOS commands? And so far as security goes, what exactly is the advantage of PowerShell over, say, using a DOS DEL command operated from the Command Prompt?


--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 #1445854
Posted Wednesday, April 24, 2013 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Xp_cmdshell does not maintain a users identity all the way through the stack, which impedes auditing and allows for obfuscation of the identity of the person running the command.

Once you start building process around it, it opens you up for ad hoc requests from malicious users because its enabled and execution of it is hard to differentiate.

It's just hard to justify why anyone would ever start using it when there are more feature-rich, secure, and auditable tools available.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1445981
Posted Wednesday, April 24, 2013 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Personally I would not choose it for new development because I think PowerShell is a better choice, but I don't have a problem with VBS and currently support some of it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1445989
Posted Wednesday, April 24, 2013 12:11 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (4/24/2013)
Xp_cmdshell does not maintain a users identity all the way through the stack, which impedes auditing and allows for obfuscation of the identity of the person running the command.

Once you start building process around it, it opens you up for ad hoc requests from malicious users because its enabled and execution of it is hard to differentiate.

It's just hard to justify why anyone would ever start using it when there are more feature-rich, secure, and auditable tools available.


I'm not even talking about xp_CmdShell. I'm talking about some Homer sitting in front of the keyboard at the DOS prompt. He needs to delete some files. He deletes some using DOS commands and some using PowerShell. Where are the audit logs and what do they say about who did what with each method?

And, no... having xp_CmdShell turned on doesn't open you up for requests from malicious users. Allowing those malicious users to get in as SA is the only problem but you already know that.


--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 #1446164
Posted Wednesday, April 24, 2013 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Jeff Moden (4/24/2013)
opc.three (4/24/2013)
Xp_cmdshell does not maintain a users identity all the way through the stack, which impedes auditing and allows for obfuscation of the identity of the person running the command.

Once you start building process around it, it opens you up for ad hoc requests from malicious users because its enabled and execution of it is hard to differentiate.

It's just hard to justify why anyone would ever start using it when there are more feature-rich, secure, and auditable tools available.


I'm not even talking about xp_CmdShell. I'm talking about some Homer sitting in front of the keyboard at the DOS prompt. He needs to delete some files. He deletes some using DOS commands and some using PowerShell. Where are the audit logs and what do they say about who did what with each method?

Who cares? Maybe nowhere. Maybe somewhere. But either way the files were deleted while Homer was logged in as himself. Do the same through xp_cmdshell and the delete would be logged against the SQL Server service account, or the xp_cmdshell proxy account.

And, no... having xp_CmdShell turned on doesn't open you up for requests from malicious users.

Sure it does. It gives cover for malicious users whose actions would not be easily differentiated from one other users running xp_cmdshell or automated processes running xp_cmdshell.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1446199
Posted Wednesday, April 24, 2013 3:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
opc.three (4/24/2013)
Who cares? Maybe nowhere. Maybe somewhere. But either way the files were deleted while Homer was logged in as himself. Do the same through xp_cmdshell and the delete would be logged against the SQL Server service account, or the xp_cmdshell proxy account.


The real fact of the matter is that most of the world has no auditing at that level so claims of added security in that area are absolutely bogus.

And, no... having xp_CmdShell turned on doesn't open you up for requests from malicious users.

Sure it does. It gives cover for malicious users whose actions would not be easily differentiated from one other users running xp_cmdshell or automated processes running xp_cmdshell.


Unless a malicious user got in with SA privs, they can't do anything with xp_CmdShell even if it's turned on. If a malicious user gets in with SA privs, they can turn xp_CmdShell on and the logging that then occurs is simple testimony to how bad your security was. Having it turned off did nothing to increase security unless you consider the blood stain in your log to be an increase in security.


--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 #1446249
Posted Wednesday, April 24, 2013 10:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
Jeff Moden (4/24/2013)
opc.three (4/24/2013)
Who cares? Maybe nowhere. Maybe somewhere. But either way the files were deleted while Homer was logged in as himself. Do the same through xp_cmdshell and the delete would be logged against the SQL Server service account, or the xp_cmdshell proxy account.


The real fact of the matter is that most of the world has no auditing at that level so claims of added security in that area are absolutely bogus.

And, no... having xp_CmdShell turned on doesn't open you up for requests from malicious users.

Sure it does. It gives cover for malicious users whose actions would not be easily differentiated from one other users running xp_cmdshell or automated processes running xp_cmdshell.


Unless a malicious user got in with SA privs, they can't do anything with xp_CmdShell even if it's turned on. If a malicious user gets in with SA privs, they can turn xp_CmdShell on and the logging that then occurs is simple testimony to how bad your security was. Having it turned off did nothing to increase security unless you consider the blood stain in your log to be an increase in security.

Awesome...so you're down to labeling an exposure introduced by using xp_cmdshell as "bogus" and calling audit trails a "blood stain in the log"... That's OK, dismiss, but it doesn't change the fact that xp_cmdshell is less auditable, less secure, and just a plain old bad idea when it comes to designing applications when compared to almost any other available option.

All kinds of auditing can take place at the OS level and the network level, most or all of which a DBA team will have no idea are in place let alone have access too in order to manage or defeat. Layering...

It's no use...honestly...if you want to accept the risk in using xp_cmdshell, fine, that's your prerogative. I have said it many times before, and I continue to say it, it's your right to choose. Where I take issue is where you portray xp_cmdshell as secure and safe for use in the large and general sense. It's clearly not a secure option to have enabled in an environment and is absolutely not a good choice for any kind of application development, regardless of how you think it is being leveraged directly, or indirectly via a locked down stored proc. I would also like to mention again that Microsoft has initialized the xp_cmdshell option as "disabled" since SQL 2005 and just about every "Security Best Practices" document, book or respected figure on the topic recommends leaving it disabled.

You mentioned the simple DOS DEL command and asked how PowerShell might do something better. How about deleting all the files in a sub-directory with a specific file mask that are older than n-timeunits and are in a folder anywhere in the hierarchy that starts with a Q, or some other seemingly contrived set of requirements that oddly enough slips into our real-world set of requirements? Could you do that with a mix of DOS, T-SQL and xp_cmdshell by inspecting rows in a table returned from a DIR /S and then issuing lot's of DEL, sure, but that would be gobs of code and I can do that in one line of PowerShell code. Point is, DOS is good for some things but its a blunt tool. Yeah, I used to spin up the occasional Windows Batch file back in the day, but now...why bother? Because "it works"? Yeah, "it works", for some things, but PowerShell works better and there are lots of things PowerShell can do that CmdShell will never be able to touch in terms of functionality.

But this is less a conversation about PowerShell or indictment of DOS as it is about how xp_cmdshell is a bad choice in and of itself, and it clearly is a bad choice. I love you man, but I really hope one day you will realize the error of your ways and leave xp_cmdshell behind. I know of a fantastic site where people talk about solutions that do not involve accessing the file system from within T-SQL that could serve as a great resource


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

Add to briefcase ««1234»»»

Permissions Expand / Collapse