Run Your Jobs With a Proxy Account

As you know, when you define SQL Server services through sql server configuration manager, they are automatically authorized with the minimum privileges they may need.

Although not recommended, some dba’s give these accounts sysadmin permission on the instance and local admin permission on Windows.

Under normal circumstances, it is sufficient to give these accounts to log on as a service permission on Windows.

But if you only grant “log on as a service” right outside the default privileges and define a job that performs one of the following operations, the job will fail. Because SQL Server Agent service has been created with limited privileges.

1. ActiveX Script
2. Operating System(Cmd Exec)
3. Replication Distributor
4. Replication Merge
5. Replication Queue Reader
6. Replication Snapshot
7. Replication Transaction-Log Reader
8. Analysis Services Command
9. Analysis Services Query
10. SSIS Package Execution
11. PowerShell
12. Unassigned Proxies

The detailed description of the sql server agent subsystems listed in the above can be found at the link below.

http://technet.microsoft.com/en-us/library/ms187100(v=sql.105).aspx

That’s why we need to run our job with a proxy.

How To Create a Proxy

We need a credential to create a proxy.

To create credential we need a user with the authority “log on as a batch job”.

First, let’s give a user the right to “log on as a batch job” to create a credential.

We write secpol.msc and press enter on Search.

Click on User Rights Assignment and right click on “log on as a batch job” and click on properties.

And in the drop-down box, add the user. If you are not authorized to give this right, you must ask your friends in the related technical team in your company.

Then, let’s create credential as follows.

In the next step, we start creating the Proxy as follows.

In the window that opens, we write our proxy name as follows and we select the credential we will use. And then we mark the subsystem we will use from the “Active to the following subsystems”. In our example we will use the Operating System (Cmd Exec).

By clicking on the Principals, we can set the SQL Server login, the server roles, or the roles in the msdb database to use the proxy. There is no need to do this for sysadmin.

Since we will assign the testlogin user as the owner of the job, we are adding our testlogin user from the principals tab.

The only authority of the testlogin user is the owner of the job we will create. It has no other authority.

We have created our proxy by pressing Ok.

Now let’s create a new job and set its owner as testlogin.

You can give any name to the job’s name. I called JobCmdExec.

Let’s go to the step tab and give the step a name. I called CmdExecStep1.

Then select Operating system (CmdExec) from type as below.

In Run As, you will see the SQL Server Agent service account by default.

In the first stage, let’s not change this to try the job without proxy, and write “xcopy /s c:\test c:\test2” as follows.

To do this, create two folders named test and test2 under the c disk. Put a file to copy process under the folder “c:/test”. I created a text file with the name test.txt

When we run Job, we see an error.

Go to job history to see what the error is. The error we see in the message is as follows.

Non-SysAdmins have been denied permission to run CmdExec job steps without a proxy account.  The step failed.

Right click on the job and click on the properties. and from the run as tab of CmdExecStep1, select the proxy we previously defined and run the job.

After this process, we see that the job is running successfully and the test.txt file is copied to the test2 folder.