Error when executing xp_cmdshell....

Question

Post reply

Error when executing xp_cmdshell....

JMSM

SSCertifiable

Points: 5440
More actions
February 6, 2008 at 11:11 am

#119889

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Refer to -

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

Tommy

Follow @sqlscribe

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

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 SSCertifiable Points: 5440 More actions · Answer 3

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:blush:

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 SSChampion Points: 14940 More actions · Answer 4

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 SSCertifiable Points: 5440 More actions · Answer 5

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 SSChampion Points: 14940 More actions · Answer 6

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