AD login no longer working with upgrade to Server 2016

[SharazJek]

AD login was previously working great, but i just noticed today that after all my DCs are upgraded to 2016, EFA is no longer able to login. i am getting Bad Username or Password on my 3018 box, and my production 3015 box is getting an SQL error.

Error executing query:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 169

SQL:

SELECT
COUNT(*) AS processed,
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
) AS clean,
ROUND((
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS cleanpercent,
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
) AS viruses,
ROUND((
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS viruspercent,
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
-- AND (isspam=0 OR isspam IS NULL)
-- AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS blockedfiles,
ROUND((
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
-- AND (isspam=0 OR isspam IS NULL)
-- AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS blockedfilespercent,
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS otherinfected,
ROUND((
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS otherinfectedpercent,
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS spam,
ROUND((
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS spampercent,
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
) AS highspam,
ROUND((
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highspampercent,
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
) AS mcp,
ROUND((
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS mcppercent,
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS highmcp,
ROUND((
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highmcppercent,
SUM(size) AS size
FROM
maillog
WHERE
date = CURRENT_DATE()
AND
()

[SharazJek]

has no one else used a Server 2016 AD domain with EFA AD Authentication? mine went belly up when i upgraded my domain from 2012R2 to 2016.

[shawniverson]

This could be a mailwatch bug. Have you tried upgrading to 3.0.1.8 to see if the problem resolves (after snapshotting and suspending mail flow)?

Also, have you tried using a full email address to login instead of just the username?

[SharazJek]

full email and just short username both log in successfully, but both formats generate the error above.

ill try updating and see what happens. i was also considering doing a rip and replace, but i hate to lose all my learning data from the database. is there a good way to export and import this data into a new install?