Managing SQL Server 2019 service on Ubuntu with systemctl

Introduction

SQL Server 2019 is installed on Ubuntu 18.04. In the default architecture, SQL Server 2019 runs with the account mssql.

With the account mssql, the status of the service mssql-server is easily reported :

mssql@vps$ systemctl status mssql-server
          ● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2019-05-31 11:20:09 CEST; 36min ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1448 (sqlservr)
    Tasks: 182
   CGroup: /system.slice/mssql-server.service
           ├─1448 /opt/mssql/bin/sqlservr
           └─1460 /opt/mssql/bin/sqlservr

But when trying to stop/start/restart the service mssql-server with systemctl, the password is prompted.

mssql@vps$ systemctl stop mssql-server
          ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to stop 'mssql-server.service'.
Multiple identities can be used for authentication:
 1.  Ubuntu (ubuntu)
 2.  mssql
Choose identity to authenticate as (1-2):

mssql@vps$ sudo systemctl stop mssql-server
          [sudo] password for mssql:

We may want to allow mssql to manage its own service without password prompts.

2 solutions are available :

• Defining mssql-server as a user service with the account mssql.
• Granting rights with no password on the system service mssql-server to the user mssql through sudo.

Solution 1 : Defining mssql-server as a user service with the account mssql

By default the system service mssql-server is defined in the file /lib/systemd/system/mssql-server.service. It is a basic configuration for a service.

/lib/systemd/system/mssql-server.service

[Unit]
Description=Microsoft SQL Server Database Engine
After=network.target auditd.service
Documentation=https://docs.microsoft.com/en-us/sql/linux

[Service]
ExecStart=/opt/mssql/bin/sqlservr
User=mssql
WorkingDirectory=/var/opt/mssql

# Kill root process
KillMode=process

# Wait up to 30 minutes for service to start/stop
TimeoutSec=30min

# Remove process, file, thread limits
#
LimitNPROC=infinity
LimitNOFILE=infinity
TasksMax=infinity
UMask=007

# Restart on non-successful exits.
Restart=on-failure

# Don't restart if we've restarted more than 3 times in 2 minutes.
StartLimitInterval=120
StartLimitBurst=3

[Install]
WantedBy=multi-user.target

Instead of using a system service, a switch to a user service as user mssql is performed.

The system service mssql-server is first stopped and disabled as root :

root@vps$ systemctl stop mssql-server
          
root@vps$ systemctl disable mssql-server
          
root@vps$ systemctl daemon-reload

As mssql, the file /lib/systemd/system/mssql-server.service is copied in the directory $HOME/.config/systemd/user (service user directory).

mssql@vps$ mkdir -p  $HOME/.config/systemd/user

mssql@vps$ cp /lib/systemd/system/mssql-server.service $HOME/.config/systemd/user

In this copied file, 2 updates :

$HOME/.config/systemd/user/mssql-server.service

…
[Service]
# User=mssql
…
…
[Install]
WantedBy=default.target

The directive User=mssql is commented out, if not, an error message is raised about the group during the fork.

  Process: 1904 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=216/GROUP)
 Main PID: 1904 (code=exited, status=216/GROUP)

If we want this user service to start at boot, the directive WantedBy must be set to default.target and not multi-user.target, the last one is only effective for a system service at boot time.

At this stage, the user service can be managed by mssql, without password prompts, systemctl is invoked with the option --user :

mssql@vps$ systemctl --user daemon-reload
          
mssql@vps$ systemctl --user status mssql-server
          ● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/home/mssql/.config/systemd/user/mssql-server.service; disabled; vendor preset: enabled)
   Active: inactive (dead)

mssql@vps$ systemctl --user start|stop|restart mssql-server

Last step, enable the user service mssql-server at boot time :

mssql@vps$ systemctl --user enable mssql-server

Don’t forget to allow the user mssql to run services when it is not logged in, to do so, as root :

root@vps$ loginctl enable-linger mssql

Solution 2 : Granting rights with no password on the system service mssql-server to the user mssql through sudo

If you want to keep the service mssql-server as a system one, edit the file /etc/sudoers with visudo.

root@vps$ visudo

Add the following authorizations for the user mssql in this file :

Cmnd_Alias MSSQL_CMDS = /bin/systemctl start mssql-server, /bin/systemctl stop mssql-server
mssql ALL=(root) NOPASSWD: MSSQL_CMDS

Use the full path /bin/systemctl, it is recommended in the sudoers fils.

The user mssql can now manage the system service mssql-server without being prompted for the password :

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

Conclusion

To manage the service mssql-server with the account mssql, two easy solutions. It depends essentially on the preferred way or any enterprise and security constraints.