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

[Progressiverobot]

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. 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.

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 has the steps for each backend, and First install on Linux 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 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)
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:

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 covers that step in full, its time, disk and duplicates (error 1062) included, and Upgrading to 6.3.4 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 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 records that as a gap; 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:

   & "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:

   $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; the path is [Directories] DataFolder. Moving to a new machine is Moving to new hardware, and why Windows to Linux is a rebuild. What the backup archive holds, and what it does not, is in Backup and restore.

Sources: the wiki's Before You Install, Migrating the Database Backend and Installing on Linux; MigratingDatabaseBackend.md, DatabaseTls.md and Upgrading.md; and sections 3.5 and 33.3 of the public documentation.