25

My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it starts just fine when I manually start the service.

Event Viewer shows that the service fails to start because of a connection timeout. There are 2 entries regarding this timeout:

Entry 1

A timeout was reached (30000 milliseconds) while waiting for the SQL Server (SQLEXPRESS) service to connect.

Entry 2

The SQL Server (SQLEXPRESS) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.

I checked the ERRORLOG files and there are no errors logged around the time of those Event Viewer entries.

Any ideas on the cause of this problem or how I can investigate further?

0

7 Answers 7

17

This issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services.

For starting your services automatically, SQL Server needs an account with rights to start services during operating system start up.

To solve this issue you need to go Administrative Tools > Services > SQL Server(MSSQLSERVER or the name of your instances) > Right Click > Properties > Move to Log On > Set the Log On option using a Local System Account

If you have chosen this option, hit on Browse Button and find the Local Service Account or Network service Account there > Click in Apply.

Restart your computer and you will able to start your SQL Server.

2

The SQL Server (SQLEXPRESS) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.

It looks like a simple timeout. Windows is trying to start a lot of services at startup, and all those services can sometimes resource contention, making the startup of other services slow. A service only has a finite amount of time to respond to the service control manager.

The easy fix for this is to set the service you care about to Automatic (Delayed Start). Then Windows won't start it until all the other Automatic services have started.

2

Problem: SQL 2016 would no longer start on Windows start-up, but started fine when using Services panel.

Background: On a stand-alone Windows 2016 server, we had three SQL Server Express instances: 2019, 2016 and 2014. All were installed at about the same time (2019 first, then 2016, then 2014). After completing some data migration tasks, we no longer needed 2014, and uninstalled it.

Solution: Windows Control Panel > Programs and Features > SQL 2016 > Uninstall/Change > Repair. At the end, the repair message was: Missing or unspecified settings that were configured to default values: Service SID support has been enabled on the service.

See: SQL Server service SID

1

Just a quick thought on something to look into, in a domain type environment, some operating systems allow you to logon to the server before full network connectivity is established. You may want to check for either local or domain level group policy settings to not allow logon or OS startup until full network connectivity is established.

Just in case you notice this when you log onto the server after reboots, it actually logs onto the OS with the cached credential before it can reach the domain controllers to authenticate (network connectivity not fully established) with the login credential if it's a domain credential the SQLExpress service account is running as.

Not sure if that's exactly applicable in your case but this is something to at least simply investigate and try to test at least just in case.

I found this in some article I saved long ago when I had a similar issue with an AD home directory (not via login script) to map home directory for a workstation PC:

The policy value for Computer Configuration -> Administrative Templates -> System -> Logon “Always Wait for the Network at Computer Startup and Logon” will be set to “Enabled”.

If the following registry value doesn’t exist or its value is not set to 1, then this is a finding:

Registry Hive: HKEY_LOCAL_MACHINE Subkey: \Software\Policies\Microsoft\Windows NT\CurrentVersion\Winlogon\ Value Name: SyncForegroundPolicy Type: REG_DWORD Value: 1

1

Have you tried logging on as the local Administrator?

I have been having this exact same problem with SQL 2014 Express on Windows 10. I wanted to create a new user that wasn't the built-in local Administrator so I created a new user, added it to the Administrators group, set that user to autologon upon boot et voila! SQL Server service fails to start automatically. I tried delayed start, tried Local Service, tried Network Service, tried the new administrator user, tried the Always Wait... group policy setting. Nothing worked.

Even uninstalled SQL Server 2014 Express, rebooted, manually removed all leftover files/folders and rebooted again and reinstalled under the new administrator user. Still no go.

Switched back to autologon as the built-in Administrator account et voila! SQL Server service is now starting automatically.

This is obviously a workaround for me so I am still researching.

** UPDATE **

Actually, my problem was rooted in the machine having been renamed previously. Once I changed the "OriginalMachineName" value in the registry, problem solved!

1

In my case (Windows Server 2019 and SQL Server 2019), this was due to the fact that the user responsible for running as a service does not have the right to log on as a service.

From Fix: The service did not start due to a logon failure by Milan Stanojevic:

If the right to log on as a service is revoked for the user account, restore it on a domain controller or a member server (standalone) depending on your situation.

How to restore user’s right on a Domain Controller

Here’s how to do this if the user is in an Active Directory domain:

  1. Right-click Start
  2. Click Control Panel
  3. Type Administrative tools and select it
  4. Click Active Directory Users and Computers
  5. Right-click the organization unit in which the user right to log on as a service was granted (Domain Controllers organizational unit by default)
  6. Right-click the container you want then click Properties
  7. Go to Group Policy tab
  8. Click Default Domain Controllers Policy
  9. Click Edit to start Group Policy Manager
  10. Expand Computer Configuration
  11. Expand Windows Settings
  12. Expand Security Settings.
  13. Expand Local Policies
  14. Click User Rights Assignment
  15. Right-click Log on as a service from the right pane
  16. Click Add User or Group.
  17. Type the name that you want to add to the policy in the User and Group Names box
  18. Click OK.
  19. Exit Group Policy Manager
  20. Close Group Policy properties,
  21. Exit the Active Directory Users and Computers Microsoft Management Console (MMC) snap-in

How to restore user’s right on a Member Server (stand-alone)

Here’s how to do this if a user is a member of a standalone member server:

  1. Start Local Security Settings MMC snap-in.
  2. Expand Local Policies.
  3. Click the User Rights Assignment.
  4. Right-click Log on as a service from the right pane.
  5. Click Add User or Group.
  6. Type the name that you want to add to the policy in the User and Group Names box.
  7. Click OK.
  8. Quit the Local Security Settings MMC snap-in.
0
-1

I went to Local service and changed

SQL Server(MSSQLSERVER SQL Server Agent (MSSQLSERVER)

Set them to Automatic Delayed and Logon type to

Local System Account Tick ( Allow service to Interact with desktop)

And it starts automatically

1
  • Changing the account to Local System and "Allow service to Interact with desktop" are un-helpful. Delayed Start is what fixes this issue. Jan 18, 2020 at 19:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.