5

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?

Improve this question
6
  • 1
    Is it the same package on both servers or different? What are your packages doing? Are they only accessing local resources or are they reaching out to Server C?
    – billinkc
    Commented Feb 28, 2017 at 15:51
  • Yes, the packages were the same on both servers, with connections only accessing resources local to the SQL Server, with two connections, one OLE DB connecting the SQL Server as source and an ODBC connecting to an SQLite database as destination. There was also a file delete and copy. No C servers or linked server through SQL Server accessed. I've removed actions one by one until everything was removed to see if it would make any difference. It didn't. Currently the packages aren't doing anything at all, except failing to run.
    – gemisigo
    Commented Feb 28, 2017 at 21:51
  • Ive had problems in the past with sql agent jobs where it really uses the creds of the database service. Especially when trying to connect to another machine. Try running the db service as a domain acct maybe yours to see if that helps.
    – Sir Swears-a-lot
    Commented Mar 2, 2017 at 7:58
  • What is the job trying to do? Connect to remote db? Read/write to a file system?
    – Sir Swears-a-lot
    Commented Mar 2, 2017 at 8:00
  • Running the db service as a domain acct on the machine that is not part of a domain might prove difficult. Adding NT AUTHORITY\ANONYMOUS LOGON the required permissions would work (yes, I tried) but no sane person would do that. The job is only trying to run an SSIS package (which is now empty). It also wants to delete and copy an SQLite database file, and write it through ODBC, but does not connect to remote servers. I've edited the question to include some information about the job (see at the bottom).
    – gemisigo
    Commented Mar 2, 2017 at 12:51

2 Answers 2

Reset to default
0

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.

Improve this answer
2
  • This might be a viable solution if you could provide more details. i.e. the path in the registry, a slightly better formatting.
    – John K. N.
    Commented Jun 20, 2019 at 12:14
  • That would be a DWORD key inside hklm:\SYSTEM\CurrentControlSet\Control\Lsa\ (disableloopback...)
    – P-L
    Commented Aug 1, 2019 at 19:02
0

You might be encountering an issue with CLR not having been enabled on your SQL Server instance. This can cause issues with SSIS packages.

Check your CLR setting with the following command:

sp_configure 'clr enabled'

If the config_value and the run_value returns 0 then CLR has been disabled. Turn CLR on by issuing the following commands:

sp_configure 'clr enabled', 1
go
reconfigure
go

Check that the value has been set with:

sp_configure 'clr enabled'

If the config_value returns 1 and the run_value returns 0 then CLR has been enabled, but has not been activated. Execute the following command:

reconfigure
go

And then check again.

Improve this answer
2
  • Welcome to DBA.SE and thank you for your contribution. While your initial answer did provide a hint of what the issue could be, it did not provide a solution on how to resolve the issue. It is always preferable to provide an actual solution. I have edited your answer to provide you with an idea of how a good answer looks like. Feel free to edit your answer to include anything I might have missed. Your initial answer popped up in the low quality queue, because of its length and quality.
    – John K. N.
    Commented Jun 20, 2019 at 11:34
  • Thank you very much for your suggestion. This is a rather old issue (which it isn't even any longer), and I no longer remember how it was solved. However, it probably wasn't by enabling CLR or disabling loopback, or else those would ring a bell here. Nevertheless, I'll add those to my checklist for the future. Also, @hot2use, thanks for keeping these comments nice and clean. It's much appreciated ;)
    – gemisigo
    Commented Jun 20, 2019 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.