The script can work, but the job failed

Question

The script can work, but the job failed

YTZ

SSCommitted

Points: 1707
More actions
October 12, 2010 at 11:24 am

#229624

I created a job that executes,
SELECT * FROM [LinkedServer].msdb.dbo.sysjobhistory
The scipt can work from Query Analyzer, SQL Server 2005 SP3/Win 2003.
But the job that executes this script failed with the error,
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.
Any help will be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Leo.Miller SSChampion Points: 13048 More actions · Answer 1

When the script is run from the agent it is being run in the context of the account that the SQL Server Agent runs under. In your case NT AUTHORITY\SYSTEM. You need to either allow the NT AUTHORITY\ANONYMOUS account logon rights on the linked server, or set the agent to run under an account that has the relevant rights on the linked server.

In general it's considered best practice to have your SQL Server Service and Agent running under a domain user account, and assign the relevant permissions to this account as required. The NT AUTHORITY\SYSTEM is an admin account and has permissions that the SQL Agent and SQL Service don't need.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

YTZ SSCommitted Points: 1707 More actions · Answer 2

Leo & All:

I got the solution and would like share it with all.

At begining, I setup a linked Server (LinkedServerB) on ServerA. This linked server was mapped from SA to a SQL account on ServerB.

I created the job on ServerA that SQL and SQL Agent are running under LocalSystem.

I can run the script from Query Analyzer,

SELECT * FROM [LinkedServerB].msdb.dbo.sysjobhistory

and failed it from the job.

Then I changed linked server mapped account [NT AUTHORITY\SYSTEM] instead of SA.

The job can work.

Since this is a production server, I don't have a schedule to restart SQL Server Agent if I change the running account.

Thanks, Leo.

berhankoc SSC Rookie Points: 44 More actions · Answer 3

Hi YTZ,

I have same problem my stored procedure works fine but job is getting belown error. What have you done and how you fixed ? Here is my production system and unfortunately i cant do much things.

Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Login failed for user 'ETH\SVNSERVER$'. [SQLSTATE 28000] (Error 18456). The step failed.