Custom Security and SQL Job with SSIS Script Task

  • Article

Scenario

Recently I ran into a security issue that has a simple fix.  Here is the situation:

  • SSIS Package that contains a Script Task
  • Run SSIS package as a SQL Job
  • Use a special credential/proxy for the step of executing the SSIS package

Results

In the SQL Job History Log you may find something like this ...
Code: 0x00000002     Source: XXXXX Script Task      Description: The script threw an exception: Unable to generate a temporary class (result=1).  error CS2001: Source file 'C:\WINDOWS\TEMP\yyyzzzxxx.N.cs' could not be found  error CS2008: No inputs specified

Solution

Simple put,the VBA compiler has permissions to write the file into C:\windows\temp but your credential does not.   Now the simple fix is to allow just read/read&execute/list permissions to C:\windows\temp and that will resolve the issue above.   However, please not that there is a potential security risk in doing so as that this credential now has access to read all temp files in this directory.   So be forewarned that this may not fit your situation.

Conclusion

Although not suggesting the ideal security solution, this may help you troubleshoot and further refine your strategy in running you SSIS package with a Script task with a custom credential/proxy.