March 13, 2009 at 2:31 pm
I have two production servers connected via Linked Server:
Server A is SQL 2008(Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)
Server B is SQL 2000 (Microsoft SQL Server 2000 - 8.00.760)
I have a Job running on Server A. The job includes 3 steps, with each step executing a local SP. Step # 2 fails. The SP used in step #2 deletes data on server B before inserting data. The Job Step #2 is running under the context of a User [SOLOMON_DTS], who has PUBLIC and SYSADMIN server roles on server A. User SOLOMON_DTS is on both servers with the same password.[SOLOMON_DTS] is a sysadmin on server B.
Currently, as a stop gap measure, I',m running all the 3 SPs manually on Server A. Problem appears to be when the Job is executing step 2(SP2).
The error message is as follows:
03/11/2009 22:10:02,SOLOMON PO UPLOAD,Error,2,PRINCIPAL1,SOLOMON PO UPLOAD,Exec usp_Admin_Solomon_PO_Upload_process_2,,Executed as user: SOLOMON_USER.
The OLE DB provider "SQLNCLI10" for linked server "everdrivecorp05" reported an error.
Authentication failed. [SQLSTATE 42000] (Error 7399)
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "everdrivecorp05".
[SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI10" for linked server "everdrivecorp05" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
The step failed.,00:00:01,16,7412,,,,0
March 15, 2009 at 10:30 pm
Hi Manoj,
Please verify whether the Owner of the job has access to the Linked server.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply