Could not request access token for. The refresh token could be expired or invali

[tobiasmith]

Hi, once I set OAUTH2 for Exchange Online Mail account in OTOBO, downloading emails will stop with error as bellow. After edit and save mail account and login to Microsoft, error will stop for next hour. In the auth_token table will be a new refresh key with every "edit and save mail account"

Backend ERROR: OTOBO-CGI-00 Perl: 5.32.1 OS: linux Time: Thu Jun 30 17:28:34 2022

Message: CommunicationLog(ID:126465,AccountType:-,AccountID:-,Direction:Incoming,Transport:Email,ObjectLogType:Connection,ObjectLogID:187362)::Kernel::System::MailAccount::POP3 => POP3OAuth2: Could not request access token for XXXX‘. The refresh token could be expired or invalid.

RemoteAddress:
RequestURI: /otobo/index.pl?Action=AdminMailAccount;Subaction=Run;ID=2;ChallengeToken=XXXXX;

[Punamu]

@stefanhaerter Sorry to mention you directly, but since this issue is now open for 2 years is there any chance this could be looked at?

The linked PR fixed the issue for us. As of right now we have to alter the code manually after every update.

[stefanhaerter]

The mention is fine, don't worry - this will definitely been looked at, but as I'm not familiar with Outlook / Exchange at all, I can't give any estimate about this. Your PR looks like a sensible change, but usually we try to make an effort to understand why the code causing the failure was implemented and which possible side effects are to be considered.

I'll provide an update as soon as we have the capacity to review this.

[Punamu]

Thanks for the update!

I can't answer the "why" but there are some inconsistencies in the code regarding access of the account_type column.
Maybe this helps to speed up the analysis.

The insert happens without a LOWER:

MailAccount-OAuth2/Kernel/System/OAuth2.pm

Lines 263 to 264 in 1db1f36

	SQL => "INSERT INTO $Self->{TokenTable} (token, account_type, account_id, token_type) "
	. "VALUES (?, ?, ?, 'refresh')",

Also these deletes don't have a LOWER either:

• MailAccount-OAuth2/Kernel/System/OAuth2.pm

  Line 299 in 1db1f36

  SQL => "DELETE FROM $Self->{TokenTable} WHERE account_type = ? AND account_id = ? AND token_type = ?",

• MailAccount-OAuth2/Kernel/System/OAuth2.pm

  Line 305 in 1db1f36

  SQL => "DELETE FROM $Self->{TokenTable} WHERE account_type = ? AND account_id = ?",

[yahikii]

Hello, are there any updates to this situation? We also had this issue and fixed it with removing the "LOWER" infront of the "?" like Punamu described. I found the solution back then randomly in this otobo forum post: https://otobo.io/forums/topic/could-not-request-access-token-for-the-refresh-token-could-be-expired-or-invali/

The Issue is still current and I have the feeling that many more people have this issue without having an idea that its such an easy simple solution to fix it. I like to highlight that there is an open pull request with such an easy solution who would solve this issue for everyone. I don't get it why it wasn't implemtented especially as Punamu said the use of the "LOWER" function is so inconsistent. Currently I have implemented a work around for it but it would be nice to someday be able to remove it.

Thank you already for an update to it and I hope this solution can be solved soon 😊

[svenoe]

(Sorry, this package was initially developed by one of our partners for one customer and with adopting it, the responsibility on our end(!) was not decided on clearly. All people have plenty of other things to work on, none of our customers complained, testing it is a hassle,... This is quite clear, though.)