Monday, October 8, 2012

How to run a SQL Server Agent Job using Non - System Account

SQL Server agent Job is configured to run with System account. So, Ideally we can not run the job using other account. But, SQL Server provides capability to run a job with non system account using Proxy.

Some times, whenever we pull data from different data sources then if System Account do not have permission on Parent data source then we have to use other account to pull data.
To create a proxy, first we have to create a credential.This credential should have access permission.

To create a credential
In Object Explorer, expand the Security folder.
1. Right-click the Credentials folder and select New Credential….
2. In the New Credential dialog box, in the Credential Name box, type a name for the credential.
3. In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account, but the identity can be an account of another type.
4. In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
Click OK.

To create a proxy account
1. In Object Explorer, expand a server. Expand SQL Server Agent.
2. Right-click Proxies and select New Proxy.
3. On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy.

Once the Proxy is created go to that data pull step of SQL Server job. Select the option run as and then select the Proxy that you have created. 

This will successfully run the job.

No comments:

Post a Comment