3

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]

WMI Provider Error

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.

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

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:

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:

  • I already put a link to this in my question explaining I'd already found that article, and it is referring to permissions, which I've already tried (and more). Its not this. – Ian_H Mar 21 '18 at 12:58
  • I have added more information regarding the file/folder/WMI/Registry ACLs. – hot2use Mar 21 '18 at 16:11
  • Just to update you: the problem was actually with the ERRORLOG file itself, this does seem unusual though (I haven't seen another case like it from a lot of searching for this). I've added an answer to describe this. Although, it does seem that in most cases your answer does would be correct for other people, so I've given you an upvote because its probably useful for others in the future and its a good summary, just not actually the fix for this unique case. Cheers! – Ian_H Mar 23 '18 at 10:08
0

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

  • Congratulation on finding a solution. Could the permissions on the ERRORLOG file have been an issue? – hot2use Mar 23 '18 at 10:30
  • Thanks, no, I double (and triple!) checked permissions, the account had extensive permissions to that file and that still posed a problem, only removal of the file made the difference. Very strange. – Ian_H Mar 23 '18 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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