On this page
BroadSQL as a Linux service
BroadSQL normally runs in a terminal: you start it, you type the master password, you work, you type EXIT. On a server there is nobody to do that. Service mode runs BroadSQL under systemd instead: it starts at boot, with no terminal and no person, unlocks its Connections Definition File from a protected file on that machine, and makes the web interface available until the service is stopped.
This is for one BroadSQL installation on one machine, reached by people through their browser. It is not a multi user server product, and it does not change anything about the BroadSQL you run in a terminal: the same installation works both ways.
Only Ubuntu and other systemd distributions on Linux x64 are supported. Windows has no service mode.
What you need
- A Linux x64 machine with
systemd(Ubuntu 22.04 or later, Debian 12 or later, and anything comparable). - The BroadSQL Linux package, extracted into a folder of its own, for example
/opt/broadsql. See Installation. - An existing Connections Definition File in that installation's
conffolder, and its master password. A service never creates a Connections Definition File: if the file is missing, the service stops and says so, rather than starting an empty new one. - At least one ShaWeR user, so that somebody can sign in through the browser. See ShaWeR.
- For access from another machine: Nginx in front of BroadSQL, with HTTPS. See Reaching ShaWeR from another computer.
Install it
Extract the package, then from the installation folder:
sudo ./service/install-service.sh
It asks for the master password once, without showing it, and does six things: it creates an unprivileged account broadsql with a group of its own, writes the master password to /etc/broadsql/master-password owned by root and readable by root only, shares the installation with that group (read for all of it, write for the three folders the service writes), fills in the unit template, installs it as /etc/systemd/system/broadsql.service and reloads systemd.
It does not start the service: that is your decision. sudo ./service/install-service.sh --dry-run prints what it would do and changes nothing.
Options: --user <name> for another account, --group <name> for another shared group, --secret <file> for another location of the master password, --unit <name> for another unit name.
What it changes on your files, and what it leaves alone
Your installation keeps its owner. Whoever installed BroadSQL still owns every file of it, the launchers, the libraries, the bundled Java runtime, the drivers, the extensions, the samples and the configuration included. The service account is given what it needs through the group, and nothing more:
| Folder | The service account gets | Why |
|---|---|---|
| the installation folder, and everything in it | read, and the right to enter folders | it reads the launcher, the Java runtime, the libraries, the drivers and the extensions |
conf/ | read and write | the Connections Definition File, the settings file (it saves the Instance ID, with a lock and a backup beside it), the service lock file, the licences. Closed to every other account |
logs/ | read and write | the application and activity logs |
shawer/ | read and write | the private workspace of each signed-in ShaWeR user. Created if it is missing |
lib/, runtime/, drivers/, extensions/, samples/, scripts/ and the launchers stay read only to the service, and the unit grants exactly the same three folders at the systemd level (ReadWritePaths=), so a permission alone would not be enough to write anything else. The three folders carry the setgid bit, so a file the service creates in them stays shared with that group instead of becoming private to the service account.
If you keep your scripts, your exports or your history somewhere else (ScriptsLibrary, scripthistoryvault, DefaultFolder, an absolute LogFolderName), give that folder to the group yourself and add it to ReadWritePaths= in /etc/systemd/system/broadsql.service: the installer never changes a folder outside the installation.
Until 5.6.0 the installer gave the whole installation to the service account (chown -R), which took it away from the account that had installed it. If that happened to you, give it back with sudo chown -R <your account> /opt/broadsql, then run the installer again: it keeps whatever owner it finds.
Before the first start
Check that the installation has a ShaWeR user:
sudo -u broadsql /opt/broadsql/broadsql.sh
Then, at the BroadSQL prompt, SHAWER USER ADD alice; and EXIT;. Two-factor authentication is strongly advised for a machine reachable from the internet: SHAWER USER TOTP alice;.
Install it by hand instead
If you prefer to see every step, the unit is a template with three placeholders:
sed -e 's|@INSTALL_DIR@|/opt/broadsql|g' \
-e 's|@SERVICE_USER@|broadsql|g' \
-e 's|@SECRET_FILE@|/etc/broadsql/master-password|g' \
service/broadsql.service | sudo tee /etc/systemd/system/broadsql.service >/dev/null
sudo install -d -m 0700 -o root -g root /etc/broadsql
sudo systemd-ask-password --echo=no "BroadSQL master password:" | sudo tee /etc/broadsql/master-password >/dev/null
sudo chmod 0400 /etc/broadsql/master-password
sudo groupadd --system broadsql
sudo useradd --system --no-create-home --shell /usr/sbin/nologin --gid broadsql broadsql
sudo chgrp -R broadsql /opt/broadsql
sudo chmod -R g+rX /opt/broadsql
sudo install -d -g broadsql -m 2770 /opt/broadsql/logs /opt/broadsql/shawer
sudo chmod -R g+w /opt/broadsql/conf /opt/broadsql/logs /opt/broadsql/shawer
sudo chmod g+s /opt/broadsql/conf /opt/broadsql/logs /opt/broadsql/shawer
sudo chmod o-rwx /opt/broadsql/conf
sudo systemctl daemon-reload
The ownership of the installation is deliberately left alone: the group gets read everywhere and write in those three folders, which is everything the service needs.
Run it
sudo systemctl enable --now broadsql start it now, and at every boot
sudo systemctl status broadsql is it running
sudo systemctl restart broadsql
sudo systemctl stop broadsql
journalctl -u broadsql -f what it is saying, as it says it
A start that worked ends with these lines in the journal:
Connections Definition File unlocked.
Service startup command: SHAWER START;
ShaWeR started.
Address: http://127.0.0.1:8080/C6MVER39XDZS9KT7/
BroadSQL service is ready. Stop it with: systemctl stop broadsql
The address holds this installation's Instance ID, which does not change when the service restarts, so Nginx can be pointed at it once and a bookmark keeps working.
The startup option
Service mode is one startup option of BroadSQL itself, and it is what the unit's ExecStart runs:
./broadsql.sh --service the documented form, used by the unit
./broadsql.sh -service the same option, written with one hyphen
Both forms mean exactly the same thing, and nothing else does: an option BroadSQL does not know stops the start with a message naming it, instead of starting the interactive console that a service has no terminal for.
Typed by hand in a terminal, --service gets as far as the master password and stops: the credential belongs to systemd, which provides it only to the service it starts itself. Use systemctl start broadsql to start the service.
Without the option nothing changes: ./broadsql.sh opens the console, and ./broadsql.sh WORLD opens that connection. An argument that starts with a hyphen is an option and is given to BroadSQL as it was typed; an argument that does not is the name of the connection to open. A connection name therefore never starts with a hyphen.
Upgrading an installation that runs as a service
Extract the package of the new release into the installation folder, so that broadsql.sh, the settings template and lib/broadsql.jar all come from the same release. Replacing lib/broadsql.jar alone leaves the older broadsql.sh behind, and an older script passes the command line in a form the current BroadSQL no longer accepts: it reported the first argument as the name of a connection whatever it was, so --service arrived as the name of a connection called --service.
BroadSQL says so rather than starting in a way nobody asked for:
Error: -to=--service is not a connection name: a connection name does not start with a hyphen.
If journalctl -u broadsql shows that line, the installation still has the older broadsql.sh. Stop the service, extract the package again over the installation folder, then sudo systemctl start broadsql.
What the process returns
| Exit code | What it means |
|---|---|
| 0 | The service ran, and was stopped (systemctl stop broadsql). |
| 64 | The command line was not understood: an unknown option, or an option used with or without a value when it needs the opposite. |
| 69 | The service could not start: another service already holds this installation, a startup command failed, or its port is taken. |
| 77 | The master password credential is missing, unreadable, readable by other accounts, or not this file's password. |
| 78 | The configuration is wrong: no Connections Definition File is configured, or the one configured is not there. |
A start that fails never returns 0, so Restart=on-failure and systemctl status broadsql both see it for what it was. A start refused for a configuration reason is not retried: trying again would only repeat it.
What the service starts
By default, the web runtime and nothing else: SHAWER START;. ShaWeR then answers, but BroadSQL Desk is not open: a browser sent to Desk's address gets the notice BroadSQL Desk is not open. In the BroadSQL console, type DESK to open it. instead of the application.
To have BroadSQL Desk available whenever the service runs, set both commands in conf/broadsql.ini:
ServiceStartupCommands=SHAWER START;DESK;
then restart the service:
sudo systemctl restart broadsql
BroadSQL Desk is then reachable at its configured address, and the journal shows one Service startup command: line for each of the two commands. This is the configuration verified on a real server.
ServiceStartupCommands holds ordinary BroadSQL commands, exactly as you would type them in the console, separated by ;, and they run in the order given. Any extension is started the same way, by its own command.
ServiceStartupCommands=DESK; on its own also works, because DESK starts ShaWeR itself when it is not already running: neither of the two commands is technically required by the other. Writing both is still the clearer form, because the settings file and the journal then show the whole startup sequence.
If a startup command fails, the service stops instead of running half started, and the journal says which command and why.
The master password
The password is handed to the service by systemd, through the LoadCredential= line of the unit:
LoadCredential=broadsql-master-password:/etc/broadsql/master-password
At each start systemd copies that file into a private folder of the service, in memory, owned by the service account and readable by nobody else, and removes it when the service stops. BroadSQL reads it from there and unlocks the Connections Definition File with it.
So the password is not in broadsql.ini, not in the unit's environment, not in the command line, not in your shell history, not in the journal and not in any file the package or the website contains. The file on disk is owned by root with mode 0400; BroadSQL refuses to start if another account can read it.
Two settings in conf/broadsql.ini exist for the cases where the unit is not the standard one. They hold the name and location of the file, never a password, and both are checked as strictly as the credential systemd provides: ServiceMasterPasswordCredential=<name> when the unit's LoadCredential= uses another name than broadsql-master-password, and ServiceMasterPasswordFile=/absolute/path to read a file directly, on a machine without systemd. When a start names one of them in its message, that is the setting to correct.
What this protects, and what it does not. It protects against the ordinary ways a password escapes: a configuration file that gets copied or committed, a process list, a log, a backup of an installation folder. It does not protect against somebody who is already root on the machine, or who is the service account, or who can read the memory of the running process: such a person has the master password, and no scheme that stores a secret on one machine can change that.
Keep the file encrypted at rest
If /etc of this machine is backed up somewhere less protected, encrypt the credential to this machine instead:
sudo systemd-creds encrypt --name=broadsql-master-password - /etc/broadsql/master-password.cred
Type the password, end with Ctrl D, then replace the LoadCredential= line of the unit with:
LoadCredentialEncrypted=broadsql-master-password:/etc/broadsql/master-password.cred
and remove the plain file. The encrypted file is useless on any other machine, which is the point, and also the limit: restoring it on new hardware means provisioning the password again.
Change the master password
sudo systemctl stop broadsql
sudo -u broadsql /opt/broadsql/broadsql.sh then SET MASTER PASSWORD; and EXIT;
sudo systemd-ask-password --echo=no "BroadSQL master password:" | sudo tee /etc/broadsql/master-password >/dev/null
sudo chmod 0400 /etc/broadsql/master-password
sudo systemctl start broadsql
Change the Connections Definition File first and the credential second, in that order: between the two the service cannot start, and starting it would only report that the credential is wrong.
If the credential is lost
The Connections Definition File is encrypted with the master password, and a lost master password cannot be recovered: the connections in that file are lost with it. Keep the master password where you keep your other important passwords, and keep a backup of conf/. The credential file is a copy of the password for this machine, never the only one.
Keeping your existing installation
An installation you have been using in a terminal becomes a service without being converted: the same folder, the same conf/broadsql.ini, the same Connections Definition File, the same ShaWeR users and two-factor secrets, the same Desk licence, the same Instance ID and therefore the same address.
Two things change, and both are permissions:
- the installation is shared with the group of the service account: it keeps its owner, so you still own your files, and that account can read all of it and write
conf/,logs/andshawer/. Run BroadSQL in a terminal as the service account (sudo -u broadsql /opt/broadsql/broadsql.sh) when you want to work on the files it writes, so that what you create there stays usable by the service; - the master password is now also in
/etc/broadsql/master-password.
One service per installation. A second service on the same installation is refused: the first one holds conf/broadsql-service.lock, and two of them would fight over the same Connections Definition File and the same port. Running BroadSQL in a terminal while the service runs is allowed, as it always was.
What a service does not do
- It opens no database connection. Starting a service is not signing in: nobody is given a database session by it, and
RememberLastConnectionis ignored. People connect from the browser after they have signed in. - It prints no sign-in link. The one-time links that
DESKopens in a browser need a console, and a link in a journal would be a password in a log. They are off in service mode whateverShawerLaunchLinkssays. - It opens no browser, whatever
WebOpenBrowsersays. - It bypasses nothing. Signing in through the browser needs a ShaWeR user, a password and, where it is configured, a two-factor code, exactly as on a desktop. The web interface being available is not an authorization.
Troubleshooting
| What the journal says | What it means |
|---|---|
[Enter password] and then No password entered (end of input), exiting application | BroadSQL started as a console, not as a service, so the --service option did not reach it. Check ExecStart in /etc/systemd/system/broadsql.service, and that the installation is 5.6.0 or later. |
is not a connection name: a connection name does not start with a hyphen | The installation's broadsql.sh is older than its lib/broadsql.jar: it gave BroadSQL the service option as the name of a connection. Extract the package of the release again over the installation folder, as described above. |
is not a BroadSQL option | A startup option in ExecStart is mistyped. The service option is --service, or -service. |
the credential ... does not exist | The unit's LoadCredential= names a file that is not there, or the service was started without systemd. Check the path in /etc/systemd/system/broadsql.service. |
the credential ... can be reached by other accounts | The mode of the file is too open: sudo chmod 0400 /etc/broadsql/master-password. |
the credential is not the master password of ... | The file holds the wrong password, or a trailing character you did not intend. Write it again with systemd-ask-password. |
the Connections Definition File ... does not exist | ServersFileName in conf/broadsql.ini points somewhere else than your file. Nothing was created. |
... is not writable by the service account | conf/ is not writable by the service: sudo chgrp -R broadsql /opt/broadsql/conf, sudo chmod -R g+w /opt/broadsql/conf, sudo chmod g+s /opt/broadsql/conf. Running the installer again does the same thing. |
another BroadSQL service is already running on this installation | A service is already up. sudo systemctl status broadsql. |
port 8080 is already used by another program | Another program has the port. Change ShawerPort in conf/broadsql.ini, or stop that program. |
No ShaWeR user exists | Nobody can sign in yet: create one as described above. |
Failed to start broadsql.service with nothing else | journalctl -u broadsql -n 50 shows the reason; a start refused for a configuration reason is not retried, by design. |
A service that stops on its own and does not come back is a service that was refused for a reason it cannot fix by trying again. Read the journal, correct the reason, then sudo systemctl start broadsql.
More
- ShaWeR: sign-in, users, two-factor authentication, the address, Nginx and the settings.
- BroadSQL Desk: the browser interface and its licence.
- Installation and Application settings.
Found something wrong or unclear on this page? Tell us, with the page address.