January 8, 2008 at 1:32 pm
Folks,
I seem to be having the ever-popular problem with a package.
It runs using the dtexec util with no issue, but seems to have some sort of issue as an SQL Agent job.
Package created by user A.
Package needs to run as user B (it has permissions on another server to get XML files).
I have:
created a LOGIN for user B, with sysadmin priviledges
created a CREDENTIAL for user B
created an SSIS/CmdExec proxy for using Credential for user B
Setup the job to be OWNED by user B, and the single step (of execute the project) setup to use proxy for user B.
When running, I get the typical "package could not be loaded", either from FILE or from SQL Server.
IF USER B is setup as an ADMINISTRATOR on the sql server machine, it DOES RUN FINE. If not (even if setup as power user), it gives the above error.
Any ideas here????????
What else should I be looking at -- the "logging" is non-existent here -- I have all turned on in package, but since the package isn't loaded, I get nothing.
January 10, 2008 at 10:47 am
Just to update other things that I have tried to get this to work, it looks like NONE of them work other than adding the id I'm using to the Administrators group on the sql server machine.
The user this needs to run as has to copy a file from a server over to a directory on this machine so that i can process it -- it is the first step in the ssis package.
I have:
setup the ID as a sysadmin role, added a credential for it, created a proxy with OnExec and SSIS package for that credential, and set that proxy as the Run As for the step (the error message DOES indicate that it is trying to run as that id, but can't load the package).
I have made sure that all files/directories that the package deals with has full control by this ID.
I have tried to add it to the following groups, all to no avail:
SQLAgentuserRole --added to msdb system db, and this role
SQLAgentReaderRole --added to msdb system db, and this role
SQLAgentOperator --added to msdb system db, and this role
SQLServer2005DTSUser$machinename
SQLServer2005SQLAgentUser$machinename
Power Users
All of these fail. If added to the Administrators group, it runs fine.
Since the first step is a .cmd file, I even added permissions for read/execute to the windows/system32/cmd.exe file (I Know that running scheduled jobs will sometimes fail if the user does not have exec perms to this file).
Are there any type of "hidden" directories SQLAgent uses to maybe precompile the package, or other permissions I need to look into to solve this? Since the package doesn't get loaded, there are no package logging events, and sql agent logging is abysmal.
Any and all help appreciated.
January 13, 2008 at 12:58 pm
I had a similar problem. Are you trying to access a file using a file connector and the file path is a mapped drive? If so, specify the full path, not using the mapped drive letter. That was my sin and cost me 3 days of trying everything until I replaced the mapped path with full path.
January 14, 2008 at 7:31 am
Thanks for the follow-up -- I am not using a mapped drive though. I am using the UNC filename, so that shouldn't be an issue.
I did get around it by replacing that file copy step with a new step that runs a custom C# console app to do the copy by impersonating the id I needed, but seems like there should be an easy way to do this from Sql Server Agent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply