Authentication agains external SQL (MariaDB) directory not working

ralfbecker · June 5, 2026, 8:24am

Issue Description

I created am external SQL directory using the recovery admin AND set it under Authentication to be used (instead of the internal directory).
stalwart-cli snapshot gives the following output:

{
  "@type": "destroy",
  "object": "Directory"
}
{
  "@type": "create",
  "object": "Directory",
  "value": {
    "directory-iuhrsiviaaqa": {
      "@type": "Sql",
      "queryEmailAliases": "SELECT     alias.mail_value as address FROM egw_accounts JOIN egw_addressbook ON egw_accounts.account_id=egw_addressbook.account_id JOIN egw_mailaccounts a
lias ON egw_accounts.account_id=alias.account_id AND alias.mail_type=3 JOIN egw_mailaccounts active ON egw_accounts.account_id=active.account_id AND active.mail_type=0 AND active.mail
_value='active' LEFT JOIN egw_mailaccounts delivery ON egw_accounts.account_id=delivery.account_id AND delivery.mail_type=1 WHERE account_status='A' AND account_type='u' AND (account_
expires=-1 OR account_expires>UNIX_TIMESTAMP())     AND (delivery.mail_value IS NULL OR delivery.mail_value<>'forwardOnly') AND account_lid<>'anonymous'     AND contact_email = $1",
      "description": "EGroupware",
      "columnEmail": "name",
      "columnClass": "type",
      "columnSecret": "secret",
      "memberTenantId": null,
      "store": {
        "@type": "MySql",
        "allowInvalidCerts": false,
        "authUsername": "ralf",
        "database": "egroupware",
        "maxAllowedPacket": null,
        "readReplicas": {},
        "timeout": 15000,
        "port": 3306,
        "poolMinConnections": 5,
        "useTls": false,
        "poolMaxConnections": 10,
        "host": "db",
        "authSecret": {
          "@type": "Value",
          "secret": "****"
        }
      },
      "queryMemberOf": "SELECT     groups.account_lid AS member_of FROM egw_accounts groups JOIN egw_acl ON acl_location=CAST(-groups.account_id AS char CHARACTER SET ascii) AND acl_a
ppname='phpgw_group' JOIN egw_addressbook ON egw_addressbook.account_id=egw_acl.acl_account WHERE groups.account_type='g' AND egw_addressbook.contact_email = $1",
      "queryRecipient": "SELECT     contact_email AS name,     account_pwd AS secret,     n_fn AS description,     'individual' AS type     /*1024*1024*quota.mail_value AS quota*/ FRO
M egw_accounts JOIN egw_addressbook ON egw_addressbook.account_id=egw_accounts.account_id JOIN egw_mailaccounts active ON egw_accounts.account_id=active.account_id AND active.mail_typ
e=0 AND active.mail_value='active' /*LEFT JOIN egw_mailaccounts quota ON egw_accounts.account_id=quota.account_id AND quota.mail_type=2*/ LEFT JOIN egw_mailaccounts delivery ON egw_ac
counts.account_id=delivery.account_id AND delivery.mail_type=1 LEFT JOIN egw_mailaccounts alias ON egw_accounts.account_id=ABS(alias.account_id) AND alias.mail_type=3 WHERE (account_t
ype='g' OR active.mail_value='active' AND account_status='A' AND (account_expires=-1 OR account_expires>UNIX_TIMESTAMP()))   AND (delivery.mail_value IS NULL OR delivery.mail_value<>'
forwardOnly') AND account_lid<>'anonymous'   AND (contact_email = $1 OR alias.mail_value = $1) UNION  SELECT     contact_email AS name,     member.account_pwd AS secret,     n_fn AS d
escription,     'individual' AS type     /*1024*1024*quota.mail_value AS quota*/ from egw_accounts groups join egw_mailaccounts gmail on gmail.account_id=-groups.account_id and gmail.
mail_type=3 JOIN egw_acl ON acl_location=CAST(-groups.account_id AS char CHARACTER SET ascii) AND acl_appname='phpgw_group' JOIN egw_accounts member ON acl_account=member.account_id J
OIN egw_addressbook on member.account_id=egw_addressbook.account_id JOIN egw_mailaccounts active ON member.account_id=active.account_id AND active.mail_type=0 AND active.mail_value='a
ctive' /*LEFT JOIN egw_mailaccounts quota ON member.account_id=quota.account_id AND quota.mail_type=2*/ LEFT JOIN egw_mailaccounts delivery ON member.account_id=delivery.account_id AN
D delivery.mail_type=1 where groups.account_type='g' AND active.mail_value='active'   AND (delivery.mail_value IS NULL OR delivery.mail_value<>'forwardOnly') AND member.account_lid<>'
anonymous'   AND member.account_status='A' AND (member.account_expires=-1 OR member.account_expires>UNIX_TIMESTAMP())   AND gmail.mail_value = $1",
      "columnDescription": "description",
      "queryLogin": "SELECT     contact_email AS name,     account_pwd AS secret,     n_fn AS description,     'individual' AS type     /*1024*1024*quota.mail_value AS quota*/ FROM eg
w_accounts JOIN egw_addressbook ON egw_addressbook.account_id=egw_accounts.account_id LEFT JOIN egw_mailaccounts active ON egw_accounts.account_id=active.account_id AND active.mail_ty
pe=0 AND active.mail_value='active' /*LEFT JOIN egw_mailaccounts quota ON egw_accounts.account_id=quota.account_id AND quota.mail_type=2*/ LEFT JOIN egw_mailaccounts delivery ON egw_a
ccounts.account_id=delivery.account_id AND delivery.mail_type=1 WHERE active.mail_value='active' AND account_status='A' AND (account_expires=-1 OR account_expires>UNIX_TIMESTAMP())
AND (delivery.mail_value IS NULL OR delivery.mail_value<>'forwardOnly')   AND account_lid<>'anonymous' AND contact_email = $1"
    }
  }
}
{
  "@type": "update",
  "object": "Authentication",
  "value": {
    "maxAppPasswords": 5,
    "maxApiKeys": 5,
    "passwordHashAlgorithm": "argon2id",
    "passwordMaxLength": 128,
    "passwordDefaultExpiry": null,
    "passwordMinLength": 8,
    "passwordMinStrength": "three",
    "directoryId": "#directory-iuhrsiviaaqa"
  }
}

Then I try to log in with [email protected] for which the queryLogin returns the correct secret, testing it e.g. in phpMyAdmin.

Expected Behavior

The login should succeed.

Actual Behavior

I get the error message: Invalid username or password. Please try again.

Relevant Log Output

I set the a Tracer to console with log-level Trace, but nothing is shown when I try to login with a user from the external directory, while logging in with the recovery admin produces a line in docker logs -f stalwart-mail.
When I trace tcp.port == 3306, I can see no connection from Stalwart

Stalwart Version

v0.16.x

Installation Method

Docker

Database Backend

RocksDB

Blob Storage

Filesystem

Search Engine

Internal

Directory Backend

SQL

Additional Context

Stalwart is started / running with the following docker-compose.yml:

networks:
  dev_default:
    external: true
services:
  mail:
    restart: unless-stopped
    networks:
       - dev_default
    image: stalwartlabs/stalwart:v0.16-alpine
    container_name: stalwart-mail
    ports:
      - "993:993"
      - "4190:4190"
      - "587:587"
      - "465:465"
      - "25:25"
      - "8081:8080"
    extra_hosts:
      - "boulder.egroupware.org:172.17.0.1"
    volumes:
      - $PWD/data:/var/lib/stalwart
      - $PWD/config:/etc/stalwart
      - $PWD/certificates:/opt/stalwart/certs
    environment:
      - STALWART_RECOVERY_ADMIN=admin:secretPW
      # disables the mail-server only gives the UI: https://stalw.art/docs/configuration/recovery-mode/
      #- STALWART_RECOVERY_MODE=1
      - STALWART_PUBLIC_URL=https://boulder-stalwart.egroupware.org

There is a Nginx proxy running in an other container giving access to the WebUI (port 8080) under the given URL https://boulder-stalwart.egroupware.org and I can successful login with the recovery-admin and PW. Which I used to configure the directory.

I have reviewed the documentation and FAQ and confirm that my issue is NOT addressed there.

on

I have searched this support forum (open and closed topics) and confirm this is not a duplicate.

on

I understand that topics in this category are triaged by a bot first but a human reply will follow up. If I’d prefer a human-only reply, I’ll add the no-ai tag to my topic.

on

helpbot · June 5, 2026, 8:25am

Let me look into the SQL directory authentication configuration to help diagnose this issue.

I can see several issues in your configuration. Here are the most likely causes for the login failure and the missing DB traffic:

Issue 1: `columnEmail` is set to `"name"` but your query returns `contact_email AS name`

Your snapshot shows:

"columnEmail": "name",
"columnSecret": "secret",
"columnClass": "type",
"columnDescription": "description"

These are fine because your queryLogin aliases the columns correctly (contact_email AS name, account_pwd AS secret, etc.). So the column mappings themselves look correct.

Issue 2: Password hash format - most likely the root cause

The account_pwd field in EGroupware stores passwords in its own format. Stalwart only recognises specific hash prefixes (argon2, bcrypt, SHA-512 crypt $6$ , SHA-256 crypt $5$ , MD5 $1$ , {SHA}, {SSHA}, {PLAIN}, {CRYPT}, etc. – see the Passwords docs).

If EGroupware stores passwords in a format Stalwart doesn’t recognise (e.g. a custom scheme or plain MD5 without the expected prefix), Stalwart will fall back to plain-text comparison and fail. You need to verify that SELECT account_pwd FROM egw_accounts WHERE account_lid = 'youruser' returns a hash with one of the supported prefixes.

Issue 3: No TCP connection to port 3306 at all

This is the most telling symptom – Stalwart is not even trying to reach the DB. This strongly suggests the external directory is not being used for the login attempt at all, likely because:

The login attempt is hitting the recovery admin path (which bypasses the configured directory), OR
There is a configuration issue where the directory is defined but not actually reachable/loaded

Things to check:

Confirm you are logging in via IMAP/SMTP (port 993/587), not the WebUI at port 8080. The WebUI login uses the recovery admin credential separately.
The authSecret shows "****" – confirm the actual DB password is saved correctly in the config.
The host: "db" – confirm that hostname resolves from within the stalwart-mail container on the dev_default network. You can test with docker exec stalwart-mail ping db or docker exec stalwart-mail nc -zv db 3306.

Issue 4: `extra_hosts` points to `172.17.0.1`, not to `db`

Your extra_hosts maps boulder.egroupware.org to the Docker host bridge IP, but your DB store uses host: "db". That hostname needs to resolve to your MariaDB container within the shared dev_default network. If the MariaDB container is not on dev_default, Stalwart cannot reach it.

References:

This is an automated reply from the Stalwart Help Bot. Other community members may follow up if this answer is incomplete or wrong.

ralfbecker · June 5, 2026, 8:43am


docker exec stalwart-mail nc -zv db 3306
db (172.18.0.18:3306) open

So the DB access / networking seems to be find.

Ok, so I commented the recovery-admin in docker-compose.yml and restarted the container.

Now I can see in Wireshark that Stalwart actually talks to the DB, and sends the loginQuery, but still with `$1` and not the actual login name.

And therefore get’s an error back:
`Unknown column ‘$1’ in ‘WHERE’`

The Stalwart login then shows now a temp. error:
`Temporary server failure. If the problem persists, contact your administrator.`

So it looks like the format for the prepared parameter `$1`, which was shown in the WebUI, is NOT correct for MariaDB!

MariaDB uses `?` as placeholder not `$1`.

Topic		Replies	Views
Subaddress and sql directory Issues	17	34	June 6, 2026
Can't authenticate to STMP from LDAP directory Issues	3	23	May 29, 2026
Strange authentication failure for newly created account using Stalwart CLI Issues	4	19	June 14, 2026
Login problem after exit recoverymode on windows Questions	4	26	May 28, 2026
Stalwart-cli does not support commands in the migration document Issues	2	27	June 15, 2026