How To: Run Sql Server Agent and Sql Server Jobs with least privilege in Sql Server 2005

Article
08/30/2007

How to: Run Sql Server Agent service under an account which is not a member of the local administrators group

1. Add the account under which you want to run the Sql Server agent service in the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group. This group is pre-configured with all the required permissions to run the service. Make sure the windows account is not a member of the local administrators group. SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group can be found under Computer Management >Local Users and Groups > Groups

2. Change the log on account of the Sql Server agent service using Sql Configuration Manager. Use the account that you just added to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group.

3. The account that runs Sql Server Agent service must be a member of the sysadmin fixed server role. This is a product requirement. This will be done automatically, since the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group is a member of sysadmin by default.

4. Now the Sql Server agent service is running under an account which is not an administrator on the machine.

In what scenario does the account that runs the Sql Server agent service need to be a member of the local administrators group?

There is no such scenario where an administrative account is needed to run the Sql Server agent service. The only limitation is in case of Multiserver administration. In a Multiserver setup, a master server distributes jobs to target servers. In such a scenario, enlisting target servers to master servers may fail with the error message “The enlistment operation failed”. To resolve this issue, simply restart the Sql server service and the Sql server agent service.

How to: Run a Sql Server job such that the owner of the job and the “Run as” account of each job step is not a member of the “sysadmin” fixed server role.

Job steps of type “Transact Sql”

1. This walkthrough shall configure “fareast\varunsh” as the owner of the job. Currently “fareast\varunsh” is added only to the Sql Server logins. It is NOT a member of the sysadmin fixed server role.

2. Job steps of type “Transact Sql” execute under the security context of the job owner, if the owner is not a sysadmin. If the owner is a sysadmin, the job executes under the security context of the Sql server agent service account, which is always a member of the sysadmin role. Hence the job owner must not be a sysadmin. Create a job, and set the owner as “fareast\varunsh”.

3. Create a new job step that updates a table.

4. Execute the job. See the result. It says job failed. Look at the job history.

5. This suggests that the account lacks permissions somewhere. We need to give update and select permissions on the correct table. Add the “fareast\varunsh” login as a user in the database.

6. Give it only update and select permission on the CMRC_Orders table.

7. Again execute the job. Now it runs successfully. Check that the row has been updated.

This shows how to run a job of type Transact Sql without the owner of the job being a sysadmin.

Job steps of type “Operating System (CmdExec)”

2. By default, a job of type “Operating System” runs under the security context of Sql Server Agent service account. Due to the product requirement, that account is always a member of the sysadmin fixed server role. Our goal is to ensure that the job owner is a not a sysadmin. Non-sysadmins do not have permissions to execute “Operating system” job steps without using a proxy account. In other words, to run a job step of type “Operating system” with the owner being a non-sysadmin, we have to configure a proxy to run the step. The step would then execute under the security context of the proxy account. We now need to add a proxy.

3. To add a proxy, first add the (non-sysadmin) account under which you will run the job step of type “operating system” in the Credentials Node.

4. Create a proxy for “Operating system” commands and select the newly created credential for the proxy.

This proxy can run only “Operating System” commands and not other commands like “ActiveX Scripts” etc. You can configure different proxies for different type of commands (also called subsystems).

5. Create a new job and set the owner to a non sysadmin user.

6. Create a new job step. The operating system command used for this walkthrough will be to delete a folder. Set the Run as account to the newly created proxy.

7. Click on OK. It gives the following error.

The owner of the job must have access to the proxy account. When the owner of the job is not a sysadmin, it would need access to the proxy to run commands using it. This prevents any account from calling operating system commands through a proxy, when otherwise it wouldn’t have permissions to call operating system commands.

8. Go to the proxy and give the job owner access to it, as shown through the Principals tag.

9. After giving access, start the job. Again it gives an error. Look at the job history.

10. The proxy account needs delete permissions on the folder. Remember the step is executing under the context of the proxy account. Configure a proper ACL on the folder.

11. Run the job again. The job runs successfully and the folder is deleted.

This shows how to run an Operating System job step with the least privileges to get the job done.

In what scenario does the owner of a job, require being a member of sysadmin fixed server role?

The transact Sql, operating system and analysis service tasks can log the output of the job to either a operating system file or to the sysjobstepslogs table in the msdb database. Only if the user executing a job step is a member of sysadmin role, can the output be logged in an operating system file. Otherwise, if the user is not a member of sysadmin role, the output must be logged in the table. Application teams should be advised to log the output to the sysjobstepslogs table, rather than to an operating system file.

After configuring the output to be logged to a file, if you run the job under a non sysadmin owner, it gives this error.