Job failure. - Login Failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Job failure. - Login Failed

Author

Topic

tovkumar
Starting Member

6 Posts

Posted - 2009-03-03 : 04:29:50

Hi

I am getting problem in my job execution. When i execute my job, give below error message :

Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.

can anyone let me know what mistake i made ?

thank
kmr

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-03 : 06:22:22

Is it a replication or SSIS job? Any way you are trying to access remote server without valid credentials and to run the job you need to specify that.

tovkumar
Starting Member

6 Posts

Posted - 2009-03-03 : 06:27:48

Hi thanks for your reply. This is not SSIS.

This job contains my stored procedure (USP_CalculateDataCapture) which is running in management studio without any problem when
i run seperately.

EXEC USP_CalculateDataCapture @DCId = 2

but the same, when i run it through job which is not working. it gives the below error message.

Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.

Kindly let me what should i do to solve this error!

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-03 : 07:41:35

check out the account sql server agent runs under and verify that it has access to sql server instance. look also at job owner, try to change it sa from whatever it is now

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

tovkumar
Starting Member

6 Posts

Posted - 2009-03-03 : 08:48:11

hi subhash

i have many jobs which are all running without any problem, even the same user i have assigned it in another job running perfectly. Now I have deleted the job and recreated and assigned sa user too. but still the same error!!!

NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-03 : 10:19:38

Look into the code of stored procedure. You may be doing anything that access resource out of the current SQL Server instance (may be some network resource, file system, or other services).

tovkumar
Starting Member

6 Posts

Posted - 2009-03-03 : 23:45:53

i am using sqlserver authendication to connect the server by giving userid and pwd.

i have many jobs which are all running without any problem, even the same user i have assigned it in another job which is running perfectly. Now I have deleted the job and recreated and assigned sa user too. but still the same error!!!

NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.

This SP which i am using in job is creating many linkedserver by reading of another table which contained location and it's ip number.

primary stored procedure :
.
.
.

EXEC @WMS_Error = USP_CreateLinkedServer @ServerIpAddress = @ServerIpAddress, @SQLLogin = @SQLLogin, @SQLPassword = @SQLPassword
SET @TempName = 'Job_Usp_' + @DCName
.
.
.

Calling Stored Procedure : (USP_CreateLinkedServer)
.
.
IF ( NOT EXISTS ( SELECT * FROM master.dbo.sysservers WHERE srvname = @ServerIPAddress ) )
BEGIN
EXEC @WMS_Error = sp_addlinkedserver @server = @ServerIPAddress, @srvproduct = 'SQL Server'
IF ( @WMS_Error = 0 )
BEGIN
EXEC @WMS_Error = sp_addlinkedsrvlogin @rmtsrvname = @ServerIPAddress ,@useself = 'false' ,@rmtuser = @SQLLogin ,@rmtpassword = @SQLpassword
END
.
.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources