SQL Server Performance

Proxy fails to Run the job

Discussion in 'SQL Server 2005 Integration Services' started by itsdeshpande, Apr 30, 2008.

  1. itsdeshpande New Member

    Hi All,
    I need to create a Proxy to run a job. So, I created a Credential, and created a proxy with that credential. The account that I am using has access as under:

    1. On the database that I want to fetch data from.
    2. Access in msdb - dtsoperator, dtsadmin, dtsltduser, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole
    3. The services are running on this account as well.
    4. Log on as batch job, Log on as Service.
    But, when I run the job, it fails with the below error.
    Message
    Executed as user: *** . The process could not be created for step 1 of job 0xEC1F800AD7CF2546A2DD58B2365D7D48 (reason: A required privilege is not held by the client). The step failed.
    Also, If the job is run with the SQL Agent Account, it runs fine. Note that the Agent is running under the same account as that used in the Proxy..
    Please help.
    Manoj Deshpande.
  2. itsdeshpande New Member

    HI,
    SOLUTION!!!
    I got it, I got it. Here is what I did to resolve my problem.
    First, this account that is being used as a Proxy Credential, need not be a local admin on the box. That's one of the reasons that we got for a Proxy. Take it off from there.
    Then, do this:
    1. Create a new credential with an account which should not be a member of the local administrators on the server.
    2. Create a Proxy using this Credential.
    3. Give permissions to this account used for Credential as under:
    • Goto Start --> Right Click Computer --> Manage --> Local Users and Groups --> Groups
    • Add the account used for Proxy in the following groups:
    SQLServer2005MSSQLUser$<<ServerName>>$MSSQLSERVER
    SQLServer2005SQLAgentUser$<<ServerName>>$MSSQLSERVER
    • Now, change the account on the SQL Server and SQL Server Agent service to run under the account that is used for Proxy. To do that, Goto Start --> All Programs --> SQL Server 2005 --> Configuration Tools --> Open SQL Server Configuration Manager.
    Note that To run the services, the account must be a member of the sysadmin role on SQL Server. This is already achieved by adding the account in the two groups (second bullet point), as they have permissions of sysadmin.
    • Right Click SQL Server service and select Properties, where you can change the account. Do this for only SQL Server and SQL Server Agent services.
    • If prompted, restart the services.
    • Give permissions of “Modify” to the account used in Proxy/Credential, on all the folders accessed by the job. ALso “Replace permission entries on all child objects…”
    • Give Full control on the folder C:program FilesMicrosoft SQL Server90DTSPackages to the account used in Proxy/Credential.
    That should get you going. If anyone who tried this still has issues, please let me know and I'll try to help.
    Thanks,
    Manoj Deshpande.

Share This Page