Troubleshooting a startup issue of SQL Server 2019 on Ubuntu linked to user root action error

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: \SystemRoot\security.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.