SSIS: Execute SQL Server Agent job under custom user account: stored credentials and proxies

Processing source files can be tricky at some times. At development phase you could have some local copies of the source files lying on the development server but when it comes the time of production use the source files could be anywhere. If you’re lucky, some 3rd party tool copies the files directly to your SSIS server but some times you need to access the file(s) from a shared folder somewhere in the network by yourself. When this is the case the next thing you need to worry about is authentication. See, some organizations have a very strict authentication policy and you may be given a specific domain user account to use when accessing the files over the network. How to handle this kind of situations when you have production SSIS instance running and you need multiple different user accounts to fetch files over the network?

The problem: SQL Server Agent Service account has insufficient privileges

The problem is that by default the SSIS packages ran scheduled by the SQL Server agent are executed under the SQL Server Agent Service Account. It may be a local user account (it shouldn’t, but well, you know…) or it just lacks the required permissions that you must have to fetch the source files from remote shared folder. The classic (~ bad) solution is to give your SQL Server Agent Service Account the (full control) permissions and privileges over the entire network to fool around there and there. But this is just bad policy and can lead to severe problems when things go wrong (user account accidentally deletes important files etc.). Usually you’re just given a specific user account to use to fetch the source files. Now what? How to configure SSIS package execution to use your specific user account rather than the default Agent Service account? Here’s how.

The solution: SQL Server stored credentials and proxies

First you need to create new credentials entry under Security > Credentials > New credential. Just type in the specific (domain) username and password and save.

Then we’ll create a new SSIS Execution Proxy under SQL Server Agent > Proxies > SSIS Package Execution > New proxy. There you will need to give the proxy a describing name and then map it to the credentials we’ve just created before. Then you need to map it to subsystem(s) by checking “SQL Server Integration Services Package“.

Finally we’re ready to set up the SQL Server Agent Job that will exceute the package that processes the source file(s). When you’re editing the job step properties just choose the newly created proxy from “Run as”-combobox and you’re ready to go! Now the SSIS package is exceuted under the credentials of your specific domain account and the permissions shouldn’t be an issue anymore.

Happy proxying!

-Green Danger