Sql-server – SQL Server 2008r2 won’t start after windows updates, error log & wmi provider errors

configuration-managererror logsql serversql-server-2008-r2

I have a SQL Server 2008 R2 instance on a Windows 2008 R2 box, and windows has not been updated for almost a year (I know), last night windows patches were applied (around a 100 that were pending), and this morning SQL Server will not start.

The error in event viewer is:

initerrlog: Could not open error log file 'D:\Program Files\Microsoft
SQL Server\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG'. Operating system error
= 5(failed to retrieve text for this error. Reason: 15105).

As per many links (e.g.SQLServerCentral), I have found this does appear to be some kind of permissions issues. The service account SQL Server is configured to use is a network service account, I have checked this in AD and appears to be OK and the password is correct (nothing has changed since yesterday in that regard, when it was working) I have also tried:

Made the service account local admin on the box (temporarily)
Ensure that the service account was also added to the SQL Server groups on the server
Given explicit permission to the service account to the error log folder (full control); then to the whole program files folder (full control)

And I still get the same error.

Next, I tried to change the service account used (to either a network admin – (I know, just to test) or local service account) in Configuration Manager, when applying these changes I get a WMI Provider error:

WMI Provider Error [call to WMI Provider error code: 0x800742a2]

I am a bit confused by this and haven't seen the like before.

Does anyone have any ideas about what is going on, or anything else to try that I've missed?

Edit 1 :

To add, I've checked the first hits when searching for the WMI error MS Support, SQLServerCentral, but they still seem to be suggesting permissions problems. I've double checked all the above relating to permissions issues and can't see how that is the problem (The service account is now local admin (!), in the SQL Server groups on the box, and has explicit full control to all program files). I am wondering whether this WMI error is a red herring, or totally separate problem.

Edit 2 :

It seems there was something up with the ERRORLOG file that was preventing startup, not a permissions issue – not sure what, but perhaps a corruption or something of the file itself, as deleting it (well, copying it elsewhere) seems to have forced SQL Server to generate a new one and it started up OK. I'm not sure what is wrong with the errorlog file (I will see if I can open it and investigate), and I don't know what the WMI Provider error was, now that the service is online I can't fully test it now, I will try again later in some planned downtime to see if the WMI Provider comes up (and update this as appropriate/add as answer).

Edit 3 (final update):

Seems all of the symptoms were misleading and the usual cause (permissions) to this kind of problem wasn't the cause in this case, it was a problem with the ERRORLOG file itself, and removing it (to be recreated) solved the issue and services came up. I've added an answer describing this. However, this seems to be the only case I can find of these symptoms having this cause, and many cases where it is permissions, which I've included links to above, the answer in this question goes into good detail on. Therefore, if you have this problem in the future, I suggest follow those steps (checking permissions issue first), before checking out the ERRORLOG file itself.

Best Answer

The official solution as per Microsoft article is:

To work around this problem, add the domain user account to the SQLServer2005MSSQLUser$ComputerName$InstanceName domain group.

To do this, follow these steps:
1. Click Start, point to Administrative Tools, and then click Active Directory Users and Computers.
2. In the Active Directory Users and Computers snap-in, click Users.
3. Double-click SQLServer2005MSSQLUser$ComputerName$InstanceName.
4. In the Properties dialog box, click the Members tab.
5. On the Members tab, click Add.
6. In the Select Users, Contacts, Computers, or Groups dialog box, type the user in the DomainName\UserName format under Enter the object names to select, and then click OK. 7. In the Properties dialog box, click OK.

References

SQL Server Configuration Manager

When you run the SQL Server Configuration Manager and modify settings the configuration manager will add and remove permissions on files and directories based on the configuration changes made.

This is why Microsoft states, that configuration changes should always be made with the SQL Server Configuration Manager.

Important
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

Reference: SQL Server Configuration Manager (Microsoft Docs)

Actual Steps To Perform

In your case you might have performed several of the steps, but not in the correct order. Try the following:

Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to a user you don't want the service to run under
Restart the SQL Server (INSTANCE) service for your instance (should fail)
Close SQL Server Configuration Manager
Add the service account that you want the SQL Server service to run under to the specified group (as detailed at the beginning of the post; steps 1-7)
Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to the user you just added to the SQLServer2005MSSQLUser$ComputerName$InstanceName group.
Restart SQL Server (INSTANCE) service for your instance

In the case that this doesn't work you may have broken ACLs (permissions) at some level in the SQL Server Program Files structure.

SQL Server Accounts And ACLs

Your service still isn't starting and it could still be an issue with permissions (ACLs)?

Then you might have to fix the file/folder permissions. The full list of SQL Server Account and the required permissions are listed in the following exhaustive documentations:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2012)

Everywhere where MSSQLServer is referenced in the documentation, replace with the account you wish to run your SQL Server (INSTANCE) with.

For example when checking the Reviewing Access Control Lists Created for SQL Server Service Accounts section of the document, ensure that your YourServiceAccount has access to:

Service Account for | Files and Folders        | Access
--------------------+--------------------------+---------------
MSSQLServer         | Instid\MSSQL\backup      | Full control
                    | Instid\MSSQL\binn        | Read, Execute
                    | Instid\MSSQL\data        | Full control
....

Instead of MSSQLServer use YourServiceAccount.

References:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2016+)

Related Solutions

Sql-server – SQL Server Configuration Manager not display services

I know of one other thing that you can try. It affects SQL Server 2005 specifically, but might also be useful in your scenario. Essentially, the wmiprvse.exe executable runs under the context of NETWORK SERVICE. SSCM loads the Sqlmgmprovider.dll into this executable under the same security context. Ensure that NETWORK SERVICE has READ permissions to the SQL Services in question.

The steps needed to accomplish this are detailed in the Microsoft Support Article:

Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration.

To do this in a standalone environment is a bit trickier. Let's try this with SQL Browser first:

Open cmd.exe as an administrator.
Type the following command: sc <server_name> sdshow <service_name>
Copy the string returned to your favorite notepad clone, we will need this string to revert back to prior configuration if this does not work. This is our SDDL string. The next step will be our concatenated SDDL string.
Add the following to the end of the string: (A;;CCLCSWRPWPDTLOCRRC;;;NS)
Type the following command: sc <server_name> sdset <service_name> <concatenated_SDDL_string>
Open SSCM and see if SQL Browser displays as expected.

The two commands run should look something like this:

sc \\my-server sdshow SQLBrowser

sc \\my-server sdset SQLBrowser D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)(A;;CCLCSWRPWPDTLOCRRC;;;NS)

Sql-server – Windows could not start the SQL Server service error

Open SQL Server configuration manager (start-->Run-->SQLServerManager10.msc) and try to start the service. If there is a problem starting the service, locate the SQL server Error log (in the SQL service configuration manager advanced tab there is a location or path of the error log) and post the output in the forum.