One of our virtual server chose to reboot last night the OS was able to recover without issues. But for some reason SQL Server refused to work; I looked to event viwer, I see following error message logged in Security Event Log:

Event Type: Error
Event Source: Service Control Administrator
Event Category: None
Event ID: 7000Date: 9/17/2009
Time: 10:06:56 AM
User: N/A
Computer: ServerName
Description: The SQL Server (MSSQLSERVER) service failed to start due to the following error: The service did not start due to a logon failure.

So my first suspision was password was incorrect, but Service has been running for a while successfully so what caused it to stop working? In any case we tried re-applying security credintials by SQL Server Configuration Administrator to no avail. So digging through the Event Logs again I ran by this message:

Event Type: Error
Event Source: Service Control Administrator
Event Category: None
Event ID: 7041
Date: 9/17/2009
Time: 10:06:56 AM
User: N/A
Computer: ServerName
Description:The MSSQLSERVER service was unable to log on as DOMAIN\ServiceAccount with the now configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.

Service: MSSQLSERVER
Domain and account: DOMAIN\ServiceAccount

This service account does not have the necessary user right “Log on as a service.” User Action Assign “Log on as a service” to the service account on this computer.

I was suprised to see the eroror because service account has been running as service for a while; I am not sure how the account got removed from “Log on as service” assemble policies. But I launched gpedit.msc and went to …

Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment -> Log on as service

I tried to add the service account but option was disabled; talking to Windows Services they cannot clarify why it is disabled.

With SQL Server 2005 everytime we are configuring services it is recommend to use “SQL Server Configuration Administrator” because it does various system security configuration for registery and file system that will be missed if by the fixed Service Administrator, but it seems when setting service account in this utility it does not add the account to the assemble policy. I used fixed Service Administrator console (services.msc) to reapply the service account.

This time I get a message “Account has been added to Log On as Service”.

Restarted SQL Services, things working successfully now.

Problem: SQL Server 2005 failed to start because service account does not have access to Log On As service in assemble policies.
Solution: Apply the security settings by the Service Management console (services.msc); by the SQL Server Configuration Administrator does not seem to add the account to assemble policies.

Check it out:SQL Learnings