Monday, March 26, 2012

Package won''t run from job

I imported my packages to SQL Server 2005.

Under Stored Packages > msdb, if I right click the package name an select "run package", the package runs fine without errors.

However if I put the same package in a job and run the job, the job fails every time and won't tell me why. It just says to check the history. This is what the history says:

Executed as user: myDomain\SQLService. The package execution failed. The step failed.

Seems like some kind of permission failure.

Any ideas, please. This permissions stuff is driving me nuts.

Here are all the permission levels that I am aware of when running a job:

1.) permissions at the package level (conn mgrs, ftp mgrs, etc)

2.) rely on SQL Server storage for roles and access control - what does even mean?

3.) job owner (sa) - how does this differ from the job step owner?

4.) job step > run as [user] (SQL Server Agent Account) - why does this require a separate owner?

5.) package > server > Log on to the server [as user] - what the heck is this?

It's maddening. For example, I have no idea what #5 means - when it (the package) is logging on to the server, WHO is it logging on as if the package is running in a job? The job owner? The job step owner? Or something entirely different?

I've asked other people here if they have any clue how all these permissions interrelate, and frankly no one knows. It's a big mystery..... sort of like the big bang. Don't ask me how I passed my MCDBA. That is another great mystery of the universe.

It's no wonder I can't get this thing to run!

Thank you, error handler, for sending me task-related error messages.

So here's the problem:

The script threw an exception: Could not find a part of the path 'o:\myFolder'.

In one of my packages, I have a script that gets file names from the network drive, o:

My other packages reference the d: drive with no problem. It's the o: drive that's having problems.

So maybe whoever is running the package doesn't have permissions to the o: drive, right?

So, getting back to my first post, who needs the permission to read the o: drive? Probably the step owner?

|||

See this classic KB that explains it:

http://support.microsoft.com/default.aspx/kb/918760

The access to o: should be granted to the user who runs the package - Agent service account or Proxy account if you use it for this job. In your case this is currently myDomain\SQLService (from "Executed as user..." message). Also, if o: is network drive, switch to using UNC path (\\server\path) as drive mapping is user-specific.

|||

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

However, I still have some questions about how all the pkg/job permissions interrelate to each other:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

3.) job owner (sa) - how does this differ from the job step owner?

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

If anyone could answer any or all of these questions, and #5 especially, I would really appreciate it!

Thanks much

|||

sadie519590 wrote:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

sadie519590 wrote:

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

Without more context, I'm not sure. My best guess is that is referring to storing packages in SQL Server rather than the file system. If you do that, it can handle access to the packages.

sadie519590 wrote:

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

Yes, unless you have set up a proxy.

|||

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

That's because the conn mgrs will be using the SQL Agent account, right? (Because the this is job step owner?)

Thanks|||Correct.|||

sadie519590 wrote:

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

o: is a mapped drive, correct? Mapped drives are a user-session concept. That is, when you log on, you can map drives. However, if I remote into that box and log on as well, I don't get to see your mapped drives. Service accounts, such as those that run SQL Server, SQL Server Agent, etc... don't have any clue what a mapped drive is. As soon as you log off of the box, the mapped drives go away. So yes, if you are going to use network shares, you HAVE to use UNC.|||

Yes, the unc path did solve the problem.

Thanks all.

No comments:

Post a Comment