That error message is almost always related to the "Double Hop" problem. The easiest way to fix it is to make sure all trusted connections are connecting using kerberos instead of NTLM. The alternative would be to use a SQL user instead of trusted auth.

Setting up kerberos is not a simple undertaking, but will resolve this problem and other problems you'll likely experience in the future. I have a many page document specific to our environment (so I can't share all of it) that describes how to set it up. My opening description is as follows:

Configuring SPN's Allows for machines to connect to SQL Server with Kerberos authentication instead of NTLM. Microsoft does not allow NTLM authentication for connections using what is known as the "Double Hop". Microsoft does allow connections using Kerberos to connect using the "Double Hop".

A typical Scenario where you would connect through the "Double Hop" would be by using a linked server. If you connect to SQLSRV1 using Trusted Windows Authentication, and then Select from a linked server that is configured to pass the login's current security context, you have just made a "Double Hop".

If you attempt to do this utilizing NTLM you will get an error saying "Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" This error will occur whether you're trying to connect through IIS first, through SSIS, SSRS, or even while trying to do bulk file operations to a file server as the second hop.

Run the following code on your backend SQL server to determine whether the connections to the server are using kerberos or not.

SELECT sys.dm_exec_connections.session_id AS SPID,

sys.dm_exec_connections.connect_time AS Connect_Time,

DB_NAME(dbid) AS DatabaseName,

loginame AS LoginName,

sys.dm_exec_connections.auth_scheme as Auth_Scheme,

sys.dm_exec_connections.net_transport AS Net_Transport,

sys.dm_exec_connections.protocol_type as Protocol_Type,

sys.dm_exec_connections.client_net_address as Client_Net_Address,

sys.dm_exec_connections.local_net_address as Local_Net_Address,

sys.dm_exec_connections.local_tcp_port as Local_TCP_Port

FROM sys.sysprocesses

Right Outer JOIN sys.dm_exec_connections

ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id

Order By Auth_Scheme, Net_Transport