xp_cmdshell issues

Question

xp_cmdshell issues

schleep

SSChampion

Points: 12585
More actions
December 1, 2014 at 8:53 am

#301574

Hi all,
We have 2 domains (A and B) connecting to our sql server in B. A is trusted by B. B is not trusted by A.
My user -- a member of AD groups in B -- is able to execute xp_cmdshell on that server. She is not a member of sa, but we have configured the proxy account.
When she logs into domain A -- as a member of AD groups in A that have windows authentication logins on that server --, she gets permisson denied on xp_cmdshell. Is there some way to allow execute on xp_cmdshell in this situation? Preferably that does not require special-casing / hard-coding for her account?
To be clear, I want to connect to the SQL Server using the $Con object
$Con = Connect-QADService -service "<DomainA>" -ConnectionAccount "<DomainA\User> -ConnectionPassword $pw
Thanks!
Paul

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

I don't have time to elaborate right now and will try to do so tonight but giving an individual proxy privs to execute xp_CmdShell directly is one of the biggest security violations that I know of because it greatly elevates the privs even for neophyte users. ONLY trusted DBAs should have privs to run xp_CmdShell and that should be done by giving the DBA(s) SysAdmin privs. Giving a user individual proxy privs to xp_CmdShell is almost the same as giving them Domain Admin privs. Individual users should only be given privs to execute very well written, very restrictive stored procedures.

This is also a large part of the reason why xp_CmdShell has undeservedly gotten such a bad reputation.

The only saving grace here is that it would appear that the Kerberos Double-Hop "problem" (I call it a security FEATURE rather than a problem) appears to have successfully stopped you and your user from extending the security mistake.

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

schleep SSChampion Points: 12585 More actions · Answer 2

Hi Jeff,

To be clear, it's not her account that has privs on xp_cmdshell. And she is a trusted user.

Moreover, the sproc she's executing that's calling xp_cmdshell take no parameters. It writes a bunch of .csvs to a network path.

If there's a way to do that from within a sproc that's called from an application without xp_cmdshell, I'm all ears.

But she can only run that sproc successfully from DomainB.

So, how to go about enabling from DomainA?

Thanks,

Paul

djj SSCoach Points: 18831 More actions · Answer 3

I am no security expert but what about EXECUTE AS?

http://www.sqlservercentral.com/articles/Execute+AS/71673/

Emil B SSCertifiable Points: 5568 More actions · Answer 4

djj (12/3/2014)
I am no security expert but what about EXECUTE AS?
http://www.sqlservercentral.com/articles/Execute+AS/71673/

Thats one option.

The other one is to use a signed stored procedure. This is definetly more secure and should roll out xp_cmdshell out of the scope. have a look and decide if it may apply to your setup.Giving Permissions through Stored Procedures - article is quite long but covers few different ways on the subject of giving privileges through stored procs.

djj SSCoach Points: 18831 More actions · Answer 5

Emil Bialobrzeski (12/3/2014)
djj (12/3/2014)
I am no security expert but what about EXECUTE AS?
http://www.sqlservercentral.com/articles/Execute+AS/71673/
Thats one option.
The other one is to use a signed stored procedure. This is definetly more secure option and should roll out xp_cmdshell out of the scope. have a look and decide if it may apply to your setup.Giving Permissions through Stored Procedures - article is quite long but covers few different options on the subject of giving privileges through stored procs.

Thus my "I'm no expert" statement 😀

Your article is very helpful.

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

schleep (12/3/2014)
Hi Jeff,
To be clear, it's not her account that has privs on xp_cmdshell. And she is a trusted user.
Moreover, the sproc she's executing that's calling xp_cmdshell take no parameters. It writes a bunch of .csvs to a network path.
If there's a way to do that from within a sproc that's called from an application without xp_cmdshell, I'm all ears.
But she can only run that sproc successfully from DomainB.
So, how to go about enabling from DomainA?
Thanks,
Paul

Excellent! Sorry about the major alarm my part. Most people make the mistake of giving individuals privs to run xp_CmdShell directly and I had no indication that you did otherwise. I was trying to make sure your systems weren't at risk. Sounds like you're doing the right thing.

What you're suffering is the "Kerberos Double-Hop problem". Again, I don't try to solve that "problem" directly because I consider it to be a major security "feature".

Since you're doing all of this correctly by using a stored procedure, the first thing that I'd ask is, what login does the database have as the database owner? My normal recommendation is to have the SA account disabled and make the owner of the database "SA" (sounds counter intuitive but it works). Then you can add the "EXECUTE AS OWNER" directive (please read about it in "Books Online") to the stored procedure and things should work as you expect. If they don't, then please post back.

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

schleep SSChampion Points: 12585 More actions · Answer 7

My bad, I guess I didn't go into enough detail in the OP.

I am aware of the dangers of xp_cmdshell, and I use it a) sparingly and b) carefully - I think. 99% of time it's to BCP out the contents of a ##table.

Never to execute code that may be the result of user-typed input.

...the first thing that I'd ask is, what login does the database have as the database owner?

A DomainB AD account that is member of a DomainB AD group that has sa rights on our sql servers.

The same account I've been using for the xp_cmdshell proxy since at least SQL 2K if not 6.5.

My normal recommendation is to have the SA account disabled and make the owner of the database "SA" (sounds counter intuitive but it works).

So, set the sa account to Disabled, but change db owner to sa (or is "SA" referring to something else?)? Counter-intuitive is right!:-D

That'll take some time to test, since I can't change DB ownership while users are connected.

Any thoughts on the certificate approach? That seems like something that can be "grafted" on to current prod with minimal, if any, disruption -- and no after-hours work.

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

schleep (12/3/2014)
So, set the sa account to Disabled, but change db owner to sa (or is "SA" referring to something else?)? Counter-intuitive is right!

Yes. The owner of the database would be "SA". Disabling the account denies attackers one-more known target of opportunity.

That'll take some time to test, since I can't change DB ownership while users are connected.
Any thoughts on the certificate approach? That seems like something that can be "grafted" on to current prod with minimal, if any, disruption -- and no after-hours work.

Erland Sommerskog has a very good write up on his site on how to do all of this with certificates. Apologies for not having the exact link handy. It was huge overkill for what I needed/wanted to do so I've not even tried it.

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

schleep SSChampion Points: 12585 More actions · Answer 9

djj provided the link above. I'm looking into it now...

Emil B SSCertifiable Points: 5568 More actions · Answer 10

schleep (12/3/2014)
djj provided the link above. I'm looking into it now...

djj provided a link to 'execute as' I would still strongly suggest that you familiarise yourself with the link I provided to Erlands article and use a certificate to sign stored procedures.

If you give someone ability to impersonate sa he would be able to execute any piece of scrip as sa

Exec 'drop table x' as login = 'sa'

schleep SSChampion Points: 12585 More actions · Answer 11

My apologies Emil, you are correct.

In fact, Erland's article also covers the Execute As scenario. But I am looking at the Certificates section.

Emil B SSCertifiable Points: 5568 More actions · Answer 12

I did use this method once in my security model to give users rights to read data through stored procedures and be able to create new users, reset password , which is a security admin privileged, etc. etc. without assigning them to any server roles. It took me a while to go through it but the end effect was very satisfying

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 13

Emil Bialobrzeski (12/3/2014)

schleep (12/3/2014)
If you give someone ability to impersonate sa he would be able to execute any piece of scrip as sa
Exec 'drop table x' as login = 'sa'

Which is why that must NEVER be done. You should only give the user privs to run a stored procedure that does the "SA" things.

Or, maybe that's the point that you were trying to get across?

--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: 1004392 More actions · Answer 14

schleep (12/3/2014)
djj provided the link above. I'm looking into it now...

That's not one of Erland Sommarskogs articles, which I highly recommend.

I'm looking for his stuff on the subject now.

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