Initerrlog could not open error log file error log

Scenario: Created a VPC using Oracle VM VirtualBox. Installed SQL Server 2008 using local user accounts. While installing AX 2012, it fails because VPC machine is not in domain. So I ran dcpromo to install domain controller, and created a domain. With this installation and configuration, restarted the machine and then logged on as domain administrator. Noticed that SQL Services (MSSQLServer) is stopped. SQL Server Agent is stopped. Surely because earlier these were installed using local service account user. And now since those users are now part of CORP domain so authentication permission is invalid. I changed the logon permission but still it was not starting. Consistently getting this error in the event log.

Error: initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.).

Troubleshooting and Solution:

Looked to the error again with special attention, it is saying “Could not open error log file ”. ” Well… where is the error file, how will I know.

1. Start SQL Server Configuration manager.

2. Click to select ‘SQL Server Services’ from the left menu options.

3. On the right panel, right click on ‘SQL Server (MSSQLSERVER)’ and click ‘Properties’.

4. Click ‘Advanced’ tab.

5. Scroll down and copy the value stored for ‘Startup Parameters’. Paste these on notepate. It will be something like this (as I installed SQL Server on D: drive):

-dD:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf;-eD:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG;-lD:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.ldf

6. Notice the highlighted section. Browse to the location “D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog“.

7. Right click to open ‘Log’ Properties and then to visit to ‘Security’ tab. Now there are couple of user and group names in the top panel. For example, CREATED OWNER, SYSTEM, SQLServerMSSQLUsers$<Machine>$<SQLInstance>(<Domain>SQLServerMSSQLUsers$<Machine>$<SQLInstance>) and so on.

This SQLServerMSSQLUsers$<Machine>$<SQLInstance> has permission on the folder. And we need to make sure that our service account which is CORPsvcsqluser is part of this group.

8. Click Start -> Administrative Tools -> Active Directory Users and Computers.

9. Search and open group SQLServerMSSQLUsers$<Machine>$<SQLInstance>. Browse to ‘Members’ tab, and add ‘CORPsvcsqluser’ account in there.

10. Go back to SQL Server Configuration Properties window, and right click and start the service. It will work.

Additionally, there are other SQLServer groups too, for example, SQLServerReportServerUser, SQLServerSQLAgentUser, SQLServerMSASUser and so on… add corresponding service account to each of these groups under ‘Member’ tab.

This solution is specific to the scenario I have given. It may or may not be right solution for other scenarios.

Introduction

ALWAYS start SQL Server engine on Linux with the account mssql.

mssql@vps$ sudo systemctl start|stop mssql-server

NEVER RUN directly the binary sqlservr as root, for example :

root@vps$ /opt/mssql/bin/sqlsrvr

Indeed, the engine can not then restart normally with the user mssql.

mssql@vps$ sudo systemctl start mssql-server
          
    ● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2019-05-31 18:57:06 CEST; 4min 7s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
  Process: 15259 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=1/FAILURE)

Diagnosing and troubleshooting the issue

SQL Server creates hidden binary system files (*.hiv) in the directory /var/opt/mssql/.system/system. Their usage is not documented.

mssql@vps$ ls -lrt /var/opt/mssql/.system/system

-rw-r----- 1 mssql mssql  8192 May 31 18:44 licensing.hiv
-rw-rw---- 1 mssql mssql 49152 May 31 15:40 security.hiv
-rw-rw---- 1 mssql mssql 16384 May 31 15:40 lsa.hiv

When running inadvertently SQL Server as root, permissions are modified for these files :

mssql@vps$ ls -lrt /var/opt/mssql/.system/system

-rw-r----- 1 mssql mssql  8192 Jun  6 18:44 licensing.hiv
-rw-r----- 1 root  root  49152 Jun 18 18:59 security.hiv
-rw-r----- 1 root  root  16384 Jun 18 18:59 lsa.hiv

That’s why, at the next startup, SQL Server engine does not start, the user mssql is not allowed to read/write these *.hiv files.

The information is available in the crash dump files generated :

       Reason: 0x00000007
       Status: 0xc0000218
      Message: Cannot open or read the persistent registry: SystemRootsecurity.hiv.

By default crash dump files are located in the directory /var/opt/mssql/log/, otherwise in the directory specified with
the directive defaultdumpdir in the configuration file /var/opt/mssql/mssql.conf.

mssql@vps$ /opt/mssql/bin/mssql-conf get filelocation defaultdumpdir

defaultdumpdir : /opt/mssql/dba/srvmssql/crashdump

So, as root, reset the owner for these files to mssql :

root@vps$ cd /var/opt/mssql/.system/system

root@vps$ chown mssql:mssql security.hiv
root@vps$ chown mssql:mssql lsa.hiv

Unfortunately, the service mssql-server still does not start. Same issue than the one with the *.hiv files,
ensure that the owner of the SQL Server and SQL Server agent log files is not root (default directory /var/opt/mssql/log) :

-rw-r----- 1 root root 14357 May 31 19:50 errorlog
-rw-r----- 1 root root 6694 May 31 19:50 sqlagent.log

When attempting to recycle error log files, the error «Access is denied» is encountered by the engine and the engine shutdowns. To check that’s the case, as mssql :

mssql@vps$ /opt/mssql/bin/sqlservr

2019-05-31 21:04:22.58 Server      Error: 17058, Severity: 16, State: 1.
2019-05-31 21:04:22.58 Server      initerrlog: Could not open error log file '/opt/mssql/dba/srvmssql/log/errorlog'. Operating system error = 5(Access is denied.).
2019-05-31 21:04:22.88 Server      Error: 17058, Severity: 16, State: 1.
2019-05-31 21:04:22.88 Server      initerrlog: Could not open error log file '/opt/mssql/dba/srvmssql/log/errorlog'. Operating system error = 5(Access is denied.).

As root, modify the owner of the impacted error log files to mssql :

root@vps$ chown mssql:mssql errorlog

root@vps$ chown mssql:mssql sqlagent.log

Now the service mssql-server starts successfully :

mssql@vps$ sudo systemctl start mssql-server

mssql@vps$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; disabled; vendor preset: enabled)
   Active: active (running) since Fri 2019-05-31 22:33:28 CEST; 1min 31s ago

Conclusion

Is there any need of a conclusion ? Obvious conclusion : use root connections only when necessary and close these connections
as soon as they are not needed anymore.