An recurring issue with Integration Services packages that read or write to file systems is permissions issues. It's quite difficult to have a package run properly when it can't see the source files, or can't write results to a particular network share. But it's insanely hard to get a package working if you don't think permissions are set up properly but your sysadmins do. I'll rehash what many of you already know (the typical problem and typical solution) in order to provide some background, then I'll call out a tool I've used when that just doesn't work... and you need to persuade your sysadmins to look a little harder.
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.
I always choose (3). No, I don't ask our sysadmins to create a new account for every Agent job. I create "service accounts" that I can use to manage distinct "systems." I've got one that handles ETL work for my data warehouse. I've got another that handles some import work for a third-party system. Each of them is used in a few places, but the key is that their permissions are very low (no interactive login, etc) and they only get rights as necessary to complete their tasks.
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.
