Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

2 Answers

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 http://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account

share|improve this answer
    
You could also grant the computer account permissions to the other network resources, if in a domain environment: serverfault.com/questions/135867/… –  Shawn Melton 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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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