SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error when executing xp_cmdshell....


Error when executing xp_cmdshell....

Author
Message
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 Visits: 977
Hello the following error appears to me when i execute the xp_cmdshell with another user that is not xp_sqlagent_proxy_account but here goes the steps that i've done.

I logon to the machine whith xp_sqlagent_proxy_accou that is (sqlagent_proxy_account) and execute the xp_cmdshell wich give me the output expected.

exec master..xp_cmdshell 'dir *.exe'

Then Volume in drive C is Sistema
Volume Serial Number is F0E3-DF6D
NULL
Directory of C:\WINDOWS\system32
NULL
03/24/2005 04:55 PM 186,368 accwiz.exe
.
.
.
383 File(s) 51,331,657 bytes

After i created one user that sould execute this extended stored procedure and i give him the following privilege:

grant execute on master..xp_cmdshell to etg2;
When i loggin with this new account and execute the same command:
exec master..xp_cmdshell 'dir *.exe' but the folloing error appears to me:

Msg 50001, Level 1, State 50001
xp_cmdshell failed to execute because LogonUserW returns error 1326. please make sure the service account SQL Server running under has appropriate privilege. For more information, search Book Online for topic related to xp_sqlagent_proxy_account.

Why?
What should i do?
How can i give the privilege to other user to run xp_cmdshell?

Thanks and regards
JMSM
Tommy Bollhofer
Tommy Bollhofer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 3359
Refer to -

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

Tommy

Follow @sqlscribe
Sugesh Kumar
Sugesh Kumar
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4567 Visits: 358
Though u give permissions to execute the sp directly only users with sysadmdin rights can use xp_cmdshell exclusively. Other wil get some or the other error.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 Visits: 977
Hello again everybody,

But hope u can help me again.
As u say Tommy Bollhofer i see the link that u send me but i don't understand some of this points.
My question is as simple as this:

I've one user user etg2 that is owner of database xyz, i need that this user can execute the xp_cmdshell.
So when i see the link that u told me the following commands appears to me but i've som questions to ask u,.

1st:
In the first point the following error appears to me:
"Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79 The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option."

1st.a: why should i've to execute this command?

--1, allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

2nd:
One error appears when try to create login whit the command that is shown to me.

--2, grant permission to xp_cmdshell
USE master
go
CREATE LOGIN etg2 WITH PASSWORD = '1q2w'
go

--Note, we are in the master database!!!
CREATE USER etg2 FROM LOGIN etg2

From this point forward ive to tell u that i've done nothing but i even don't understand what commands should i execute?
I've only what that usre etg2 that is owner of database xyz can execute the xp_cmdshell.

Hope u can help me.
Thanks and regards
JMSMBlush


Can u help me on this theme that im feeling a big dunkey.


--Run as login x
EXECUTE AS login = 'etg2'
--Below fails, no execute permission on xp_cmdshell
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--Note, we are in the master database!!!
GRANT EXECUTE ON xp_cmdshell TO etg2

--Try again
EXECUTE AS login = 'etg2'
--Execution of xp_cmdshell is allowed.
--But I haven't configured the proxy account...
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--3, specify the proxy account for non-syadmins
--Replace obvious parts!
EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'
EXECUTE AS login = 'etg2'
--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT

--Cleanup
EXEC sp_xp_cmdshell_proxy_account null
DROP USER etg2
DROP LOGIN etg2
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
Tommy Bollhofer
Tommy Bollhofer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 3359
The first part enables xp_cmdshell (which is disabled by default)


EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
go



Here we are creating a login for testing purposes only


CREATE LOGIN TommyTest WITH PASSWORD = '@dyln1234&'
go
CREATE USER TommyTest FROM LOGIN TommyTest
go



Now create the proxy account


EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyDomainAccount,'@dyln1234&'



Grant permissions on xp_cmdshell to the test account


GRANT EXECUTE ON xp_cmdshell to TommyTest
go



Validate everything is working


EXECUTE AS login = 'TommyTest'
EXEC xp_cmdshell 'DIR C:\*.*'
go



Tommy

Follow @sqlscribe
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 Visits: 977
Hello Tommy Bollhofer,

We forgot that we need to run this feature for SQL 2000.
Can u help us?

Thanks and regards
JMSM
Tommy Bollhofer
Tommy Bollhofer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 3359
In that case, launch enterprise manager, right-click on the SQL Server Agent and select properties. Select the job system tab and un-check the box under "Non-SysAdmin job step proxy account". Configure the proxy account as described earlier and grant permissions on xp_cmdshell to the test login.

Tommy

Follow @sqlscribe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search