I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file or overwrites an existing file in another location. The package works as expected from visual studio and when executed manually (double click in explorer). When run from a maintenance plan or scheduled through the SQL server agent job menu the file is always blank. The process will overwrite existing files and create files when no file exists. I believe this indicates it is not a permissions issue.

  • Running MSSQL Server 2008r2
  • SQL Server Agent is running as NT AUTHORITY\System with privileges provided to the computer$ on the network share.

Any ideas what is going on and why?

  • What account runs SQL Agent? – billinkc Oct 30 '13 at 23:24
  • NT AUTHORITY\System – BZN_DBer Oct 30 '13 at 23:36
  • So you have added permissions on the share to the computer$ account, what permissions did you give? Also, what NTFS permissions on the folder did you give to computer$? Usually you give everyone full control on the share permissions and then lock down with NTFS folder permissions, i'm curious what you have applied. – steoleary Nov 5 '13 at 16:23
  • I have provided full control through both the NTFS permissions and the share permissions by add thing the computer to a user group. – BZN_DBer Nov 6 '13 at 18:17

It's not that it's executed from a maintenance plan. Rather, it's that the user executing the package is different. NT AUTHORITY\SYSTEM is god on the machine. However, outside the machine, the account is meaningless.

In your case, it attempts to talk to a foreign machine and that request is denied as the share is unlikely to allow Everyone read/write access to it.

I know of at least 3 ways to resolve the problem. The first and worst is to grant Everyone full control of the network share. That would probably allow the local account to do what it needed to on the remote machine but I'm embarrassed to even list that as an option.

The real two options are to change the execution account for SQL Agent to a domain account with sufficient privileges in both the SQL Server Instance and has network access to the share.

A more precise and less risky fix would be to create a create a Credential and then under the SQL Server Agent, create a Proxy that uses the Credentials for job steps of type SSIS Package Execution and then update the job to use the Credentials it is now empowered to access.

As @Shawn Melton pointed out in the comments, a fourth option would be to grant the computer account permissions to the other network resource https://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account

  • You could also grant the computer account permissions to the other network resources, if in a domain environment: serverfault.com/questions/135867/… – user507 Oct 31 '13 at 3:32
  • I've already granted the computer account permissions on the share. As I mentioned in the question the file is created however it is empty. Prior to giving the computer account permissions on the share the agent could not create the file. – BZN_DBer Oct 31 '13 at 3:37

If your file is created but it is empty. You may still have permission issues . Try to use some file monitor program to see what SQL Server (Agent) is doing in your machine and in your remote machine.

Process Monitor is a good utility for this. You can filter file operations and see what your package is doing. You should add this information to your question so that we can help you more.

Also you may try to run your package with different credentials to see if your problem is really about permission issues. A handy utility about this is ShellRunAs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.