Why powershell?

Question

Why powershell?

Viewing 15 posts - 16 through 30 (of 35 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

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.

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

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.

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

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 :rolleyes: :hehe:

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

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 :rolleyes: :hehe:

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.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 8

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.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 10

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.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 11

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

Sergiy SSC Guru Points: 110206 More actions · Answer 12

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.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 13

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?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 14

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.

_____________
Code for TallyGenerator

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 15

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