Databases Azure

MonetDB on Ubuntu 24.04 on Azure User Guide

Last updated: 2026-09-20 | Product: MonetDB 11.55.7 on Ubuntu 24.04 LTS on Azure

Overview

MonetDB is an open source relational database built for analytics. A traditional row store reads whole records off disk even when your query only mentions two columns; MonetDB stores each column separately and touches only the ones you ask for. That is why aggregations, grouping and window functions over large tables run so much faster on the same hardware.

It speaks standard SQL, so your existing queries, tools and skills carry straight over, and it ships with mclient (an interactive SQL terminal), msqldump (a SQL dump utility), and client libraries for Python, Java, R, PHP, Ruby, ODBC and JDBC.

The cloudimg image delivers MonetDB 11.55.7 (Dec2025-SP3) on Ubuntu 24.04, served over TLS, with a unique database password generated on the first boot of your VM. Backed by 24/7 cloudimg support.

What is included:

MonetDB 11.55.7 (Dec2025-SP3) from the official MonetDB apt repository, pinned and verified against the vendor's signing key
The MAPI/SQL port served over TLS on port 50000, with a certificate generated for your VM at first boot
A durable database area at /var/monetdb5/dbfarm, owned by the unprivileged monetdb service account, which survives reboots
A per-VM database password written to /root/monetdb-credentials.txt, readable only by root
On-VM self-tests that prove the security posture at any time

A note on TLS

MonetDB does not implement TLS itself — upstream's own guidance is to put a TLS termination proxy in front of it, and that is exactly what this image does. The database listens only on 127.0.0.1:50001, and stunnel terminates TLS on port 50000, the port MonetDB clients expect. The practical consequence for you is that you connect with a monetdbs:// URL rather than monetdb://, and the examples below show exactly how.

A note on the default credential

MonetDB documents that every newly created database gets the user monetdb with the password monetdb. This image never creates that pair. No database exists in the image at all; on your VM's first boot a database is created with a password generated for that instance, set at the moment of creation rather than changed afterwards.

Prerequisites

An Azure subscription
SSH access to the VM (port 22), plus port 50000 from wherever you will run SQL
The MonetDB client (mclient) on your workstation if you want to connect remotely, or just use the one already on the VM

Step 1: Deploy from the Azure Marketplace

Search the Azure Marketplace for MonetDB on Ubuntu 24.04 LTS by cloudimg, choose Standard_B2ms or larger, and allow inbound 22 and 50000.

MonetDB is developed from a main-memory perspective, so RAM is the resource that matters most. Standard_B2ms (2 vCPU, 8 GiB) is a sensible starting point; move up the memory-optimised sizes as your working set grows.

Step 2: Deploy from the Azure CLI

az vm create \
  --resource-group my-resource-group \
  --name my-monetdb \
  --image cloudimg:monetdb-ubuntu-24-04:default:latest \
  --size Standard_B2ms \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

Then open the SQL port to the address range you will connect from:

az vm open-port --resource-group my-resource-group --name my-monetdb --port 50000 --priority 1001

Step 3: Retrieve this instance's database password

Every VM launched from this image generates its own credentials on first boot and writes them to a root-only file. Nothing is shared between deployments.

sudo cat /root/monetdb-credentials.txt

You will see the database user, its password, the database name, this VM's public address, and the TLS certificate fingerprint — you need that fingerprint to connect from another machine, because the certificate is generated on your VM and so is not signed by a public certificate authority.

The file also carries MONETDBD_PASSPHRASE. That is the passphrase for the monetdbd administrative control channel, which can create and destroy databases. Remote control of that channel is disabled on this image (control=no), so the passphrase is not usable over the network as shipped — it is generated per instance so that if you ever do enable remote control, you are not inheriting a secret shared with every other deployment of this image.

MonetDB per-VM credentials file

Step 4: Confirm the database is running

systemctl is-active monetdbd monetdb-tls
mserver5 --version | head -1
sudo -u monetdb monetdb status

Both units report active, the server reports 11.55.7 (Dec2025-SP3), and the demo database shows state R for running.

Check what is reachable from outside the VM:

sudo ss -Hltn | awk '{print $4}' | sort -u

Only 22 and 50000 are bound to a public address. MonetDB itself is on 127.0.0.1:50001, behind the TLS proxy.

MonetDB service status and listening sockets

Step 5: Connect with mclient

mclient has no --password option — the only non-interactive way to supply a password is a small configuration file, which MonetDB calls a dotfile. The image already ships one for the root user at /root/.monetdb, so on the VM itself you can simply run:

sudo mclient -d demo -s "SELECT 'connected' AS status, current_user AS whoami;"

To go through the TLS front door instead of the loopback socket — which is what a remote client does — build the monetdbs:// URL from the credentials file:

sudo bash -c '
set -u
CREDS=/root/monetdb-credentials.txt
CH=$(grep "^MONETDB_TLS_CERTHASH=" "$CREDS" | cut -d= -f2-)
PW=$(grep "^MONETDB_PASSWORD=" "$CREDS" | cut -d= -f2-)
T=$(mktemp -d); umask 077
printf "user=monetdb\npassword=%s\n" "$PW" > "$T/dot"
DOTMONETDBFILE="$T/dot" mclient \
  -d "monetdbs://127.0.0.1:50000/demo?certhash=sha256:$CH" \
  -s "SELECT 11.55 AS version, current_user AS whoami;"
rm -rf "$T"
'

Step 6: Create a table and load data

MonetDB uses standard SQL for schema definition:

sudo mclient -d demo -s "
DROP TABLE IF EXISTS quarterly_sales;
CREATE TABLE quarterly_sales (
  id       INTEGER,
  sold_at  DATE,
  region   VARCHAR(32),
  product  VARCHAR(64),
  qty      INTEGER,
  amount   DECIMAL(12,2)
);"

COPY INTO is MonetDB's bulk loader and is dramatically faster than individual INSERT statements. Reading from STDIN keeps everything in one statement:

sudo mclient -d demo -s "
COPY 6 RECORDS INTO quarterly_sales FROM STDIN USING DELIMITERS ',', E'\n';
1,2026-01-15,EMEA,widget,10,199.50
2,2026-01-16,AMER,widget,4,79.80
3,2026-01-16,EMEA,gadget,7,349.00
4,2026-02-02,APAC,widget,12,239.40
5,2026-02-11,EMEA,gadget,3,149.55
6,2026-02-19,AMER,sprocket,20,880.00
"

Confirm the rows really landed:

sudo mclient -d demo -s "SELECT COUNT(*) AS rows_loaded FROM quarterly_sales;"

To load from a file on the VM instead, give an absolute path the monetdb service account can read and use COPY INTO ... FROM '/path/to/data.csv'. To load a file that lives on your machine rather than the server, add ON CLIENT and mclient will read it for you.

Step 7: Run an analytic query

This is what the column store is for — an aggregation with a window function over it:

sudo mclient -d demo -s "
SELECT region,
       SUM(amount)                             AS revenue,
       SUM(qty)                                AS units,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM   quarterly_sales
GROUP  BY region
ORDER  BY revenue DESC;"

EMEA totals 698.05, AMER 959.80 and APAC 239.40. Because each column is stored separately, this query reads only region, qty and amount — never the whole row.

MonetDB columnar analytics query

MonetDB can also show you how long the server spent on a query, which is useful when tuning:

sudo mclient -d demo -t performance -s "SELECT region, SUM(amount) FROM quarterly_sales GROUP BY region;"

Step 8: Connect from your workstation

From another machine, use the monetdbs:// URL and the certificate fingerprint from Step 3. Replace the address and fingerprint with the values from your own VM:

mclient -d "monetdbs://<vm-ip>:50000/demo?certhash=sha256:<MONETDB_TLS_CERTHASH>" \
        -s "SELECT COUNT(*) FROM quarterly_sales;"

mclient will prompt for the password; it is <MONETDB_PASSWORD> from the credentials file. To avoid the prompt, put user= and password= lines in a .monetdb file in your working directory or your home directory.

The fingerprint is needed because the certificate is generated on your VM. If you install a certificate from a public certificate authority (Step 11), drop the ?certhash= parameter entirely and the ordinary verified TLS path applies.

From Python

The pymonetdb driver speaks the same TLS URL:

import pymonetdb
conn = pymonetdb.connect(
    "monetdbs://<MONETDB_PUBLIC_IP>:50000/demo?certhash=sha256:<MONETDB_TLS_CERTHASH>",
    username="monetdb", password="<MONETDB_PASSWORD>")
cur = conn.cursor()
cur.execute("SELECT region, SUM(amount) FROM quarterly_sales GROUP BY region")
print(cur.fetchall())

Step 9: Verify the security posture

The image ships self-tests you can re-run at any time. Each individual check is itself proven against a known-bad input first, so a check that always passes cannot go unnoticed.

sudo /usr/local/sbin/monetdb-selftest.sh

Individually:

sudo /usr/local/sbin/monetdb-port-check.sh
sudo /usr/local/sbin/monetdb-daemon-check.sh
sudo /usr/local/sbin/monetdb-verify-auth.sh /root/monetdb-credentials.txt

monetdb-verify-auth.sh confirms that the published monetdb/monetdb pair is refused, that blank and weak passwords are refused, that this VM's own password returns real rows over TLS, and that both a plaintext connection and an untrusted certificate are rejected.

MonetDB security posture self-tests

The daemon's administrative control channel — which can create and destroy databases — is disabled over the network and exists only as a local socket. You can confirm that directly:

sudo -u monetdb monetdbd get control,discovery,listenaddr,port /var/monetdb5/dbfarm

Step 10: Back up and restore

msqldump writes a complete SQL dump of a database:

sudo bash -c 'msqldump -d demo > /var/backups/demo-$(date +%F).sql; ls -lh /var/backups/demo-*.sql'

Restore by feeding a dump back to mclient. Point it at whichever dump you want to restore:

sudo mclient -d demo <backup-dir>/demo-2026-09-20.sql

For a physical backup, stop the database and copy its directory out of the database area:

sudo bash -c 'monetdb_dir=/var/monetdb5/dbfarm; du -sh "$monetdb_dir"/demo; echo "stop the database with: sudo -u monetdb monetdb stop demo"'

Step 11: Use your own TLS certificate

The image generates a self-signed certificate for your VM at first boot, which is why remote clients need the fingerprint. If you have a certificate from a public certificate authority, install it and restart the TLS front door — then clients connect with a plain monetdbs:// URL and no fingerprint at all.

# Replace the per-VM pair with your own certificate and key, then restart.
sudo install -m 0644 -o root -g root \
  /etc/letsencrypt/live/<your-domain>/fullchain.pem /etc/monetdb/tls/server.crt
sudo install -m 0640 -o root -g stunnel4 \
  /etc/letsencrypt/live/<your-domain>/privkey.pem /etc/monetdb/tls/server.key
sudo systemctl restart monetdb-tls.service

Step 12: Rotate the database password

A password change must refuse a weak value. This block proves that refusal with a known-bad password first, then performs a real rotation and updates both the credentials file and the root dotfile so the VM stays consistent.

sudo bash -c '
set -u
CREDS=/root/monetdb-credentials.txt
DOTFILE=/root/.monetdb
OLDPW=$(grep "^MONETDB_PASSWORD=" "$CREDS" | cut -d= -f2-)

reject() {
  case "$1" in
    ""|monetdb|password|admin|changeme) return 0 ;;
  esac
  [ ${#1} -lt 16 ] && return 0
  return 1
}

# 1. A weak value must be REFUSED, not quietly accepted.
for candidate in "" "monetdb" "password"; do
  if reject "$candidate"; then
    echo "REFUSED a weak password as expected"
  else
    echo "PROBLEM: a weak password was accepted"; exit 1
  fi
done

# 2. A real rotation with a strong value.
NEWPW=$(openssl rand -base64 33 | tr -d "\n/+=" | cut -c1-32)
if reject "$NEWPW"; then echo "PROBLEM: generated password judged weak"; exit 1; fi

T=$(mktemp -d); umask 077
printf "user=monetdb\npassword=%s\ndatabase=demo\nhost=localhost\nport=50001\n" "$OLDPW" > "$T/dot"
DOTMONETDBFILE="$T/dot" mclient -d demo \
  -s "ALTER USER SET PASSWORD '"'"'$NEWPW'"'"' USING OLD PASSWORD '"'"'$OLDPW'"'"';"

# 3. Record the new password so the VM stays consistent.
sed -i "s|^MONETDB_PASSWORD=.*|MONETDB_PASSWORD=$NEWPW|" "$CREDS"
sed -i "s|^password=.*|password=$NEWPW|" "$DOTFILE"
rm -rf "$T"

# 4. Prove the new credential works and the old one no longer does.
sudo mclient -d demo -s "SELECT '"'"'rotated'"'"' AS status;"
echo "password rotation complete"
'

Support

cloudimg provides 24/7 support for this image: deployment, upgrades, TLS certificates and custom domains, bulk data loading, schema design, query tuning and scaling.

MonetDB is a trademark of its respective owner. All product and company names are trademarks or registered trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.