I found (by reading the Dovecot source) that the correct format is user:example.com:password, not user@example.com::password.
I've also narrowed down the problem a bit. It seems there's a problem using the DIGEST-MD5 mech. The {DIGEST-MD5} scheme works just fine from a SQL database. With a {DIGEST-MD5} password the database, the PLAIN mech works, but not the DIGEST-MD5 mech:
dovecot: auth(default): new auth connection: pid=69873 dovecot: auth(default): client in: AUTH 1 PLAIN service=smtp nologin resp=<hidden> dovecot: auth-worker(default): sql(brt.a@srv.twinthornes.com): query: SELECT password FROM mailbox WHERE username = 'brt.a@srv.twinthornes.com' AND active=1 dovecot: auth(default): client out: OK 1 user=brt.a@srv.twinthornes.com
dovecot: auth(default): new auth connection: pid=69884 dovecot: auth(default): client in: AUTH 1 DIGEST-MD5 service=smtp nologin dovecot: auth(default): client out: CONT 1 cmVhbG09IiIsbm9uY2U9IktvVGpxbkNJaVEzcEMwYjVrMFY5Zmc9PSIscW9wPSJhdXRoIixjaGFyc2V0PSJ1dGYtOCIsYWxnb3JpdGhtPSJtZDUtc2VzcyI= dovecot: auth(default): client in: CONT<hidden> dovecot: auth-worker(default): sql(brt.a@srv.twinthornes.com): query: SELECT password FROM mailbox WHERE username = 'brt.a@srv.twinthornes.com' AND active=1 dovecot: auth(default): digest-md5(brt.a@srv.twinthornes.com): password mismatch dovecot: auth(default): client out: FAIL 1 user=brt.a@srv.twinthornes.com