Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error when executing xp_cmdshell.... Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2008 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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

Post #452372
Posted Wednesday, February 6, 2008 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:16 AM
Points: 1,157, Visits: 3,263
Refer to -

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



Tommy

Post #452375
Posted Wednesday, February 6, 2008 11:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:39 AM
Points: 3,461, Visits: 350
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
Post #452379
Posted Wednesday, February 6, 2008 12:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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
JMSM


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



Post #452397
Posted Wednesday, February 6, 2008 12:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:16 AM
Points: 1,157, Visits: 3,263
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

Post #452408
Posted Wednesday, February 6, 2008 1:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
Hello Tommy Bollhofer,

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

Thanks and regards
JMSM
Post #452412
Posted Wednesday, February 6, 2008 1:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:16 AM
Points: 1,157, Visits: 3,263
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

Post #452419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse