SQL Agent SSIS Package - Security Group credentials?

• ncbaras

  Valued Member

  Points: 63

  More actions

  May 28, 2015 at 4:02 pm

  #308525

  I have a semi-acceptable workaround to this issue, but it seems odd that SQL Agent would work this way and I was hoping to get some insight from the community.

  Here's the scenario:

  1. SQL 2012 standard edition

  2. Developed simple SSIS package to loop through .csv files on a network share folder, import them into a table, and rename them

  3. Runs successfully from my local machine via Visual Studio

  4. Import dtsx file to SQL Server SSIS File System. Runs successfully from there.

  5. Create a SQL Job to execute the package. The T-SQL portion of the job succeeds, but the rename fails with "Access to the path is denied" verbiage.

  6. The service account running SQL Agent belongs to an AD security group with full control the the network share folder.

  When logging into the SQL Server as the service account, I was able to browse/move/rename the file(s) in question (i.e. as far as windows is concerned the service account has the proper permissions). After much trial-and-error with my network admin, we ended up explicitly adding the service account to the network share (rather than implicitly as part of the AD security group) and it started working.

  Is there a rule somewhere that says SQL agent jobs must have explicit permissions to a network share? Is there some setting in the package or in windows itself that we're missing?

  Thanks in advance!

  -Nick