November 11, 2011 at 3:01 pm
This is confounding me. I'm running SQL 2005 ssis package to read from a database and then use the selects to create flat files on a network share.
The sql agent account that runs the SSIS package was granted read/write access to the network folder. When the account was given direct permission, the package runs perfectly; when the account was included in a domain group and the group is given permission instead, I get an access denied error.
From my job server, I can map a drive using the sql account credentials and read/write whether in a group or not.
This behaviour happens against any server we try (production; non-production). The big problem is that due to auditing constraints I have to use a group.
Anyone see this behaviour before?
-------------------------------------------------------------------
"Generally you don't see that kind of behavior in a major appliance." - Dr. P. Venkman
November 14, 2011 at 12:40 am
I haven't seen this behaviour before, but the only thing I can think of right now is the following:
AD uses some sort of synchronization between the servers (I'm not really an Active Directory expert). Maybe the synchronization wasn't done yet when you added the account to the group and therefore you got an Access Denied error?
(just guessing here)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 11:19 am
Thanks for the reply Koen.
The issue seems confined to SSIS 2005 itself though. Using the same account I can map to the shared folder and read/write to my heart's content. And this behaviour happens on every server I try. Put account in a group...SSIS fails but account can write through mapped drive; take account out of group...SSIS works and account can write through mapped drive.
I'll probably have to architect a different solution - just wanted to know if this oddity had been seen before.
-------------------------------------------------------------------
"Generally you don't see that kind of behavior in a major appliance." - Dr. P. Venkman
November 14, 2011 at 11:39 am
I can't see any reason why this would help in this case, but I have generally had more success using UNC paths rather than mapped drives for jobs like this.
November 14, 2011 at 11:58 am
The package does use UNC - the mapped drive was just a test that the permissions were setup properly.
-------------------------------------------------------------------
"Generally you don't see that kind of behavior in a major appliance." - Dr. P. Venkman
November 14, 2011 at 8:09 pm
More info: same package works when run from an instance that has recently been updated to 2005 SP4 (all other instances of 2005 is SP3) and from an instance where I installed 2008 R2. So either I have a "corrupt" package or some fix was implemented in SP4/2008.
Looks like I have to either re-create or hurry up the SP4 patching....
-------------------------------------------------------------------
"Generally you don't see that kind of behavior in a major appliance." - Dr. P. Venkman
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply