SQL Server Performance Forum – Threads Archive

Proxy account permission issues.

Hi, Some of our user are required to run CmdExec jobs like scheduled DTS or executing xp_cmdshell. Since they are non-sysadmin user the Proxy Account in SQL Server Agent is set and it is the starting account of SQL Server and SQL Server Agent services, which is also a system administrator on the server. But this does not work. The CmdExec jobs fail if the owner of those jobs is non-sysadmin with error specifying that a required privilege is not held by the user. When the users try to run xp_cmdshell in QA, they get an error message:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636 All sys-admin users succeed in any of the above. I tried a solution that I found in the net that suggests to set the proxy account in the security policy "Replace a process level token" in the server, but this is no help. The proxy account is set, so what is going on? Any help? Thanks.
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

BOL explains a bit of this … When a SQL Server user executes a command prompt command using xp_cmdshell, the command must execute in the security context of a Windows account. If the SQL Server user is a member of the sysadmin fixed server role, SQL Server executes the command prompt command using the Windows account under which the SQL Server service is running. If the SQL Server user executing xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server executes the command using the Windows account specified as the SQL Server Agent proxy account. If no SQL Server Agent proxy account has been set, the user gets an error. From the error it confirms a required privilege is not held by the client. Tyr using xp_sqlagent_proxy_account in assigning the correct rights to the user.
BTW, interesting discussion about XP_CMDSHELL athttp://sqlteam.com/forums/topic.asp?TOPIC_ID=45799 this forum. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya. The problem is that the proxy account IS assigned, although not by using xp_sqlagent_proxy_account but by using the GUI in SQL Server Agent Properties in EM. And the proxy account is the SQL Server/SQL Agent account which is a System Administrator in the machine. Weird. Any way, I will try to specify the proxy acount by using the xp instead of the GUI. some time the GUI in EM (from the client) does not apply modifications. (I saw that when I tried to change some database settings in Server Property (Default Database Folder). Only when I did this in the server itself it has been applied.)
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Satya, I cheked it as follows: 1. deleteing the assigned account by xp_sqlagent_proxy_account ‘DEL’
2. Assigning the account by xp_sqlagent_proxy_account ‘SET’, , , (System Admin SQL/Agent account)
3. Restarting the Agent.
4. Unchecked the checkbox in Agent’s Property/Job system in EM for "Only user with SysAdmin…."
Does not work !! non-sysadmin users still are not able to execute a job that runs scheduled DTS by dtsrun. The job fails and the error message is : The process could not be created for step 1 of job 0xC03E26D89B3C2B4F80EAE29FB49BE3CD (reason: A required privilege is not held by the client). The step failed. sysadmin users run the job successfuly. Funny. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Hello, Aviel:
I am wondering if you have this issue resolved.
I have a job using a proxy account and all running fine until some network configuration changed and the server rebooted.
So now the job runs fine with sysadmin account but fail running as proxy with sysadmin credential. Hopefully, you can provide some insights.
Thanks.

]]>