Thursday, July 14, 2011

SQL Server Fails To Start After Reboot - Using DOMAIN\user Service Account




Recently, I was trying to get a SQL server installation to backup to a network drive. This won't work unless SQL is configured to run under an account that has network priviledges. So to fix this I opened the service control panel and switched the logon properties for Sql Server (MSSQLSERVER) to a known domain\user account that has access to the network folder I wanted to back up to. After I did this everything worked fine, SQL was running, backups ran, I could even stop and start the service with the newly stored credentials. Life was good until the next time the server rebooted and SQL didn't automatically start like it was supposed to. To fix this I opened the service control panel and tried the start SQL and received this message "The service did not start due to a logon failure". 


When that happens this message is also written to the event viewer:


Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 7/14/2011
Time: 7:46:11 AM
User: N/A
Computer: RCX-FLEET
Description:
The SQL Server (MSSQLSERVER) service failed to start due to the following error: 
The service did not start due to a logon failure. 


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


My first thought was that I must have entered the logon credentials incorrectly. So again, I entered the password, clicked "apply" and started SQL thinking that everything was fine. But on the next reboot the same problem occured again. This time I dug deeper into the Event Viewer and found this entry buried in the System log section:


Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7041
Date: 7/14/2011
Time: 4:04:25 AM
User: N/A
Computer: RCX-FLEET
Description:
The MSSQLSERVER service was unable to log on as DOMAIN\user with the currently 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\user


This service account does not have the required user right "Log on as a service."


User Action


Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.


If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


This message is pretty straight forward. It tells you what the problem in and how to fix it, most messages aren't this good. Following the instructions I opened the Security Setting snap in (Secpol.msc), navigated to "Local Policies" -> "User Rights Assignment" -> "Log on as a Service." I right-clicked "Log on as a Service" selected "Properties" so I could add the domain\user account I used to start SQL. But to my surprise I found "Add User or Group" greyed out. Well it turns out that sometimes this is set at the Group Policy level and the only place you can add users is by logging on to the Domain Controller, going to "Domain Security Policy" -> "Local Policies" -> "User Right Assignment" -> "Log on as a Service". Once you get here you should be able to add the DOMAIN\user account you want SQL to run under. 

2 comments:

  1. For an effective website, host is actually vital and picking the best web hosting for your provider enhances your SEO alongside the sales. You may find numerous WordPress throwing options available, namely Free, Shared, VPS counting specialized and also took care of WordPress throwing.Optimized WordPress Hosting

    ReplyDelete
  2. Email look for tools are a click on out of email owner's public file. Within a few clicks, you can easily take the email deal with possession to the following degree and speak to an individual's past of criminal offenses, detentions, steering sentences as well as other vulnerable however available public information.email finder free

    ReplyDelete