SQL Server 2008r2 won't start after windows updates, error log & wmi provider errors

Question

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.

Answer 1

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

• Error message when you change the service account of SQL Server 2008 components to a non-domain administrator account on a Windows Server 2008 domain controller: "The service did not start due to a logon failure" or "WMI Provider Error" (Microsoft Support)

• WMI Provider Error - When Changing SQL Services Account (SQLServerCentral)

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:

1. Open SQL Server Configuration Manager
2. Set the SQL Server (INSTANCE) service account to a user you don't want the service to run under
3. Restart the SQL Server (INSTANCE) service for your instance (should fail)
4. Close SQL Server Configuration Manager
5. 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)
6. Open SQL Server Configuration Manager
7. Set the SQL Server (INSTANCE) service account to the user you just added to the SQLServer2005MSSQLUser$ComputerName$InstanceName group.
8. 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+)

Answer 2

This is the final update/fix:

I suspect that this issue was fairly unique, and all of the indicated problems were misleading - they all lead to permissions issues, which turned out to not be the problem. As highlighted by another answer, it does seem that in most cases, that would be the problem.

In this case, there seems to have been a problem with the ERRORLOG file itself, removing the file from the location seems to have forced SQL Server to generate a new one on startup and services then came up correctly.

Later I tested changing settings in configuration manager and there was no sign of the WMI Provider error, so that seems to have been misleading as well. It all seemed to come down to a problem with the ERRORLOG file itself, rather than access to it. I will try and interrogate that ERRORLOG file and see if I can find out what was wrong with it (and update as appropriate), but for now it seems the problem is resolved.

Summary:

In most cases, these symptoms are probably a permissions problem, with links to similar issues in the original question and detailed in the answer by @hot2use.

However, (in what is likely infrequent cases, and was in this case) sometimes the problem is an issue with the ERRORLOG file itself, not permissions to access it, and deleting (or just moving somewhere else) seems to force SQL Server service to come up and generate a new ERRORLOG file.

Additional information/links:

Links to similar problem from original post:-

SQLServerCentral | MsSupport | SQLAuthority

A different kind of problem with WMI Provider (that seems to have similar symptoms):- MsSupport

Link to answer on this question that goes into detail about the permissions error, which is often the cause with these symptoms (just not this time!):- AnswerByHot2use