SSIS Package Fails When Ran as Job with Login Failed error

SSIS and Import/Export
1

Hi experts, this is a frequent problem that has been posted many times.
My SSIS package uses a ForEach loop to cycle thru several servers via a Dynamic connection to collect info about each instance.
The package runs fine when I run it in Debug mode. But when it is executed by the Job Agent, it fails with a 'Login failed for [domain account]' error. The Agent service is a domain account which has sysadmin rights across all servers. How can I get the package to run as a job? Thanks

2

The agent job runs in the context of the account that is running SQL Server - not the account running SQL Server Agent. And it only does that when the agent job is owned by a sysadmin account...

If the agent job is not owned by a sysadmin account - it runs in the context of the owner of the agent job.

The prevent these kinds of issues/concerns you really should setup a proxy account for SSIS. Then you can tell SQL Server Agent to run the SSIS package with the proxy account to insure that the credentials being utilized for the job are the expected credentials and that account has the appropriate rights.

3

Thanks jeffw. Both agent and sql server run under the same domain account which has the sysadmin role. I own
the job and I also have the sysadmin role. This is why I'm perplexed. But the package cycles thru server instances, trying to connect to each to collect info. Still wondering how the package can runn successfully, connect to those servers and retrieve info - yet fail when ran as an agent job... Thanks

4

what is the source of the collection you are looping or cycling through? static or dynamic?
Have you checked each of the items in the collection are accurate? no typos etc?
if dynamic could one of these server maybe not allow connection maybe it is a linux server or a router :slight_smile:
are these sql servers?

5

Thanks, yosiasz. Yes these are all sql servers. Yesterday, I restricted the collection to only 1 sql server and the Job still failed with "login failed". I'm puzzled. John

6
  1. is the Agent User a legit AD user?
  2. Can you bring up SSMS using that user? Right Click SSMS shortcut then hold shift and right mouse click
7

Setup a proxy account on the instance where you are running the package - for testing create the proxy account with your credentials. Set the SSIS package to run as that proxy account...

If that is successful - modify the proxy account to use the domain account credentials. If that doesn't work then you know that domain account doesn't actually have privileges in SQL Server. It it does when - then you know that the context the package was running in was not that account.

Either way - your problem is the context in which that agent job is running. It is not running in the context you believe it to be running...that is why you should create a proxy account to insure that the SSIS package is Run As that account...

The owner of the agent job can be whatever you want - but the SSIS step needs to be set to the proxy account.

1 Like
8

yosiasz - Yes to both questions. Thanks