Working on a new stored proc, I encountered an error when trying to run a job that called the stored proc. The error message was:
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI" for linked server "costpoint_rw" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "costpoint_rw". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "costpoint_rw" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.
The solution I found was to modify the modify the security context for the linked server to map the NT AUTHORITY\SYSTEM account to the SQL login on the remote server that had the rights I needed. I'm sure there are other ways to do it. I'm guessing that using a Proxy would be another way to solve it. But in the case, I chose to go this route.
On the linked server's properties, go to the section on Security. In this case my setting was "Be made without using a security context." Then I mapped the local server login (NT AUTHORITY\SYSTEM in this case) to a local account on the remote server by entering the remote login's user and password information.
Doing the above solved the first error but then I received errors that "Server 'myserver' is not configured for RPC". In SQL Server 2000 this was enabled by default but in SQL Server 2005 it is not. Go to the linked server properties and then check the tab for Server Options. Make sure that "RPC" and "RPC Out" are both set to True.