xp_cmdshell - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

xp_cmdshell

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-28 : 05:09:31

Hi,

My stored proc is using EXEC master..xp_cmdshell @param
This works fine in Dev sql server (May be because I have more rights in Dev than in Prod) but not in Prod sql server due to the following error:

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

There is a group called "Users_Readers" which has execute rights on all the stored procs.

I would like the group "Users_Readers" to be able to execute the stored proc which has master..xp_cmdshell

I think I have to run the following...

--specify the proxy account for non-syadmins
EXEC sp_xp_cmdshell_proxy_account 'Domain\Users_Readers','pwd'
EXECUTE AS login = '??'

Am I on the right?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 07:18:17

yep...you need to create a proxy account with required priviledges for xp_cmdshell to use. And in prod server you should take help of DBA for creating it as you wont have permissions for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-28 : 09:36:08

By a proxy account, do you mean like a windows account login?

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 11:17:25

Firts of all it is a BAD idea to have xp_cmdshell enabled on the server and it is much WORSE to have it getting executed from a SP.

Now to the problem.You have to create a proxy account which I think you already created

Then create a new user say "MyUser" in master database and grant him rights on xp_cmdshell in master database


Grant Execute on xp_cmdshell to MyUser

So the whole code would be something like this


EXEC sp_xp_cmdshell_proxy_account 'Domain\Users_Readers','pwd'
EXECUTE AS login = 'MyUser'
Exec xp_cmdshell 'dir C:\'
GO
Revert

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 11:34:01

I'm not so sure I agree

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 11:36:41

quote:
Originally posted by X002548

I'm not so sure I agree

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

On what ?? The solution...

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 11:51:42

OK...if this IS an input parameter, then is REALLY Wrong

My stored proc is using EXEC master..xp_cmdshell @param

I usually use @cmd when I set the command.

I NEVER Use an input variable...and usually ONLY use it as a batch scheduled job

;-)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-28 : 12:30:03

Thank you all.

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 12:44:10

Just not sure why all this obsession with xp_cmdshell ???

Also why does one have to do all this "windows stuff" using TSQL..There are lot of other alternatives powershell,VBSript etc..

Just imagine creating a folder in the drive using a stored procedure..Doesnt sound weird ???

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 12:44:56

quote:
Originally posted by arkiboys

Thank you all.

So did it work for you ?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 12:50:32

quote:
Originally posted by Sachin.Nand

Just not sure why all this obsession with xp_cmdshell ???

Also why does one have to do all this "windows stuff" using TSQL..There are lot of other alternatives powershell,VBSript etc..

Just imagine creating a folder in the drive using a stored procedure..Doesnt sound weird ???

PBUH

Not so obessed with it to be used in scenarios like what you explained above

But still there are other scenarios where we require it sometimes
like executing packages programatically using DTExec utility calls, bcping out data programatically onto file etc to name a few

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 12:52:26

Security?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 13:02:48

@Visakh

Not sure what you meant by executing packages "programatically".I hope you dont intend to say you are using xp_cmdshell in a SP to make DTExec calls.

Also isnt SSIS an alternative for bcp ?

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-12-28 : 13:10:34

@Brett

Yes ofcourse security.If your system is compromised it can give a granular access at the OS level .Just imagine this code run on the server.

EXEC xp_cmdshell "del /q blah blah"

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-29 : 04:45:09

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources