SQL Server Proxy Accounts Feature : Use of proxy accounts in SSIS

A classic problem in SSIS and DTS is that a package may work in the design environment but not work once scheduled. Typically, this is because you have connections that use Windows Authentication. At design time, the package uses your credentials, and when you schedule the package, it uses the SQL Server Agent service account by default. This account may not have access to a file share or database server that is necessary to successfully run the package. Proxy accounts in SQL Server 2008 allow you to circumvent this problem.

With a proxy account, you can assign a job to use an account other than the SQL Server Agent account. Creating a proxy account is a two - step process. First, you must create a credential that will allow a user to use an Active Directory account that is not their own, Then, you specify how that account may be used.

How to do this ?

To first create a credential, open Management Studio and right - click Credentials under the Security tree and select New Credential. For this example, you ’ ll create a credential called Admin Access. The credential will allow users to temporarily gain administrator access. For the Identity property, type the name of an administrator account or an account with higher rights. Lastly, type the password for the Windows account, and click OK.

The next step is to specify how the credential can be used. Under the SQL Server Agent tree, right - click Proxies and select New Proxy, which opens the New Proxy Account dialog box. Type Admin Access Proxy for the Proxy Name property, and Admin Access as the Credential Name. Check SQL Server Integration Services Package for the subsystem type allowed to use this proxy.

Optionally, you can go to the Principals page in the New Proxy Account dialog box to state which roles or accounts can use your proxy from SSIS. You can explicitly grant server roles, specific logins, or members of given msdb roles rights to your proxy. Click Add to grant rights to the proxy one at a time.

You can now click OK to save the proxy. Now if you create a new SSIS job step, you ’ ll be able to use the new proxy by selecting the Admin Access Proxy from the Run As drop - down box. Any connections that use Windows Authentication will then use the proxy account instead of the standard account.