When a SQL Agent job executes a SSIS package then by default it uses the credentials of SQL Server Agent Service Account. The service account is selected during the initial installation and configuration of SQL Server. By default the usual suspects are the local system account (NT AUTHORITY\System) or the local network service account (NT AUTHORITY\NetworkService).

Because these are local accounts they not have rights to access resources on other servers. If  package running with service account credentials attempts to access a database on another server using Windows Authentication we will encounter an error when the remote server rejects service account. The network service accesses network resources as a null session with no credentials. This can generate some odd error messages.

One approach to solve this problem is to configure the SQL Agent Service to run as a domain account. Configured correctly this is in fact more secure than using a service account and the domain account can be granted access to network resources. However, this account is global to the entire SQL Server installation.

If we do not want to use the SQL Agent Service Account as it may not have sufficient privileges and we would want our SSIS packages to use a specific Windows account which will have all the necessary permission.

This is little complicated than just picking a windows account as it involves several conceptual layers within the SQL Agent architecture.

In SQL Agent terms a Job may run as a proxy. A proxy account is associated with a credential, which in turn represents a specific user account.

Lets see all the associated steps needed to execute the SSIS package with Windows Account:

1.Create a Credential:

The first step is to create a credential storing the account and password you want to run the package. In Management Studio, click Security->Credentials->New Credential:
I usually prefer the name of the account (i.e. MyDomain\MyAccount) for clarity. The identity is the account (MyDomain\MyAccount). Enter and confirm the password.
We cannot directly reference the credential from the job configuration “run as” list. First we must setup a proxy.
        2. Create Proxy Account:
Now we setup a proxy account that references our credential. In Management Studio, click SQL Server Agent->Proxies->SSIS Package Execution->New Proxy:
The proxy name can be whatever we want. I use the windows account name for clarity. The proxy will reference the credential we created in step one. We must associate the credential with the SSIS subsystem or it will not show up in the job step configuration dialog.
        3. Setup Job:
On the general page of the Job configuration there is an owner – that is not the account the package will run as, the Owner only affects who will be able view and to run the job. The proxy account is selected from the job step configuration:
If you do not see your proxy account listed, you probably forgot to check the SSIS subsystem in step two.
Cheers,
Subhro Saha
Advertisements