Using SSIS to Access Network Resources

SSIS (SQL Server Integration Services) is the replacement for DTS in SQL 2005. One of the more deceitful concepts in SSIS is it’s security context. When you run a SSIS package form your Visual Studio Environment, it obviously runs under a different security context (most possibly as you–the developer) than it would run if it was run as a scheduled service on the SQL server itself. So let’s say you’ve developed a SSIS package that uses resources within your domain, such as accessing a network drive. How will SSIS access such a resource? As you may know, SSIS packages are executed by the SQL server agent, which by default uses the native [NT AUTHORITY\SYSTEM] account1 to execute your packages. Since this is a local account you cannot use it when you need access to resources external to the SQL server box. In order to access domain level resources, you need to use a domain proxy. A domain proxy allows the SQL agent to proxy on an existing domain account when it executes a package. The first step is to create a credential. Connect to your SQL server, expand security, right click credentials and new credential. Fill-in the dialog box to something like this: The second step is to create a domain proxy, expand SQL Server Agent, right click proxies and click new proxy. Fill in the dialog box something similar to the image shown on the left. Identity is the domain account you are going to use and the password to the domain account.
Once this is done, expand SQL Server Agent, right click Proxies and click on New Proxy. The resulting dialog box should look something like the image on the left. For the credential name use the credential you just made.
Now the last step is to modify the SSIS package to use this proxy account. So right click Jobs under SQL Server Agent, right click the job that you want to run using this proxy account, click properties, go to the steps tab, click on edit at the bottom of the screen. Change Run As to the new proxy account you just created. The resulting dialog box should look something like the image on the left. Make sure the domain account, which you are using during the step when you created the credential, has access to all the network resources this package needs access to. [1] SQL Agent actually does not use the system account but runs in the context of the SQLAgentUser group on the local machine, the [NT AUTHORITY\SYSTEM] account is by default part of the SQLAgentUser group.