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, right-click Proxies and select New Proxy to open the New Proxy Account dialog box shown in Figure 9-17. Enter Admin Access Proxy for the Proxy name property and Admin Access as the Credential name. Select the SQL Server Integration Services Package subsystem type allowed to use this proxy.

Figure 9-17: The New Proxy Account dialog box

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. As shown in Figure 9-18, 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.

Figure 9-18: Granting server roles, specific logins, or members of given msdb roles rights to your proxy

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