locked
IT Manager RRS feed

  • Question

  • Need help, we have setup a new user, other that system admin

    Executed as user: \SSIS_User. The process could not be created for step 1 of job 0x721F8B67E4D29841BC2938570621A483 (reason: A required privilege is not held by the client).  The step failed.

    Notes:

    The package is using package configurations for the following properties:

    Connection String

    Initial Catalog

    Password

    User Name

    Server Name

    The package uses environment variables to locate the configuration file that contains the connection information stated above.

    A user named SSIS_User is set up as a Windows Domain user with Domain Admin rights.

    In SQL Server 2005, this user is set up as a Login with sysadmin role.  This user is also set up as a credential and proxy.  The proxy user has access to Operating System and SSIS subsystems.

    The protection level in the package is set to DontSaveSensitive.

    The connection names in the package are the same in the configuration file.  This screen shot shows the package configuration.

    In SQL Server 2008, this setup works fine.

    Thursday, June 14, 2012 6:36 PM

Answers

  • You first have to set up a Proxy account for running scripts as a non-sysadmin,

    1. Open Enterprise manager.
    2. Click on the server name -> Management
    3. Right Click on SQL Server Agent
    4. Choose the Job System tab
    5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
    6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.

    then Verify/Change permissions.

    1. Open Administrative tools (You will need admin rights on the server to do this)
    2. Go to Local Security Policy ( this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
    3. Click on "user rights Assignment" in the left hand pane,
    4. In the right hand pane, scroll down until you find " replace a process level token".
    5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
    6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.
    • Marked as answer by Iric Wen Thursday, June 21, 2012 7:48 AM
    Thursday, June 14, 2012 7:00 PM