I've got some ridiculously stubborn difficulties when trying to run SSIS packages SQL Server Agent jobs on two SQL Servers, one in a domain and one without. I've read hundreds of posts on dozens of different forums, alas to no avail. Applying fixes and solutions I've found (eg. using proxies) only resulted in getting (slightly) different error messages. When running the packages from Visual Studio or from SQL Server Management Studio directly they execute as expected.
This is what I've got so far:
Machine A
- is named APP-TEST-SERVER
- is not in domain
- runs SQL Server 2016
- SQL Server Agent is run by NT Service\SQLSERVERAGENT and is sysadmin in SQL Server
- Integration Services is run by NT Service\MsDtsServer130 (not in SQL Server and cannot be added either)
- has a proxy named ssis_proxy which is a local login in the machine the SQL Server is running and is sysadmin in SQL Server (temporarily, of course, to see if it is the one that causes permission/login issues)
When running the job that has its step run as SQL Server Agent Service Account I get the following error message:
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 2:59:44 PM Failed to execute IS server package because of error 0x80131904. Server: 172.16.2.107, Package path: \SSISDB\test\test sp run\Package.dtsx, Environment reference Id: 6. Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Source: .Net SqlClient Data Provider Started: 2:59:44 PM Finished: 2:59:44 PM Elapsed: 0.171 seconds. The package execution failed. The step failed.
What is that NT AUTHORITY\ANONYMOUS LOGON nonsense? Shouldn't it use the Windows authentication that is used by SQL Server Agent (i.e. NT Service\SQLSERVERAGENT)? How can that be anonymous? Could it be that MsDtsServer130 not being added as login in SQL Server is causing this trouble? What can I do in this case? MsDtsServer130 cannot be added as login in SQL Server.
When it is run with step run as set to a proxy ssis_proxy I get:
Executed as user: APP-TEST-SERVER\ssis_proxy. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 3:23:15 PM Failed to execute IS server package because of error 0x80131904. Server: 172.16.2.107, Package path: \SSISDB\test\test sp run\Package.dtsx, Environment reference Id: 6. Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. Source: .Net SqlClient Data Provider Started: 3:23:15 PM Finished: 3:23:15 PM Elapsed: 0.172 seconds. The package execution failed. The step failed.
Why is it mentioning untrusted domain:
- for a login that can connect using Windows authentication in SQL Server Management Studio?
- on a machine that is not in a domain anyway?
Machine B
- is named LINESRV03
- is in domain LINEAR
- runs SQL Server 2014
- SQL Server Agent is run by NT AUTHORITY\NETWORKSERVICE and is sysadmin
- Integration Services is also run by NT AUTHORITY\NETWORKSERVICE
- has a proxy named busztv, which is a domain user in LINEAR and is sysadmin (temporarily, of course, to see if it is the one that causes permission/login issues) in SQL Server
When running the job that has its step run as SQL Server Agent Service Account I get the following error message:
Executed as user: LINEAR\LINESRV03$. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:31:31 Failed to execute IS server package because of error 0x80131904. Server: 172.16.2.80, Package path: \SSISDB\test\test sp run\Package.dtsx, Environment reference Id: 10. Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. Source: .Net SqlClient Data Provider Started: 15:31:31 Finished: 15:31:31 Elapsed: 0.328 seconds. The package execution failed. The step failed.
Again this untrusted domain.
I just cannot figure out who is the distrustful here, Integration Services or SQL Server but providing that other (non-ssis) jobs are run and the Agent runs it on its own makes me thin SQL Server is fine with NT AUTHORITY\NETWORKSERVICE and its Integration Services that simply cannot get over it.
When it is run with step run as set to a proxy busztv I get:
Executed as user: LINEAR\busztv. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:33:25 Failed to execute IS server package because of error 0x80131904. Server: 172.16.2.80, Package path: \SSISDB\test\test sp run\Package.dtsx, Environment reference Id: 10. Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. Source: .Net SqlClient Data Provider Started: 15:33:25 Finished: 15:33:26 Elapsed: 0.312 seconds. The package execution failed. The step failed.
It seems like it has some serious trust issues and it doesn't trust itself. This untrusted domain is annoying beyond any measure and this #@!% wickedly refrains from telling which login and which domain it is talking about.
EDIT:
The packages on the two servers were the same, both only trying to access a database on the SQL Server that's on the machine Integration Services is (ie. no remote servers accessed, not even through linked server) and file resources (delete and copy an SQLite database file which is later accessed through ODBC) on the same machine. Also, the proxies were given all the necessary permissions required (they can access the databases and the files). The tasks were removed one by one from the packages to see if they were the reason that prevented the job from running until there was nothing left there. Currently the task doesn't do anything and has no connection managers remaining. Still, the job fails with the same error messages as before.
Could anyone shed some light on how to kill this dragon?
Best Answer
Are you still having this problem? I don't have enough rep to comment but have you tried setting disableloopback=1 in the registry on the servers? The 'login is from an untrusted domain and cannot be used with Windows authentication' error can be caused by the loop back check failing.