SSMS SQLAgent Job succeeds when started manually, fails when triggered by schedule

Question

I have a job designed in my SQL Server with 5 steps. Each step is functionally the same, extract data from source ERP database via ODBC connection, load raw data, transform raw data, load clean data.

Each step succeeds when:

1. When run in SSIS
2. When executed individually from the SSISDB menu
3. When the named job is triggered manually
4. When triggered by a Schedule while I'm still logged into the server via an RDP

It fails only when I am not logged in.

When I check the failed log files it says my account was the caller, but it still sends an ODBC connection string without the provided password.

Each job step is individually set to run as myself via proxy. I don't see any option for executing the scheduled job as myself.

What else am I missing?

Answer 1

You likely need to

1. Create a stored credential. Either as you or a dedicated service account. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/credentials-database-engine?view=sql-server-ver16

2. Create a proxy that uses your credential. https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver16

3. Grant the Proxy access to the SSIS subsystem https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-grant-proxy-to-subsystem-transact-sql?view=sql-server-ver16

4. Modify the Agent Job Step and select the Proxy in the drop down (currently blank)