Does anyone have a good reason to run xp_CmdShell?

Question

Does anyone have a good reason to run xp_CmdShell?

Viewing 15 posts - 91 through 105 (of 107 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

opc.three (6/22/2011)

Craig Farrell (6/22/2011)
For anyone following along with the Level II masquerading as a III, here's my next hint for research:
With Sommarskog's help
Hey, I resemble that remark 😀 and if that's where we're going then I could see what this comment of Jeff's was alluding too and that would setup a whole new block to walk around.
I'm not sure any of them would listen if I offered the secure methods in an article especially since both methods will violate another totally unreasonable fear in those same, otherwise intelligent people.

OMG!!!! My brothers and I had those toy bazookas when we were kids. They were like an old "air blaster" on steroids... they throw a ball of air hard enough so that it would make a sound when the "ball" hit the wall 20 feet away. They were freakin' awsome fun especially when Mom would blow a little cigarette smoke into it... you could see the ball of air then.

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

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

Craig Farrell (6/22/2011)
Thankfully, I'm not wearing britches... so I don't care.

Ummmm.... and you're at work? Now I'm the one that's skeered. 😛

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

rmechaber (6/22/2011)
BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?

Yes it absolutely does... just not the way that most people have been led to believe mostly because they've also been led to implementing xp_CmdShell the wrong way.

So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?

That, good Sir, is the missing link that 99.999999% are missing and will be the subject of the article. I've got some more "what if" testing to do in the next couple of weeks but, with the help of the folks on this thread, I've decided it would be a good thing to write so the people can 1) Stop shooting Dad's car and 2) make robust Applications were the logins for the App don't need anything more than standard "PUBLIC" privs. Although the article will include information on how to safely run xp_CmdShell, that last part will be the crux of the article.

--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: 182279 More actions · Answer 4

rmechaber (6/22/2011)
Looks like you must create a Windows account, grant it NTFS permissions to access the relevant shares, etc., then create SQL login based on the Windows user, etc. BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?

One small item I wanted to call out, there is no requirement to create a SQL Server login corresponding to the Windows account referenced in the xp_cmdshell proxy account.

So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?

Welcome to the party 🙂 My guess as to how to accomplish this would qualify as slight of hand...I am happy said article will eventually materialize. Let me be the first post on this thread to explicitly volunteer as a proof-reader 😎

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

Rich Mechaber SSChampion Points: 11130 More actions · Answer 5

Jeff Moden (6/22/2011)
rmechaber (6/22/2011)
BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?
Yes it absolutely does... just not the way that most people have been led to believe mostly because they've also been led to implementing xp_CmdShell the wrong way.
So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?
That, good Sir, is the missing link that 99.999999% are missing and will be the subject of the article. I've got some more "what if" testing to do in the next couple of weeks but, with the help of the folks on this thread, I've decided it would be a good thing to write so the people can 1) Stop shooting Dad's car and 2) make robust Applications were the logins for the App don't need anything more than standard "PUBLIC" privs. Although the article will include information on how to safely run xp_CmdShell, that last part will be the crux of the article.

Ahh, thank you Jeff!. When I read the MS BOL article, I thought this sounded like a less-than-useful approach to security management.

Yet another reason to avoid xp_cmdshell, methinks. Though I am looking forward to your article, mostly so I can learn more about SQL security than anything else.

Whenever I've needed to run command line scripts from SQL, they've been Agent jobs doing nightly administrative work, and I just run them as a CmdExec job step. Maybe I've been lucky I don't have users clamoring to run EXE files from within SQL.

Rich

*has no clever tag line, icon, or slogan*

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

opc.three (6/22/2011)
rmechaber (6/22/2011)
Looks like you must create a Windows account, grant it NTFS permissions to access the relevant shares, etc., then create SQL login based on the Windows user, etc. BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?
One small item I wanted to call out, there is no requirement to create a SQL Server login corresponding to the Windows account referenced in the xp_cmdshell proxy account.

Whoops, nice catch, thanks. I glossed through the BOL too quickly and forgot that the credentials are mapped in SQL using sp_xp_cmdshell_proxy_account.

Rich

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

rmechaber (6/22/2011)
Craig, I looked at BOL for this following your suggestions, and I have a question:
Looks like you must create a Windows account, grant it NTFS permissions to access the relevant shares, etc., then create SQL login based on the Windows user, etc. BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?
So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?

In particular, what this does is make it exactly as securable as I was touting the SSIS could be made, due to the SQLAgent. Now, yes, you can run different proxies, but in the end if you can assign any proxy you have the equivalent of one login with all the proxies' allowances, just maybe not at once.

So, what this in particular does is allow you to restrict all the xp_cmdshell non-sa users to a single set of permissions. Does this mean that a login from one app can use an opened share for another app? Ayup. However, that's no different then what I have available in SSIS (minus the proxy discussion).

The proxy differences is important, but that's mostly because of simultaneous access. If one proxy is able to see random app A and fileshare B, and another proxy can only see Fileshare C, you can't use App A against fileshare C with proper (and detailed and highly annoying) proxy definitions. However, that's splitting hairs which is something I was trying to avoid in general. Just throwing that out there for completeness.

So, the answer to your question is... yes, you're correct. Does it answer the hidden question? No, not yet. Where DID I leave Sommerskog's blog links in this silly favorite's folder of mine....

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Craig Farrell (6/22/2011)
Where DID I leave Sommerskog's blog links in this silly favorite's folder of mine....

Oh Lord, Jeff. I haven't fully wrapped my head about it but you're going to skip up to CLR and use the windows account, aren't you?

Someone lend me that bazooka.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Craig Farrell (6/22/2011)
Oh Lord, Jeff. I haven't fully wrapped my head about it but you're going to skip up to CLR and use the windows account, aren't you?

Nah... no CLR's required for this.

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

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

Craig Farrell (6/22/2011)
rmechaber (6/22/2011)
Craig, I looked at BOL for this following your suggestions, and I have a question:
Looks like you must create a Windows account, grant it NTFS permissions to access the relevant shares, etc., then create SQL login based on the Windows user, etc. BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?
So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?
In particular, what this does is make it exactly as securable as I was touting the SSIS could be made, due to the SQLAgent. Now, yes, you can run different proxies, but in the end if you can assign any proxy you have the equivalent of one login with all the proxies' allowances, just maybe not at once.
So, what this in particular does is allow you to restrict all the xp_cmdshell non-sa users to a single set of permissions. Does this mean that a login from one app can use an opened share for another app? Ayup. However, that's no different then what I have available in SSIS (minus the proxy discussion).
The proxy differences is important, but that's mostly because of simultaneous access. If one proxy is able to see random app A and fileshare B, and another proxy can only see Fileshare C, you can't use App A against fileshare C with proper (and detailed and highly annoying) proxy definitions. However, that's splitting hairs which is something I was trying to avoid in general. Just throwing that out there for completeness.
So, the answer to your question is... yes, you're correct. Does it answer the hidden question? No, not yet. Where DID I leave Sommerskog's blog links in this silly favorite's folder of mine....

Yes, you can run xp_CmdShell procs through the Agent (and that's NOT one of the two methods I'm planning on demoing in the article). But there's a delay that may not be appropriate for the task. Feedback the user and/or the GUI is also a we bit different that way, as well.

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

Jeff Moden (6/23/2011)
Yes, you can run xp_CmdShell procs through the Agent (and that's NOT one of the two methods I'm planning on demoing in the article). But there's a delay that may not be appropriate for the task. Feedback the user and/or the GUI is also a we bit different that way, as well.

Hm, I must not have written well there. I meant to say that having a dedicated agent in SSIS is the equivalent to a proxy login for just the xp_cmdshell. You have just as much control of accessibility.

I wasn't thinking you were setting up a queue for a sqlagent job to run xp_cmdshell. Though, that's an interesting thought. I cringe at it, but it's an interesting thought.

So, no EXECUTE AS swaps, not involving the proxy login, and we're not graduating to SQLCLR, and we're not building queues... *scratches his head*.

I'm looking at this the wrong way. I'm seeing the cake from the top instead of the sides. I need to come up with a completely different approach. I'm approaching it from a security angle. The last alternative I can think of are signed procedures, again, another security component, but with more direct DBA control as to allowances. For those not familiar: Nothing that launches can be used until signed by a DBA with rights at that level via a certificate, which means, in theory, the code was reviewed by said production DBA.

Which takes me back to how much code do I really get a chance to review from my developers? That's going to depend on volume. I don't think Jeff's approaching this in that way. This is something a dev can build and a DBA can roll with reasonable assurance.

I need to go find a spark of inspiration somewhere.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Craig Farrell (6/23/2011)
The last alternative I can think of are signed procedures, ...

And now you're thinking. 🙂 That's the first method (although it's a two step process for xp_CmdShell instead of just one). The second method is a whole lot easier but tends to impinge on the sensibilities of DBA's even more than signed procedures because of 1) visceral-fear (I love that word) caused by bad information and 2) sheer laziness.

--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: 182279 More actions · Answer 13

OK, that was not what I was thinking of. I now have something to go read up on, thanks 🙂

I won't let the cat completely out of the bag, but since it looks like we're running this down pre-article, let's just say that the technique I was thinking of rhymes with Nervous Stroker.

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 14

opc.three (6/23/2011)
OK, that was not what I was thinking of. I now have something to go read up on, thanks 🙂
I won't let the cat completely out of the bag, but since it looks like we're running this down pre-article, let's just say that the technique I was thinking of rhymes with Nervous Stroker.

That's nearly as bad as the SQLAgent queue.

Really, though? Ugh. Signed procs? Yeah, fine, there are one off scenarios where they're useful, but... ugh.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 15

Craig Farrell (6/23/2011)
opc.three (6/23/2011)
OK, that was not what I was thinking of. I now have something to go read up on, thanks 🙂
I won't let the cat completely out of the bag, but since it looks like we're running this down pre-article, let's just say that the technique I was thinking of rhymes with Nervous Stroker.
That's nearly as bad as the SQLAgent queue.
Really, though? Ugh. Signed procs? Yeah, fine, there are one off scenarios where they're useful, but... ugh.

They're very useful for things other than xp_CmdShell. A simple example is where you want someone to use a proc that truncates a real table without giving that user ALTER privs on the table itself or maybe something more elemental... the privs to run a proc that updates a table without have privs to even see the table on their own. When you're properly set up, it's childs play to apply certs as the code is being promoted to QA or Production.

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