SQL Server Agent Not Executing Job

Question

We tried to schedule job in Sql server to take database backup. But it has been failed to execute.

While checking logs we could see 2 different errors.

SQL Agent error logs

Error,[382] Logon to server 'ServerName' failed (ConnLogJobHistory)
Error,[298] SQLServer Error: 18456 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000]
Error,[382] Logon to server 'ServerName' failed (ConnSetJobCompletionState)
08/18/2021 05:50:00,,Error,[382] Logon to server 'ServerName' failed (ConnAttemptCachableOp)

Event viewer windows log

Login failed for user 'UserName'. Reason: Could not find a login matching the name provided. [CLIENT: {ServerIp}]

What we need to actually do to fix this problem.

Answer 1

Are you using integrated security for your SQL Agent service or connecting via a SQL Login? Either way it seems that you don't have a Login created for it.

You can check to see if you have a Login by running

SELECT *
FROM sys.server_principals s
WHERE s.name = 'UserName'

If you don't then you need to create it. If you are using a Windows account then you would run

CREATE LOGIN [domain\UserName] FROM Windows

If you are using a SQL Login to connect you will need to create the SQL Login. Please refer to https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver15 for the exact syntax for that, but generally it looks like

 CREATE LOGIN [UserName] FROM PASSWORD = 'supercomplexPassword!@#'

Answer 2

When you see NT AUTHORITY\ANONYMOUS LOGON that typically means a Kerberos authentication failure.

It sounds like SQL Server Agent is trying to connect to SQL Server using a Windows account that does not exist in Windows on the SQL Server machine.

Most likely this issue can be resolved by configuring SQL Server Agent via SQL Server Configuration Manager to run using an account that has the required permissions in SQL Server. See Select an account for the SQL Server Agent service for more information.