holly kilpatrick 22222


I have an SSIS package with connections to Servers 1,2,3, and 4, and with four Execute SQL tasks each executing a stored procedure in the master database of each of the 4 servers. Servers 1 & 2 are SQL 2005, 3 & 4 are SQL 2000. All tasks in the package run successfully when executing interactively.

When scheduled as a SQL Agent job on Server 1, two of the Execute SQL tasks succeed and two fail. The two against the SQL 2005 succeed and the two querying the SQL2000 databases fail. The Server 1 SQL Agent service account is a domain account which is the service account on all four servers.

The error message is Executing the query "execute master.dbo.sp_collect_stats" failed with the following error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

Why does it think 'NT AUTHORITY\ANONYMOUS LOGON' is trying to log in I can't figure out what I am missing and hope that someone can give me a suggestion. Thank you.

Holly




Re: SQL Agent login problem

jwelch


Do the connections specify Integrated Security Also, just because the agent account is the service account on the SQL boxes, does not mean that it has access to the data on the server. Make sure that the account has been granted the specific permissions it needs.







Re: SQL Agent login problem

holly kilpatrick 22222

The connections use Windows Authentication. The account is also the service account on all four servers, and also has explicit permissions in master and I even gave it explicit permissions to execute the stored procedure, but I don't think that is the issue. I am beginning to think this is a double hop issue, because I found that when I log onto Server 1, and open SSMS, I can query Server 3 tables successfully using Linked Servers, and I can run another stored procedure in the Server 3 master database, but when I try to run this one, I get

Msg 18456, Level 14, State 1, Procedure sp_collect_stats, Line 57

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Line 57 is just deallocating a cursor. But a few lines further there is an insert into a table on Server 1. I can log onto Server 3, and manually run this stored procedure and it will insert the records in the Server 1 table. And I can run the SSIS package on my workstation, and it will insert the records. But if I log onto Server 1 as this service account and run the stored procedure in a Query Window, I get the Login Failed error above. And if I run it in SQL Agent, I get the error in my first email:

Executing the query "execute master.dbo.sp_collect_stats" failed with the following error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Holly






Re: SQL Agent login problem

jwelch

Could be Kerberos (double-hop) issues. Do you have to use linked servers, or could you connect to each server directly






Re: SQL Agent login problem

holly kilpatrick 22222

I am collecting data from each server and storing it in a central table on Server 1. Yes I could avoid the linked server issue by storing each server's data in a table on that server, and then consolidating the data to Server 1 afterwards, but that seemed redundant. And in fact this method is working fine with Server 2. Is there some issue because it is SQL 2000 to SQL 2005 I am running various linked server queries in stored procedures on Server 3 (2000) linking to Server 1 (2005) in another application without a problem, so I didn't think that was a limitation.





Re: SQL Agent login problem

jwelch

Well, I was referring to connecting directly to each SQL Server from the SSIS package. However, since it is your SP that is using the linked servers, that might not be practical.

It looks like this is really more of an engine problem than SSIS, since the error seems to arise whether the SP is called from SSIS or directly. If you agree, I'll move this post to the engine forum, where you may get some more ideas.






Re: SQL Agent login problem

holly kilpatrick 22222

Maybe I could work around the double hop/linked server reference in the stored procedure by taking our that part and using the stored procedure as the source for a data flow transformation. I'll try that. Good idea, thanks.

Sure, transfer to the engine forum if you think that would be more appropriate. Perhaps someone will understand why it works with Server 2 and not Servers 3 or 4.

Thanks,

Holly





Re: SQL Agent login problem

jwelch

Let's see if using the data flow fixes it. If so, we might want to leave this for future reference, and start a new thread on the engine forum.






Re: SQL Agent login problem

holly kilpatrick 22222

I rearranged the process to eliminate loading the table with a linked server statement within the stored procedure. I ended the stored procedure with a select statement, and put

SET NOCOUNT ON

SET FMTONLY OFF

at the top of the stored procedure so that the returned columns would be visible to the data transformation, and then used that stored procedure as the source and loaded the results into my storage table with the data transformation.

I would still like to understand why the first approach worked on some servers and not on others, if anyone knows.

Thanks,

Holly