0

I have an SSIS package that accesses a folder on a network share. If I run this package as part of a SQL Agent Job, it succeeds. If I run this package directly from the SSIS Integration Catalog (Right Click and execute) on my development PC, it fails with a permission error . If I run this package directly from the SSIS Integration Catalog (Right Click and execute) on the server hosting the SQL instance, it works.

All users's involved in this process have access to the folder (my username, the user setup to run the SSIS service and the SQL Agent service). I know this for sure because I can accsess the folders logged in under any of these users.

Any ideas why this would be?

What baffles me is that it works if you run it from the server hosting the SQL instance, but not from my local PC (when logged in under the same user)!

We've gotten to the point that when we want run certain packages that access the file system, we create an SQL agent job to run it. It's not ideal.

Lock
  • 5,422
  • 14
  • 66
  • 113
  • with all such problems you need to work out what Identity the task is actually running as. – Mitch Wheat Aug 10 '16 at 00:59
  • Yep. Tried and couldn't quite work it out. This post `http://stackoverflow.com/questions/15289442/which-user-credentials-does-integration-services-catalog-use-to-execute-packages` suggests it will run under my username (as I am using Windows authentication to connect to SQL Management Studio` but I don't believe that to be the case. – Lock Aug 10 '16 at 01:01
  • I tried using process monitor to monitor the file in question for user access, but nothing seemed to access it. I believe it fails in the validation steps so it doesn't even try to access the file. – Lock Aug 10 '16 at 01:01
  • @MitchWheat If the file is actually on a network share, do I have to fire up processmonitor on the server hosting the share? We tried both and it didn't show anything trying to access the file. – Lock Aug 10 '16 at 01:03
  • ok. at this point you need to break down the problem: where is it actually failing? – Mitch Wheat Aug 10 '16 at 01:04
  • Pre-execute phase. Just found this article which suggests to add `NETWORK SERVICE` to the folder. `http://www.computercabal.com/2012/04/ssis-server-file-permission-error-code.html`. I'll get our admins here to add it and see if it works. Will report back. – Lock Aug 10 '16 at 01:13
  • Correct. Permission errors are that: permission errors. Unless SSIS itself has permission to use advanced SQL Server methods for the package you are using, you can run into access rights issues (obviously this OS broad, but I've run I to similar problems that can be as simple as one folder being read only to your computer). Use a process of elimination to find where the permission error(s) is occurring. – clifton_h Aug 10 '16 at 01:38

2 Answers2

1

Adding the NETWORK SERVICE user account to the folder fixed the issue. I'm not entirely sure why as it runs under the context of the user executing the package, but it fixed the issue.

Lock
  • 5,422
  • 14
  • 66
  • 113
0

SQL Server Management Studio does not provide execute permission on Integration Service catalog using 'Sql server Authentication'. So, you can not execute any package from your machine.

you are able to run your package on the server machine because SQL server instance is installed on that particular machine and you are connecting SQL server by 'Windows Authentication'.

I hope this could help you!

Paras Sharma
  • 55
  • 2