SQL Agent Jobs Fail...
These errors typically surface when the job is put into production as a SQL Agent Job Step, where it's executing under an account you didn't develop it with. You'll typically get an error in the job history that looks like this:
Executed as user: DOMAIN\MACHINE$. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:42:58 PM Error: 2012-05-07 15:42:58.76 Code: 0xC001401E Source: Flat File Read Connection manager "Test" Description: The file name "\\Share\Folder\Testing.txt" specified in the connection was not valid. End Error
Your first reaction is "I can see that file - of course it's there!"
It's not hard to overlook the fact that the account the job is executing under is the SQL Agent account - not yours, and it needs the same permissions your account did in order to do this job. (Well - hopefully not exactly the same permissions - just those few that are absolutely necessary.)
... Because They Don't Have Permission ...
You can see that in the first part of the message - "Executed as user:" (I've replaced real data with fake names to protect the innocent.) The message tells me that the job is executing under a system account (my fictitious computer is named "machine"). The folder "Folder" on the network share "Share" doesn't permit that account access, so the job fails.
You have a few options to deal with this - some are better than others:
- You can grant access to \\Share\Folder to DOMAIN\MACHINE.
- You can create a Proxy to execute the package with your account credentials (since you have permissions).
- You can create a new domain account specifically for this purpose, grant it appropriate rights to the network share, create a Proxy for it, then configure the job to use that Proxy.
However you do it, managing proxies usually gets you to your goal... until it doesn't.
... Even After They Should!?
I've run into scenarios where I'd swear a certain account ought to have access to a resource, and yet it doesn't seem to. In the case of file systems there's one particular tool that has saved my addled brain a couple times. After all, computers are dumb machines (no offense intended) and only do what we've told them to do. So if they're refusing access... it must be because we've instructed them to do so.
Given that we've narrowed the problem down to our own inability to configure security, we need to confirm that diagnosis, and possibly figure out why. The tool I'm talking about is the Effective Permissions tab of the security settings dialog. To get there, find the file system location or object you're trying to access and open its properties. Switch to the security tab (which you swear shows what it should). Hit the Advanced button. This (permissions) view alone may highlight the mistake you've made - you may think certain accounts inherit things in a certain manner, but don't. You may think certain permissions apply further down the folder tree, but don't.
In case you're still not enlightened as to why things aren't working, and need proof one way or the other that permissions really are the issue, click over to the Effective Permissions tab. Type or select the account your proxy is configured for, and you'll see exactly how the operating system resolves the permissions.
If you set up the security settings, I hope this cuts down on the possible problems and highlights just a few. If you've asked someone else to set up permissions, this tool should show you whether the job was done correctly or not.