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 Thursday, April 25, 2013 11:19 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:13 AM
Points: 35,996, Visits: 30,290
opc.three (4/24/2013)
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


Good God, Man! At this point, I'm not even talking about xp_CmdShell. I'm talking about all the "security benefits" that you say PowerShell has to offer and I'm seeing no more than if someone were doing simple deletes at a command prompt using either DOS commands or PowerShell.

You specifically say...
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...


Name some where PowerShell has an advantage over simple DOS commands. Where are and what are all these "layers" you speak of because I see none that you've offered, so far. And remember... I'm NOT talking about xp_CmdShell. I'm talking about DOS commands at the command prompt and PowerShell at the PS prompt. Period!


--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." -- 04 August 2013
(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 #1446795
Posted Thursday, April 25, 2013 11:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
I never said a regular PowerShell session offers more security than a regular cmd shell session when not running via xp_cmdshell. I said PowerShell is more robust than cmd shell and offers more functionality.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1446802
Posted Friday, April 26, 2013 1:08 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
opc.three (4/25/2013)
I never said a regular PowerShell session offers more security than a regular cmd shell session when not running via xp_cmdshell. I said PowerShell is more robust than cmd shell and offers more functionality.


Actually, you did.

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.



Someone sitting at a PS prompt and deleting files has no more chance of being caught than someone using any other method including xp_Cmdshell.


--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." -- 04 August 2013
(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 #1446822
Posted Friday, April 26, 2013 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Jeff Moden (4/26/2013)
opc.three (4/25/2013)
I never said a regular PowerShell session offers more security than a regular cmd shell session when not running via xp_cmdshell. I said PowerShell is more robust than cmd shell and offers more functionality.


Actually, you did.

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.



Someone sitting at a PS prompt and deleting files has no more chance of being caught than someone using any other method including xp_Cmdshell.

Not true, you're misunderstanding.

A stand-alone cmd shell prompt running on Homer's machine is not the same as a cmd ahell prompt reached via xp_cmdshell in a T-SQL session Homer is running within.

A stand-alone PowerShell prompt on Homer's machine does not offer much over a stand-alone CmdShell prompt on Homer's machine in the way of added security, only in functionality. Both shells are running as Homer, from Homer's machine IP so actions from both are subject to OS level auditing under his username -and- network level auditing under his username and IP address. When Homer accesses a cmd shell promo via xp_shell neither of those things are true.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1446950
Posted Friday, April 26, 2013 7:37 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 35,996, Visits: 30,290
I understand that working from the command prompt isn't the same as working through xp_CmdShell (seriously elevated privs can be found there). It was sounding like you were saying that PowerShell offered some form of security at the command prompt (not xp_CmdShell) above a normal DOS command session at the command prompt. My mistake.

Heh... so far as xp_CmdShell usage goes, I'll just remind folks that I disagree with you on the subject and move on instead of replaying all that you and I have been through in the past.


--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." -- 04 August 2013
(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 #1446996
Posted Friday, April 26, 2013 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Jeff Moden (4/26/2013)
I understand that working from the command prompt isn't the same as working through xp_CmdShell (seriously elevated privs can be found there). It was sounding like you were saying that PowerShell offered some form of security at the command prompt (not xp_CmdShell) above a normal DOS command session at the command prompt. My mistake.

At the local prompt itself, no, not by default. As a scripting platform, absolutely. PowerShell has security scaffolding in place that can prevent command-hijacking (e.g. someone dropping a file named DIR.bat containing malicious code into a directory) and prevent script tampering (see PowerShell Script signing) as well as script execution policies to support such ideas.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1447053
Posted Friday, April 26, 2013 9:34 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
opc.three (4/26/2013)
A stand-alone PowerShell prompt on Homer's machine does not offer much over a stand-alone CmdShell prompt on Homer's machine in the way of added security, only in functionality. Both shells are running as Homer, from Homer's machine IP so actions from both are subject to OS level auditing under his username -and- network level auditing under his username and IP address. When Homer accesses a cmd shell promo via xp_shell neither of those things are true.


When Homer accesses a cmd shell promo via xp_shell - nothing happens.
Unless Homer is given SA privileges.

And if Homer is given same kind of privileges on the Windows domain - "neither of those things are true".
He can do whotever he wants from whereever he wants, remotely accessing any server/desktop around with a little chance of being caught.

Get your security within SQL Server right, at least at the same level as within Windows domains - and all your imaginary hazards of xp_cmdshell will go away.

Post #1447065
Posted Friday, April 26, 2013 9:59 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
opc.three (4/24/2013)
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.


Which is plainly stupid.
Yes, MS settings are stupid, and all those "Security Best Practices" are stupid.

Because - there is no way to disable xp_cmdshell for those users which could possibly run it.
If a user has privileges to execute xp_cmdshell the very same user has privileges to enable/disable it at any moment.

You may only "disable" xp_cmdshell by some HR policy.
But it will only prevent constructive, controllable use of the functionality, with no effect on any malicious activity.
Because malicious activity is not about following company rules - that's why it's named malicious.

Disabling xp_cmdshell for security reasons is like replacing a front door to your house with a turnpike accompanied with a strong warning about crosing the line.
It may stop only good people from entering your house.
But thiefs - they will only laugh at such "security". And strong warning will only remind them to be careful and not leave fingerprints inside (thank you, hosts).

How stupid does that sound?
Post #1447078
Posted Friday, April 26, 2013 10:05 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
opc.three (4/26/2013)
PowerShell has security scaffolding in place


You might be surprised - but SQL Server has it too.

And cowboy developers may (most certainly will) ignore that scaffolding in PowerShell as well as they do it in SQL Server.

Unless a professional admin (DBA in case of SQL Server) will force them to use it.
Post #1447079
Posted Friday, April 26, 2013 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Sergiy (4/26/2013)
opc.three (4/26/2013)
A stand-alone PowerShell prompt on Homer's machine does not offer much over a stand-alone CmdShell prompt on Homer's machine in the way of added security, only in functionality. Both shells are running as Homer, from Homer's machine IP so actions from both are subject to OS level auditing under his username -and- network level auditing under his username and IP address. When Homer accesses a cmd shell promo via xp_shell neither of those things are true.


When Homer accesses a cmd shell promo via xp_shell - nothing happens.
Unless Homer is given SA privileges.

And if Homer is given same kind of privileges on the Windows domain - "neither of those things are true".
He can do whotever he wants from whereever he wants, remotely accessing any server/desktop around with a little chance of being caught.

Get your security within SQL Server right, at least at the same level as within Windows domains - and all your imaginary hazards of xp_cmdshell will go away.


You (and Jeff) are so wrong about this it's not even worth discussing anymore because it's clear you will not see the point.


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1447086
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse