Expert SQL Server™ 2005 Integration Services

9.8. Proxy Accounts

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 2005 enable you to circumvent this problem.

With a proxy account, you can assign a job to use a different account 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 the user's own. Then, you specify how that account may be used.

To first create a credential, open Management Studio. Right-click Credentials (under the Security tree) and select New Credential to get to the screen shown in Figure 9-16. For this example, create a credential called Admin Access. The credential enables 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, confirm the password, and click OK.

Figure 9-16. The New Credential page

The next step is to specify how the credential can be used. Under the SQL Server Agent tree, ...