---
title: "Choosing a database, creating it, encrypting the link and leaving the built-in one"
url: https://www.hmailserver.co.uk/topic/28228/choosing-a-database-creating-it-encrypting-the-link-and-leaving-the-built-in-one
author: "Progressiverobot"
published: 2026-09-27T19:10:13.731Z
updated: 2026-09-27T19:10:13.731Z
category: "Installation, migration & upgrades"
tags: ["database","postgresql","mariadb","migration","tls"]
site: hMailServer Community Forum
---

# Choosing a database, creating it, encrypting the link and leaving the built-in one

*Written for 6.3.4. hMailServer 6.3.7 (17 October 2026) adds no schema step (6108, as 6.3.6). Its x64 installer's MySQL and MariaDB client is MariaDB Connector/C 3.4.11, built from MariaDB's source, in place of the 3.4.9 that 6.2.3 to 6.3.6 carried (CVE-2026-107819), with seven authentication plugins where 6.3.6's had nine: a database account that signs in with MySQL Enterprise's Windows authentication can no longer sign in, and the server then cannot open its database; the ARM64 installer carries no MySQL or MariaDB client. On MySQL, MariaDB, SQL Server and SQL Server Compact an insert that forgives a duplicate key now forgives that alone and any other failure is reported (HM5032), and on MySQL and MariaDB a lost connection is reconnected and the statement run again, so an error log that was quiet may now name failures that were hidden. See [the 6.3.7 announcement](/topic/28277). hMailServer 6.3.8 (2 November 2026), a security release, adds no schema step either. Through MariaDB Connector/C, the client the Windows installer bundles and the Linux server loads, `TlsMode=verify-full` without `TlsCaFile` to a MySQL or MariaDB server on this machine (by a loopback address, `localhost`, a Unix socket or a named pipe, or through a `Server=` list that holds one) is refused with HM7535, since in 6.3.4 to 6.3.7, with Connector/C 3.4.1 or later, neither the certificate's issuer nor its name was checked there: before upgrading, name in `TlsCaFile` a CA that issues nothing but the database's certificates, the one that issued its certificate, or set `TlsMode=require`. A `TlsCaFile` whose name holds a character outside ASCII, which in 6.3.4 to 6.3.7 could reach the client as no CA file at all, is now handed to it as named; on Windows a `TlsCaFile` or `PostgreSQLSslRootCert` name the client cannot be handed as named is refused with HM7534, so give the file, and the folders above it, an ASCII name; and on Linux, where the distribution's client is older than Connector/C 3.4.11 and not patched, name the CA in `TlsCaFile`. See [the 6.3.8 announcement](/topic/28278).*

This topic covers the four databases hMailServer 6.3.4 can keep its data in, what each needs before the schema can be made, how the connection to the database server is encrypted since 6.3.4, and how to move an installation off the built-in engine. The one thing to know first: **on MySQL and MariaDB, 6.3.4 needs MySQL 5.7.9 or MariaDB 10.2.2 or later**, because since schema 6102 every table is `utf8mb4`. On an older server the create script stops at its first table, and an upgrade from 6.3.3 stops at its first step, 6040 to 6041, with error 1071 and nothing changed.

### What the database holds, and what it does not

hMailServer stores accounts, domains, message *metadata* and settings in the database. The message bodies are files on disk, one per message, at `<DataFolder>\<domain>\<local part>\<xx>\{guid}.eml`, so the database stays small relative to `Data\`. Since 6.3.4 the database is also the settings store: the `[Settings]` keys of `hMailServer.ini` are rows from schema 6042, the file's section is the server's copy, and a line edited there for a key the database holds is named in the error log as `HM5804` and does not take effect. Where the service may write the file the line is put back; where it may not (a new Windows installation under `NT SERVICE\hMailServer`, a Linux package installation) the copy is not refreshed, the edited line stays, and the database's value is used. The `[Database]` section itself stays in the file, because it says how the store is reached.

### The four choices

| Database | `[Database] Type` | What is bundled | What you must have first |
|---|---|---|---|
| Built-in (SQL Server Compact 4.0 SP1) | `MSSQLCE` | `SSCERuntime_x64-ENU.msi`, installed by setup | Nothing. This is the default |
| MySQL / MariaDB | `MYSQL` | MariaDB Connector/C 3.4.9 as `Bin\libmysql.dll` (3.4.11 since 6.3.7, with seven authentication plugins where 6.3.6's had nine) | A reachable server, MySQL 5.7.9 or MariaDB 10.2.2 or later, an empty database, and a user that may create tables |
| Microsoft SQL Server | `MSSQL` | Nothing; the OS's own data stack | The same, including Express edition |
| PostgreSQL | `PostgreSQL` | `libpq` from PostgreSQL 18.3 | The same |

**On Linux** there is no built-in database: a Linux server keeps its data in PostgreSQL or MySQL/MariaDB, and `hmailserver --create-database` refuses SQL Server and SQL Server Compact by name. For MySQL or MariaDB install the client library yourself, because the server opens `libmariadb.so.3` at run time: `libmariadb3` on Debian and Ubuntu, `mariadb-connector-c` on Fedora and RHEL. [Databases on Linux](/topic/28247) has the steps for each backend, and [First install on Linux](/topic/28190) the order of the whole first install.

**Two things about the built-in engine.** It runs on a single database connection: `NumberOfConnections` is forced to 1 for `MSSQLCE`. And Microsoft has withdrawn the SQL Server Compact download; the copy in `installation/SQLCE/` is the archive of record, and it still installs. [MigratingDatabaseBackend.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/MigratingDatabaseBackend.md) calls it "the one real dependency liability in this tree". For anything up to a few dozen mailboxes it is adequate, and the last section of this topic is the way off it.

### How the four behave differently

| | MS SQL Server | PostgreSQL | MySQL / MariaDB | SQL Server Compact |
|---|---|---|---|---|
| DDL in a transaction | Mostly | Yes, the only one of the four | No, commits as it goes | No, commits as it goes |
| A failed schema step leaves | Normally nothing applied | Nothing applied | A partly built database | A partly built database |
| Isolation the server uses | READ UNCOMMITTED | its default | its default | its default |
| Transactions used at all | yes | yes | only if every table reports InnoDB at connect time | asked for, but a no-op: `SQLCEConnection::BeginTransaction` returns true without starting one ([Roadmap.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/Roadmap.md)) |
| Choose it when | You already run SQL Server | You want a failed schema step to roll back cleanly | You already run it | As a new installation's default, up to a few dozen mailboxes; as the target of a migration, only for a test rig |

### MySQL and MariaDB since 6.3.4

**MySQL 5.7.9 or MariaDB 10.2.2, or later.** Since schema 6102 every table is `utf8mb4` under `utf8mb4_general_ci`, InnoDB, with `ROW_FORMAT=DYNAMIC`, and a key over a `varchar(255)` is then 1020 bytes: more than the 767 bytes an InnoDB key part may be in the older COMPACT rows, and within the 3072 bytes these releases, as they ship, take.

**Why.** MySQL's `utf8` is three bytes a character, so an emoji in a contact, a folder or a signature was refused with error 1366 under strict mode and made a question mark without it. `utf8mb4_general_ci` is the twin of `utf8_general_ci`, so an address or a folder compares as it did, but it does not tell characters outside the Basic Multilingual Plane apart: two address books called `Family 👪` and `Family 🎉` are one name to its unique index.

**Creating the database yourself:**

```sql
CREATE DATABASE hmailserver CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES ON hmailserver.* TO 'hmailserver'@'%';
```

The 6102 step also changes the database's own default character set, which needs the ALTER privilege on the database, not only on its tables. Every table must end up InnoDB: the server issues transactions only if all of them report it at connect time, and silently does not otherwise. A database the server creates itself is made `utf8mb4` already.

**The 6.3.4 upgrade on these two** rebuilds every table, and on a server older than 5.7.9 or 10.2.2 it stops at 6041 with error 1071, having changed nothing. [Databases on Linux](/topic/28247) covers that step in full, its time, disk and duplicates (error 1062) included, and [Upgrading to 6.3.4](/topic/28193) the rest of the upgrade.

### Creating the database on Windows

The wizard asks only "built-in or external". For the built-in engine, `DBSetupQuick.exe` creates the database at the current schema version, 6104. For an external engine, `DBSetup.exe` runs after the file copy, asks for the connection, and either creates the database or attaches one that exists; the secure defaults of a new 6.3.4 installation are applied only to one it created. Since 6.3.4 the administrator password reaches both tools in the environment variable `HM_ADMIN_PASSWORD`, never on a command line.

The tools write `[Database]` in `Bin\hMailServer.INI`:

| Key | Meaning |
|---|---|
| `Type` | `MSSQL`, `MYSQL`, `PostgreSQL` or `MSSQLCE`, matched case-insensitively |
| `Server`, `Database`, `Username`, `Password` | The connection. `Server` is empty for the internal engine |
| `PasswordEncryption` | Written by the server; says how `Password` is protected |
| `Port` | `0` uses the backend's default |
| `Internal` | `1` only for the embedded engine |
| `NumberOfConnections` | Pool size; default 5 |

**The password in this file may be machine-bound.** With `ProtectStoredSecretsWithDPAPI=1`, the default, the server writes `Password` through machine-scoped DPAPI (`PasswordEncryption=6`), and it cannot be decrypted on another machine. If you are also moving hosts, set the password on the new machine through `DBSetup.exe` rather than copying the encrypted one across.

**Under `NT SERVICE\hMailServer`**, the account a new 6.3.4 installation runs as, `hMailServer.INI` is read-only to the service, and the COM methods the two tools call to write `[Database]` are refused before anything is made. The installer therefore runs its database step while the service is still LocalSystem. To change the database later, move the service to LocalSystem for the change and back ([Installing on Windows](/topic/28227) has the commands), or edit `[Database]` by hand and restart.

### Encrypting the connection (since 6.3.4)

The test lab measured on 25 September 2026 that the connection to MySQL and MariaDB through MariaDB Connector/C, the client bundled on Windows and the one Ubuntu packages, was never encrypted: every account was reached in plaintext, password exchange, queries and rows alike, and one made with `REQUIRE SSL` refused the server. Since 6.3.4 two keys under `[Database]` choose the behaviour:

```
[Database]
TlsMode=verify-full
TlsCaFile=C:\hMailServer\certs\database-ca.pem
```

| `TlsMode` | What the connection does |
|---|---|
| *(empty, the default)* | **prefer**: TLS whenever the server offers it, and the connection without it when the server offers none, or when the TLS it offers cannot be agreed on (one attempt with TLS and one without, for that failure only). The certificate is not checked |
| `prefer` | As the default, said explicitly |
| `disable` | No TLS. SQL Server's providers still encrypt the login itself |
| `require` | TLS or no connection; the certificate is not checked |
| `verify-full` | TLS or no connection, the certificate checked: its chain to a CA in `TlsCaFile` (or the system's trust store when that is empty; since 6.3.8 an empty one is refused, HM7535, on MySQL and MariaDB through MariaDB Connector/C to a database server on this machine), and its name or address against `Server=` |

`TlsCaFile` is a PEM file of CA certificates, read by `verify-full` and by nothing else, so that a CA file left beside `require` cannot quietly make it stronger or weaker than it says. A `TlsCaFile` that `verify-full` would read and that is not there refuses the connection (HM7531) rather than falling back to the system's store. Any other word in `TlsMode` is refused without an attempt (HM7530); `verify-ca` is refused by name, and PostgreSQL's own `PostgreSQLSslMode=verify-ca` remains for PostgreSQL. The keys are read at the service's start.

| Backend | Before 6.3.4 | Since 6.3.4, with nothing set |
|---|---|---|
| MySQL / MariaDB, Windows and Linux | Plaintext, always | TLS whenever offered, unchecked; plaintext when not |
| PostgreSQL, both platforms | libpq's default, prefer | Unchanged: libpq's prefer, or a `PGSSLMODE` in the service's environment, stands. `TlsMode` and `TlsCaFile` become `sslmode` and `sslrootcert` unless `PostgreSQLSslMode` and `PostgreSQLSslRootCert` are set, which win. `verify-full` with no `TlsCaFile` is `sslrootcert=system` on Linux (libpq 16 and later), and libpq's own default file on Windows, `%APPDATA%\postgresql\root.crt` of the service's account |
| SQL Server, Windows | The login encrypted and nothing after it, unless the server forced encryption | The whole session encrypted through MSOLEDBSQL, when version 18 or later is installed, and through SQLNCLI for a failover partner; through SQLOLEDB only the login, unless `TlsMode` is `require` or `verify-full` |
| SQL Server, Linux | Refused: ADO is Windows (HM6390) | Unchanged |
| SQL Server Compact | A file on this machine | Unchanged |

On MySQL and MariaDB the old `AllowUnencryptedConnection` is still read and changes nothing: what it described, TLS preferred and plaintext allowed, is the default now.

On SQL Server `verify-full` checks against the Windows certificate store; `TlsCaFile` is not read (HM7532), and `ConnectionStringOptions` naming encryption beside a `TlsMode` is refused (HM7533).

**Seeing it.** The application log says how the connection is protected, once the service has started and again whenever it changes, for example `Database connection: PostgreSQL: NOT encrypted: the server offered no TLS (TlsMode prefer, the default)`; the diagnostics run on the Diagnostics pages carries the same line under *Server details*. At the service's start a connection refused for TLS is the connect failure it always was, HM4354, with the database client's own reason and a sentence naming the setting to change. `--check-database` makes one connection with the TLS settings in the configuration, says what it got, and exits 0 connected, 1 not, 2 not configured. It changes nothing, so it runs beside a running service:

```
hmailserver --config /etc/hmailserver/hMailServer.ini --check-database
hMailServer.exe --check-database --config C:\hMailServer\try.ini
```

**What the server does not do for you.** It cannot switch TLS on at the database server or give it a certificate. Under `verify-full` a MariaDB 11.4 server's own self-signed certificate is accepted without a CA file when the account's password proves it (MariaDB's zero-configuration TLS, for `mysql_native_password`, `ed25519` and `parsec` accounts), and not once a CA file is named. A database that must refuse a plaintext connection should say so itself: `REQUIRE SSL` on the MySQL account, `hostssl` only in `pg_hba.conf`, Force Encryption on SQL Server.

**What is thinner than it could be.** There is no editor for these keys in the Control Panel or the Deck. `require` on the MariaDB client is checked after the login, and an unencrypted connection is closed before a statement crosses it: by then a challenge and a response have crossed in the clear for `mysql_native_password`, `caching_sha2_password` and `ed25519` accounts, and the password itself for an account that signs in through PAM or the clear-text plugin. `verify-full` refuses before the login. `require` protects against someone who can listen and not against someone who can alter the traffic, and `prefer` connects without TLS to a server that seems to offer none; only `verify-full` protects against someone on the path. And only a login with VIEW SERVER STATE lets SQL Server say whether its session is encrypted.

### Moving off the built-in database

There is no migration tool. [Upgrading.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/Upgrading.md) records that as a gap; [MigratingDatabaseBackend.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/MigratingDatabaseBackend.md) says it is not an omission, because backup and restore are already backend-agnostic: back up, repoint, restore. It works because the archive's XML carries no identity values, and the message store is addressed by domain name, local part and the file's own guid, never by id. Your mail files never move.

**Before you start.** Write down how many domains, accounts, aliases and distribution lists there are, to verify against afterwards. Take a filesystem-level backup of the whole installation. Backup refuses to run if any message row points outside `DataFolder` ("All messages are not located in the data folder"). The restore refuses an archive taken by a newer hMailServer, so migrate on one version, then upgrade. Keep a copy of `hMailServer.ini`; the administrator password lives in it, so it carries over. And any mail delivered between the backup and the cutover is lost to the new database: hold inbound traffic, or accept the gap knowingly.

1. **Stop the service** (`Stop-Service hMailServer`). Restart it for the backup itself, which runs through the running server, but stop accepting new mail first if you can.
2. **Take the backup** on **Maintenance → Backup & restore** with all three of **Back up domains**, **Back up messages** and **Back up settings**. The archive lands as `HMBackup <local time>.7z`. All three are required: the restore refuses a combination that would delete something and put nothing back, and says so before it changes anything.

   **Set `BackupMessagesDBOnly` to `1` before both the backup and the restore.** Since 6.3.4 it is a stored setting that applies when it is saved, with no restart: set it in the Control Panel, over REST (`PUT /api/v1/settings/ini/BackupMessagesDBOnly` with `1`) or with `hmctl ini set BackupMessagesDBOnly 1`. On 6.3.3 it was a `[Settings]` line in the file, read at start, so restart there. Without it, **a normal restore empties the live data directory and copies the staged message store into it**; with it that step is skipped and your `.eml` files are never touched. Remove it afterwards.
3. **Stop the service again** (`Stop-Service hMailServer`) and wait for it to stop.
4. **Create the new database**: empty, with a login that may create tables; on MySQL or MariaDB, `utf8mb4`. Edit `[Database]` to point at it, with `Internal=0`. If the service runs as `NT SERVICE\hMailServer`, move it to LocalSystem first (`hMailServer.exe --service-account localsystem /Register`) and back once the migration is verified: under the virtual account the COM methods `DBSetupQuick.exe` calls are refused before anything is made. Then:

   ```powershell
   & "C:\Program Files\hMailServer\Bin\DBSetupQuick.exe"
   if ($LASTEXITCODE -ne 0) { throw "Database setup failed with exit code $LASTEXITCODE" }
   ```

   On MySQL and SQL Server Compact a creation that fails halfway leaves a partly built database: drop it and start this step again.
5. **Restore.** Start the service (`Start-Service hMailServer`). At its first start against the new database the server adopts the file's `[Settings]` section into the empty store.

   **Before you restore, check `BackupMessagesDBOnly` on the new database** (`hmctl ini get BackupMessagesDBOnly`, or the Control Panel). The setting has to be identical for the backup and the restore, and the restore runs against the new database, which has a settings store of its own. If it is not `1` there, set it again, which applies when it is saved. That is the case when the old server could not write its line into the file, which it reported as `HM5806` or `HM5803`, and under `NT SERVICE\hMailServer`, which does not write the file's copy. A restore without it is a normal one, which empties the data directory. Then:

   ```powershell
   $app = New-Object -ComObject hMailServer.Application
   $app.Authenticate("Administrator", "<password>")
   $backup = $app.BackupManager.LoadBackup("D:\hmail-migration\HMBackup 2026-08-13 220511.7z")
   $backup.RestoreDomains  = $true
   $backup.RestoreMessages = $true
   $backup.RestoreSettings = $true
   $backup.StartRestore()
   ```

   The server restarts itself at the end; that is expected.

**Verify all of these**, because a partial restore looks like a working server: the domain, account, alias and list counts, against what you wrote down before you started; the folder list over IMAP as a real account; one whole message body; certificates, ports, IP ranges, routes and rules; one message in and one out; and an empty `ERROR_hmailserver_<date>.log`.

**Rolling back** is exact until you delete the old database, which was only ever read: stop the service, put the original `[Database]` section back, start the service. Since 6.3.4 the settings come back with it, and a setting changed since the cutover is named as `HM5804` on that start, which is the rollback working, not a fault. Ids change, so a script that recorded `hm_accounts.accountid` will break; key on the address instead.

### What it does not cover, and where to read more

Moving the data folder to different storage is a separate operation, [Relocating an installation to another drive](/topic/28244); the path is `[Directories] DataFolder`. Moving to a new machine is [Moving to new hardware, and why Windows to Linux is a rebuild](/topic/28194). What the backup archive holds, and what it does not, is in [Backup and restore](/topic/28221).

Sources: the wiki's [Before You Install](https://gitlab.com/hmailserver/hmailserver/-/wikis/Before-You-Install), [Migrating the Database Backend](https://gitlab.com/hmailserver/hmailserver/-/wikis/Migrating-the-Database-Backend) and [Installing on Linux](https://gitlab.com/hmailserver/hmailserver/-/wikis/Installing-on-Linux); [MigratingDatabaseBackend.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/MigratingDatabaseBackend.md), [DatabaseTls.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/DatabaseTls.md) and [Upgrading.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/Upgrading.md); and sections [3.5](https://www.progressiverobot.com/hmailserver-documentation/#35-a-database) and [33.3](https://www.progressiverobot.com/hmailserver-documentation/#333-switching-database-engines) of the public documentation.