SQL Agent Job Sudden Failure, Login failed for user NT AUTHORITY\SYSTEM.
-
I have a scheduled job (runs every Friday at 5am) that has been running since 9/2011. Suddenly, the job failed this morning with a "Login failed for user 'NT AUTHORITY\SYSTEM'... " error message.
1) There are other jobs that run using the same account. Just to test, I re-started a different job this morning and it ran fine.
2) I took all of the code from the original job (that is now failing), created a new job that is identical (except for the names) and it runs fine.
Without getting into the pro(s) and con(s) of running services under NETWORK SERVICE or the BUILTIN\Administrator (PLEASE!), has anyone seen this before? Is this just some incident of a job becoming corrupt?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
When you say "run using the same account" do you mean the jobs have the same owner?
Are there any job steps that use proxies?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
DB_Newbie2007 (2/1/2013)
"Login failed for user 'NT AUTHORITY\SYSTEM'... "Is that message coming from the SQL Agent Job, or the code that the job is running?
Are there any other errors or warnings in the Windows Event Viewer?
--------------------
Colt 45 - the original point and click interface
-
Sorry for taking so long to response back on this... I know I personally do not like it when a thread suddenly "quits" without any resolution, especially when I am trying to research a problem.
- There were no errors in the Events log.
- The error was from SQL Agent (the code, run manually, executed fine).
- Yes, all of the jobs have the same "owner".
As mentioned in the original post, "I took all of the code from the original job (that is now failing), created a new job that is identical (except for the names) and it runs fine.", so the problem was resolved. However, I am still curious if anyone else ever experienced this kind of a problem or might have some insight as to the cause?
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
Any proxies in play?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Nope. No proxies.
The job is actually run with a custom higher level account with full permissions in every database. Yes, it has full SA permissions... again, please no discussions on the use of an "SA" level account to run the job... focus on the particular issue at hand, thanks! 🙂 😀
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
DB_Newbie2007 (2/11/2013)
Nope. No proxies.The job is actually run with a custom higher level account with full permissions in every database. Yes, it has full SA permissions... again, please no discussions on the use of an "SA" level account to run the job... focus on the particular issue at hand, thanks! 🙂 😀
What do you mean by that? The Login running the job has nothing to do with the security context the job runs under.
I am thinking the job owner of the original job left the company and their Active Directory account was deleted. Or if it was a SQL Login the login was deleted or they were removed from the sysadmin Role and that is why the job started failing.
When you recreated the job the owner is now you, or maybe you made it sa or some other valid login so now it works.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sorry for any confusion... I believe there are a few levels of "ownership" (http://www.sqlservercentral.com/articles/SQL+Jobs/68764/) per my understanding?
1) The account that is configured to run SQL Server Agent service.
2) The account that has "ownership" of the job (which is what I was referring to previously):
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO
3) The Step "run as" (i.e., proxy account).
The "job owner" (listed from the query above) is an account that is in the sysadmin fixed server role, so I believe the step should be executed under the account used by the SQL Server Agent service (NT AUTHORITY\SYSTEM in our case), as are all of the jobs on this server. We use a specific account for all of the "job owners".... we try to not do anything using the "sa" account (I could not tell you what the sa pwd is... it is locked away in a drawer).
The job step has the "run as" left blank (i.e., the "Proxy account").
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
DB_Newbie2007 (2/11/2013)
Sorry for any confusion... I believe there are a few levels of "ownership" (http://www.sqlservercentral.com/articles/SQL+Jobs/68764/) per my understanding?1) The account that is configured to run SQL Server Agent service.
2) The account that has "ownership" of the job (which is what I was referring to previously):
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO
3) The Step "run as" (i.e., proxy account).
The "job owner" (listed from the query above) is an account that is in the sysadmin fixed server role, so I believe the step should be executed under the account used by the SQL Server Agent service (NT AUTHORITY\SYSTEM in our case), as are all of the jobs on this server. We use a specific account for all of the "job owners".... we try to not do anything using the "sa" account (I could not tell you what the sa pwd is... it is locked away in a drawer).
The job step has the "run as" left blank (i.e., the "Proxy account").
Thanks! 🙂
You can still allow jobs to be owned by sa even if the Login is disabled or the password is unknown. In fact it's actually a pretty common thing for people to do by default. If done this way the job always runs in the context of the SQL Server Agent service account even if proxies for specific steps are defined.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Could it be there was a momentary loss of connectivity to the domain controller and the credentials passed could not be verified? I don't recall if I've encountered this particular error or not, probably.
----------------------------------------------------
-
Couple of weeks ago I was also experiencing the same error. What I did was to assign admin roles to nt authority/system within SQL Server and the error was resolved but I'm not sure of the implecations of assign admin right to nt authority/system, my gut tells me its not best practise but so far so good. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply