Cannot connect to a remote server with error msg 18456

[jimmYLepp]

Hi,

I am trying to pull data from a remote SQL Server database on a regular schedue. When I run the procedure in QA it work great, when I try and run the job from a scheduled task it fails with this error :


Msg 18456, Sev 14: Login failed for user '\'. [SQLSTATE 28000]


Seems like SQL Agent cannot log into the remote box properly, even though I added the sa password as the linked server login. what is causing this and what is the best workaround. Also, the local server is NT 8 SQL 2000 and the remote box is NT 4, sql 7.0.

jimmmY

 

[ClaireHCM]

You should make sure that your SQL server agent is running under a domain account whichi is the same as the remote server/or those account should communicate with each other.

Setting up login as sa in linked server,is nothing to do with job execution.You need to have right setting for sql server service account,then only you can execute job in remote server.

 

[jimmYLepp]

Thnkas, looks like this is the problem, just tried it on another server in that domain and it worked.