SQL Agent SSIS job step fails with "The login is from an untrusted domain and cannot be used with Integrated authentication"

Peter Bishop 161 Reputation points
2023-09-15T16:28:42.2066667+00:00

A user raised an issue with me today where a SQL Agent SSIS job step was failing with:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication

The SSIS step was using an SSIS subsystem proxy rather than the SQL Server Agent account.

I tried recreating the job step and even abstracted the step into a standalone job, all without solving the problem

After some investigation (and Google) I found a reference that this could be caused by using a DNS alias in the SSIS catalog connection string rather than the actual hostname. Changing this to use the instance's hostname fixed the problem.

However I have several other equivalent job steps which do use the same DNS alias without issue and I'm pretty sure that this job step hasn't been changed and was previously working without issue.

Can anyone advise why:

  • DNS alias fails in this way but hostname works?
  • Why this works in some job steps but not in others?

The actual SSIS package doesn't even try to start so I don't think it's related to differences in the SSIS packages.

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
{count} votes