dovecot - Feb 2017 - How to add User in MSSQL DB - error unknown user

Hello

 

Please I need to add  "Maurizio" to this MSSQL DB, but I don't now
how to
add this step 

in my opinion I'think that the user can't be found, so I will have the
"unknown user"

Can give here any little help to fix this?

 

Feb  8 12:09:56 caloro dovecot: auth: Debug: auth client connected
(pid=13300)

Feb  8 12:09:56 caloro dovecot: auth: Debug: client in:
AUTH#0111#011PLAIN#011service=pop3#011session=dfsfdsdsJITACX+KIh#011lip=37.1
20.190.188#011rip=151.248.162.33#011lport=110#011rport=54604#011resp=<hidden
>
Feb  8 12:09:56 caloro dovecot: auth-worker(13303): Debug: Loading modules
from directory: /usr/lib/dovecot/modules/auth

Feb  8 12:09:56 caloro dovecot: auth-worker(13303): Debug: Module loaded:
/usr/lib/dovecot/modules/auth/libdriver_mysql.so

Feb  8 12:09:56 caloro dovecot: auth-worker(13303): Debug:
sql(maurizio at caloro.ch,151.248.162.33): query: SELECT username AS user,
password_enc AS password, CONCAT(homedir, maildir) AS userdb_home, uid AS
userdb_uid, gid AS userdb_gid,  CONCAT('maildir:', homedir, maildir) AS
userdb_mail, CONCAT('maildir:storage=', (quota*1024)) as userdb_quota
FROM
mail_users WHERE (username = 'maurizio at caloro.ch' OR email
'maurizio at caloro.ch') AND ((imap = 1 AND 'pop3' =
'imap') OR (pop3 = 1 AND
'pop3' = 'pop3') OR 'pop3' = 'smtp' OR
'pop3' = 'sieve')

 

Feb  8 12:09:56 caloro dovecot: auth-worker(13303):
sql(maurizio at caloro.ch,151.248.162.33): unknown user

 

Feb  8 12:09:58 caloro dovecot: auth: Debug: client passdb out:
FAIL#0111#011user=maurizio at caloro.ch

Feb  8 12:09:58 caloro dovecot: pop3-login: Disconnected (auth failed, 1
attempts in 2 secs): user=<maurizio at caloro.ch>, method=PLAIN,
rip=151.248.162.33, lip=37.120.190.188, session=<DPyY5wJITACX+KIh>

 

Regards and thank for any hint or help

mauri

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 8 Feb 2017, Maurizio Caloro wrote:
> Please I need to add  "Maurizio" to this MSSQL DB, but I
don't now how to
> add this step
add this step to which workflow, action, ... ?
> in my opinion I'think that the user can't be found, so I will have
the
> "unknown user"
>
> Can give here any little help to fix this?
INSERT INTO mail_users (...) VALUES (*correct* data of maurizio);

Who has installed the Dovecot server and the user DB? This person should 
know how to fill the *correct* data into the SQL table.
> Feb  8 12:09:56 caloro dovecot: auth-worker(13303): Debug:
> sql(maurizio at caloro.ch,151.248.162.33): query: SELECT username AS user,
> password_enc AS password, CONCAT(homedir, maildir) AS userdb_home, uid AS
> userdb_uid, gid AS userdb_gid,  CONCAT('maildir:', homedir,
maildir) AS
> userdb_mail, CONCAT('maildir:storage=', (quota*1024)) as
userdb_quota FROM
> mail_users WHERE (username = 'maurizio at caloro.ch' OR email >
'maurizio at caloro.ch') AND ((imap = 1 AND 'pop3' =
'imap') OR (pop3 = 1 AND
> 'pop3' = 'pop3') OR 'pop3' = 'smtp' OR
'pop3' = 'sieve')
- -- 
Steffen Kaiser
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQEVAwUBWJsGd3z1H7kL/d9rAQJA+Af9EQk22pq64AYSxwQhkjm0a5D3qA72oW+M
+k+46ivZXYvZiqGPYzY7YpeUQAKSRw6ihkIeF+hrm8Li6bauZJ5mDt7+DBZNCXc4
5S0+qZpuVBFGrC/k/grajnkRiiB56ejMkGjNLiB6tukUVGoeT3U5Q7hzmW0q0hlf
vECIswRv6Yct0ZfsmHpy1apeB3HwOb3z4C8a6oaZBMkgo3GhSeJZQVKfeJtjaG2O
HIPMHjvFKJjw9P8DfRjP5rqqUxI2MZLYq5ShI/pgOIqNoTSTvRCALbcAdOj0DO8R
2xCH+0vB3vEMk4kz0bQxlW0EXdJYJkoqx2O19dNDHVdYkPBexes78w==tFGQ
-----END PGP SIGNATURE-----

Linux caloro.ch 3.16.0-4-amd64 #1 SMP Debian 3.16.39-1 (2016-12-30) x86_64
GNU/Linux

Apt-get install postfix dovecot
Adduser -m maurizio
Add config steps to dovecot.conf, main.cf, master.cf
 
Email from CLI running, but if connect from Outlook or Thunderbird I have
this error, I think
that maurizio this user aren?t opened correct on this sqldb?

root at caloro:/home/maurizio# postconf -n
alias_maps = $alias_database
append_dot_mydomain = no
biff = no
broken_sasl_auth_clients = yes
command_directory = /usr/sbin
config_directory = /etc/postfix
daemon_directory = /usr/lib/postfix
data_directory = /var/lib/postfix
debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin ddd
$daemon_directory/$process_name $process_id & sleep 5
dovecot_destination_recipient_limit = 1
home_mailbox = Maildir/
html_directory = no
inet_interfaces = all
inet_protocols = ipv4
local_transport = local
mailbox_size_limit = 0
mailq_path = /usr/bin/mailq
manpage_directory = /usr/share/man
message_size_limit = 52428800
mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain
mydomain = nmail.caloro.ch
myhostname = nmail.caloro.ch
mynetworks = 127.0.0.0/8 192.168.1.0/27
myorigin = $mydomain
newaliases_path = /usr/bin/newaliases
queue_directory = /var/spool/postfix
readme_directory = /usr/share/doc/postfix
sample_directory = /usr/share/doc/postfix
sendmail_path = /usr/sbin/sendmail
setgid_group = postdrop
smtp_tls_note_starttls_offer = yes
smtp_tls_security_level = may
smtpd_banner = $myhostname ESMTP $mail_name
smtpd_client_restrictions = permit_mynetworks, permit_sasl_authenticated,
reject_unknown_client_hostname
smtpd_helo_required = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated,
reject_unauth_destination, reject_unauth_pipelining,
reject_non_fqdn_recipient
smtpd_relay_restrictions smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = $myhostname
smtpd_sasl_path = private/auth
smtpd_sasl_type = dovecot
smtpd_sender_login_maps mysql:/etc/postfix/mysql-virtual_sender_permissions.cf
smtpd_sender_restrictions = permit_mynetworks, reject_sender_login_mismatch,
permit_sasl_authenticated, reject_unknown_helo_hostname,
reject_unknown_recipient_domain, reject_unknown_sender_domain
smtpd_tls_cert_file = /etc/ssl/server/servername.pem
smtpd_tls_key_file = $smtpd_tls_cert_file
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_security_level = may
unknown_local_recipient_reject_code = 550
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual_alias_maps.cf
virtual_gid_maps = static:2000
virtual_mailbox_base = /var/customers/mail/
virtual_mailbox_domains mysql:/etc/postfix/mysql-virtual_mailbox_domains.cf
virtual_mailbox_limit = 0
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual_mailbox_maps.cf
virtual_transport = dovecot
virtual_uid_maps = static:2000


-----Urspr?ngliche Nachricht-----
Von: dovecot [mailto:dovecot-bounces at dovecot.org] Im Auftrag von Steffen
Kaiser
Gesendet: Mittwoch, 8. Februar 2017 12:52
An: Maurizio Caloro <mauric at gmx.ch>
Cc: dovecot at dovecot.org
Betreff: Re: How to add User in MSSQL DB - error unknown user

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 8 Feb 2017, Maurizio Caloro wrote:
> Please I need to add  "Maurizio" to this MSSQL DB, but I
don't now how
> to add this step
add this step to which workflow, action, ... ?
> in my opinion I'think that the user can't be found, so I will have
the
> "unknown user"
>
> Can give here any little help to fix this?
INSERT INTO mail_users (...) VALUES (*correct* data of maurizio);

Who has installed the Dovecot server and the user DB? This person should
know how to fill the *correct* data into the SQL table.
> Feb  8 12:09:56 caloro dovecot: auth-worker(13303): Debug:
> sql(maurizio at caloro.ch,151.248.162.33): query: SELECT username AS 
> user, password_enc AS password, CONCAT(homedir, maildir) AS 
> userdb_home, uid AS userdb_uid, gid AS userdb_gid, 
CONCAT('maildir:',
> homedir, maildir) AS userdb_mail, CONCAT('maildir:storage=', 
> (quota*1024)) as userdb_quota FROM mail_users WHERE (username = 
> 'maurizio at caloro.ch' OR email > 'maurizio at
caloro.ch') AND ((imap = 1 AND 'pop3' = 'imap') OR (pop3 = 1
> AND 'pop3' = 'pop3') OR 'pop3' = 'smtp' OR
'pop3' = 'sieve')
- --
Steffen Kaiser