February 6, 2008 at 11:11 am
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
February 6, 2008 at 11:21 am
February 6, 2008 at 11:44 am
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
February 6, 2008 at 12:25 pm
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
February 6, 2008 at 12:58 pm
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 @sqlscribeFebruary 6, 2008 at 1:07 pm
Hello Tommy Bollhofer,
We forgot that we need to run this feature for SQL 2000.
Can u help us?
Thanks and regards
JMSM
February 6, 2008 at 1:21 pm
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 @sqlscribeViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply