troubleshooting Question

Issues with executing Stored Procedure with SQL Server Agent

Avatar of Fiendly
FiendlyFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
3 Comments2 Solutions2100 ViewsLast Modified:
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:

Message
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!

Thanks!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros