January 30, 2008 at 5:03 am
[font="Arial"]Hello,
Can you tell me how can i give permissions for the execution of the xp_cmdshell to one user without give the sysadmin permission. This problem appears because theres an apicatioon(app user) that needs to execute some operating system task but i cant give this privilege to users.
I give the grant execution on the xp_cmdshell but it gives me the following error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.
(0 row(s) affected)
Thanks and regards.
jmsm;)[/font]
January 30, 2008 at 5:28 am
JMSM (1/30/2008)
[font="Arial"]Hello,Can you tell me how can i give permissions for the execution of the xp_cmdshell to one user without give the sysadmin permission. This problem appears because theres an apicatioon(app user) that needs to execute some operating system task but i cant give this privilege to users.
I give the grant execution on the xp_cmdshell but it gives me the following error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.
(0 row(s) affected)
Thanks and regards.
jmsm;)[/font]
If the user is not in the sysadmin role SQL Server will need to you need use a proxy account. You can set this up using sp_xp_cmdshell_proxy_account (see http://msdn2.microsoft.com/en-us/library/ms190359.aspx)
Regards,
Andras
March 28, 2008 at 11:47 am
I've tried this and users still get the following error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.
Any idea what else it could be?
¤ §unshine ¤
March 28, 2008 at 11:55 am
are you sure you specified valid account? did you follow syntax shown below to create account?:
EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyUserName', 'myDomainPassword'
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 28, 2008 at 12:02 pm
Yes, however it is a windows user id and i did not enter a password. Is that incorrect?
¤ §unshine ¤
March 28, 2008 at 12:06 pm
yes, you should enter password while creating it.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 28, 2008 at 12:10 pm
Any password? I used a group windows authenticated account. all developers can access everything via this account rather than being individually assigned security. Less maintenance.
¤ §unshine ¤
March 28, 2008 at 12:31 pm
You must put the correct password for the Windows account you've specified will be the proxy account. And yes, when a non-Sysadmin runs xp_cmdshell, xp_cmdshell will execute as this Windows account and only this Windows account. So be careful with respect to what rights it has.
K. Brian Kelley
@kbriankelley
March 28, 2008 at 12:36 pm
ok. I'll have to talk to whoever setup this account. They just all log in with their individual windows account and have access.
thanks!
¤ §unshine ¤
March 28, 2008 at 1:30 pm
I have one of the network guys looking into a password for this account, however i find it weird that they can see legacy dts packages and schedule and run scheduled jobs.
¤ §unshine ¤
March 31, 2008 at 6:34 am
You mean that the developers can? If so, you may want to investigate what rights they have in msdb. Jobs aren't normally something they should be able to execute (if they don't own them) unless someone is taking advantage of an undocumented (but well known) database role within msdb or db_owner membership has been given out.
K. Brian Kelley
@kbriankelley
March 31, 2008 at 8:06 am
Yes, they can. It is a development box and I gave it to them via their credentials. That is why I dont understand why they cant execute cmd shells.
¤ §unshine ¤
March 31, 2008 at 9:49 am
xp_cmdshell is in the master database. Also, by default it has no security permissions assigned against it, meaning only members of the sysadmin fixed server role have the ability to execute it. In SQL Server 2005 by default it is also configured where no one can execute it. However, that can be changed using the Surface Area Configuration tool.
K. Brian Kelley
@kbriankelley
March 31, 2008 at 11:23 am
I gave the developers windows group access to and also enabled it in SAC. still nothing.
¤ §unshine ¤
March 31, 2008 at 12:28 pm
If you're not a member of the sysadmin fixed server role, the SQL Server Agent proxy account must be set, as previously discussed. So if you have a non-sa level account these are the things that have to be done:
- xp_cmdshell has to be enabled (easiest way is in SAC)
- the login has to be granted access to the master database and the user it maps in as must have EXECUTE rights against xp_cmdshell
- The SQL Server Agent proxy account must be set correctly
If any of these three things are missing in SQL Server 2005, xp_cmdshell won't work for a non-sysadmin level.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply