I have a stored procedure that if I execute while logged into server A it runs fine but when I try to schedule it to run with SQL Server Agent on Server A it fails miserably. The query / Stored Procedure is pulling data from Server B.
Little background on my SQL Servers configuration:
There are 2 SQL Server 2000 machines running on MS Server 2003 in a Workgroup called SQLWG. They are set up to replicate from one machine to the other. These SQL Servers are set up for Windows Auth.
I have a 3rd SQL Server but it's 2005 on a MS Server 2003 machine. It's in the workgroup WORKGROUP. It is configured for Mixed-Mode Auth.
I created a query that runs perfectly fine when I'm logged into the SQL Server 2005 box and getting results from either of the SQL 2000 machines. When I create it as a stored procedure and execute said stored procedure it works perfectly fine. When I schedule it to run as a SQL Server Agent it fails with the following:
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "MSDASQL" for linked server "SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSDASQL" for linked server "SQL" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.". [SQLSTATE 01000] (Error 7412). The step failed.
Not sure where the problem actually is at. I know all the information I've come across with this error recommends putting the SQL Server 2000 machines in Mixed Mode but that's not an option.
I can work around the issue by using an ASP page to query Server A and write to Server B but I'd like a solution a little more elegant and less taxing on both systems. If I'm looking in the wrong direction then a nudge in the right one would also be appreciated!