April 30, 2009 at 8:58 am
Alright, I'm stumped. In an effort to tighten up my SQL2000 server, I removed the SQL login for BUILTIN\Administrators. I soon found that this caused my SQL Server Agent jobs not to start, because the local system account that runs the service could no longer access SQL Server. The Agent service would simply start and immediately stop.
After some searching, it looked like I could resolve this by adding a sysadmin login called NT AUTHORITY\SYSTEM. I did this, and it allows the Agent service to start and stay running, however, when I run a job, the job always fails.
If I add back in the sysadmin BUILTIN\Administrators login, the jobs can successfully execute again.
Is there a way I can make this work with NT AUTHORITY\SYSTEM? Am I missing something? I really don't want the BUILTIN\Administrators group to have access to SQL Server.
May 1, 2009 at 12:06 pm
What account is running SQL Agent? You can add that account as a login.
May 1, 2009 at 12:26 pm
This was related to my other thread... 🙂
I've resolved this also. I removed access for BUILTIN\Administrators to lock down the box from my IT/hosting people. I then created a new domain user called {DOMAIN}>\sqlserveragent, which now runs the Agent service.
This got me to the point that the service would start, but when I tried to run a job it would always fail. I turned on Profiler and found that when I ran a job, {DOMAIN}\Administrator, which was the SQL Server service account (not the Agent account), was trying to log in to run sqlmaint.exe. Well, since I removed access to BUILTIN\Administrators, this account could not log in to SQL.
The next step was another new domain account, {DOMAIN}\sqlserver, which I now use to run the SQL Server service.
The whole reason for having to go through this was that, it turns out MS hardcoded something into a system stored procedure that equates access to Agent for NT AUTHORITY\SYSTEM with access to Agent for BUILTIN\Administrators. In other words, even if you have a specific sysadmin login for NT AUTHORITY\SYSTEM, it still will not get access to Agent if BUILTIN\Administrators does not also have sysadmin access. Very annoying, and very hard to track down.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply