May 16, 2005 at 3:46 am
After rebooting one of our test SQL Servers I noticed the SQL Server Agent had not been restarted. When I try to restart it I get the following error:
Login failed for user 'NT AUTHORITY\SYSTEM'.
What puzzles me is that the SQL Server Service runs with the same local system account and this has restarted without any problems after reboot.
Any help or advice would be appreciated.
Carl
May 16, 2005 at 5:14 am
Sounds like "BUILIN\Administrators" was removed from SQL Server logins.
You have to either add it back or grant 'NT AUTHORITY\SYSTEM' to acces SQL Server with 'sa' privilege.
May 16, 2005 at 5:17 am
Yes. I meant to include the fact that the BUILTIN\Administrators account had been removed.
I have just done a quick google search and spotted it is likely to be this problem.
Why is the SQL SERVER service still running without any problems? Because this is set to use the LocalSystem account
May 16, 2005 at 6:39 am
There is because old SQL Server Agent sessions were still running after you removed the 'BUILTIN\Administrators" group.
May 16, 2005 at 7:07 am
I've just created a login for the NT AUTHORITY\SYSTEM account and granted access to the msdb database to this login.
The SQL Server Agent is now running fine.
Carl
May 16, 2005 at 7:10 am
So is the NT AUTHORITY\SYSTEM account the local system account?
May 16, 2005 at 7:11 am
Yes, It is.
May 16, 2005 at 7:55 am
Thanks for your help.
In terms of moving forward, it looks to me like it is best to run the SQL Services as a domain account rather than local system as it allows you to send email and write to the network. It is also better if you want to remove the BUILTIN\Administrators account. As it stands now, my test server has a login for the NT AUTHORITY\SYSTEM account to run SQL SERVER Agent; which is what I was trying to get away from by removing the BUILTIN\Administrators account.
I still don't get why the SQL SERVER Service is still able to run though? Does the SQL SERVER service run as the system account? Thats what it seems to be when I look in current activity?
May 17, 2005 at 5:18 am
My "best practice" is to configure sql server and sql agent to run under the same domain account that has no special privileges (except password never expires). This account doesn't even have to be in Windows Local Administrators group on the server. All required permissions are set automatically during installation or during account change in EM. This account is a member of sysadmin role, so you don't need to explicitely grant access to msdb. On connection tab of sql server agent properties make sure that Windows Authentication is selected to connect to sql server. Then sql server agent doesn't need BUILTIN/Administrator or any special login.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply