HELP: wrinting dovecot-sql.conf to authenticate to an existing database

[Filippo Zeus]

hi all, 

can anyone help me with dovecot-sql.conf please ? I'm not a mysql expert and I'am trying to authenticate dovecot2 to an existing database.


I have a database "egroupware" with table "egw_accounts". The field "account_lid" is the full email address and "account_pwd" is the password in Blowfish format.

Here is my dovecot-sql.conf

driver = mysql
connect = host=localhost dbname=egroupware user=egroupware password=*********
default_pass_scheme = BLF-CRYPT
password_query = SELECT account_pwd AS password FROM egw_accounts WHERE account_lid = '%u'



when I try to login to POP or IMAP I got this error in mail.log/mail.info

Error: sql(hidden at mydomain.com,::1): Invalid password in passdb: crypt() failed: Invalid argument
auth: Debug: client passdb out: FAIL#0111#011user=hidden at mydomain.com
dovecot: pop3-login: Debug: Ignoring unknown passdb extra field:

I think I'm having trouble with the password value that is stored in the account_pwd as: 

{crypt}$2a$12$XTAolzXgwMC211MuZELQUedPv9tjZNs7Osh97PnZ50pAKuk3WPGmS

Reading at http://wiki2.dovecot.org/Authentication/PasswordSchemes

I suppose that the heading "{crypt}$2a$" instead of a simple "$2a$" (witch identifies a Blowfish password) is my problem.

How can I fix this ? 

Here is my dovecot -n output:


# 2.2.13: /etc/dovecot/dovecot.conf
# OS: Linux 3.16.0-4-amd64 x86_64 Debian 8.1 xfs
auth_cache_size = 64 M
auth_debug = yes
auth_mechanisms = plain login
lda_mailbox_autocreate = yes
log_timestamp = "%Y-%m-%d %H:%M:%S "
login_log_format_elements = USER=<%u> METHOD=%m FROM=%r TO=%l %c
mail_location = maildir:/var/mail/%d/%n/Maildir
mail_plugins = quota
mail_privileged_group = vmail
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date ihave spamtest spamtestplus imapflags notify
namespace inbox {
  inbox = yes
  location = 
  mailbox Archive {
    auto = no
    special_use = \Archive
  }
  mailbox "Deleted Messages" {
    auto = no
    special_use = \Trash
  }
  mailbox Drafts {
    special_use = \Drafts
  }
  mailbox Sent {
    special_use = \Sent
  }
  mailbox "Sent Messages" {
    special_use = \Sent
  }
  mailbox Spam {
    auto = no
    special_use = \Junk
  }
  mailbox Trash {
    auto = no
    special_use = \Trash
  }
  prefix = 
  separator = .
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  quota = maildir:User quota
  quota_exceeded_message = Storage quota for this account has been exceeded, please try again later.
  quota_grace = 10%%
  quota_rule = *:storage=6G
  quota_rule2 = Trash:storage=+100M
  quota_rule3 = spam:ignore
  quota_warning = storage=95%% quota-warning 95 %u
  sieve = ~/dovecot.sieve
  sieve_before = /var/mail/sieve/before.sieve
  sieve_default = /var/mail/sieve/default.sieve
  sieve_dir = ~/sieve.d
  sieve_extensions = +notify +imapflags +spamtest +spamtestplus +relational +comparator-i;ascii-numeric
  sieve_global_dir = /var/mail/sieve/global.d
  sieve_max_script_size = 1M
}
postmaster_address = postmaster@%d
protocols = " imap sieve pop3 sieve"
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
}
service imap-login {
  inet_listener imap {
    port = 143
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}
service managesieve-login {
  inet_listener sieve {
    port = 4190
  }
  service_count = 1
  vsz_limit = 64 M
}
service managesieve {
  process_limit = 15
}
service quota-warning {
  executable = script /usr/local/bin/quota-warning.sh
  unix_listener quota-warning {
    user = vmail
  }
  user = dovecot
}
ssl = required
ssl_cert = </etc/ssl/dharma/mail/mail_antonica_it.crt
ssl_key = </etc/ssl/dharma/mail/mail_antonica_it.key
userdb {
  args = uid=vmail gid=vmail home=/var/mail/%d/%n
  driver = static
}
protocol lda {
  mail_plugins = quota sieve
}
protocol imap {
  mail_plugins = quota imap_quota
}
protocol sieve {
  mail_max_userip_connections = 10
  managesieve_logout_format = bytes=%i/%o
  managesieve_max_line_length = 65536
}