OAUTH2 - "The request body must contain the following parameter: 'client_id'"

[pswarz]

Hi everyone,

I'm not sure if this is a general bug or if I made a mistake while migrating from OTRS 6 to Znuny 6.4.5, but I have this issue:

I try to get a OAUTH2 token from Microsoft Azure App for the first time and while I'd expect Microsoft to ask me to sign in or (if I'm already signed) to grant permissions, I receive this error from Microsoft:

Sorry, but we’re having trouble signing you in.

AADSTS900144: The request body must contain the following parameter: 'client_id'.

I double-checked SysConfig for having https enabled, having the FQDN and the ScriptAlias right, also I've double-checked my Azure App und have recreated another one (just to be sure )

After checking my apache logs I've found error messages in OAUTH2 module that the field "token_config_id" is missing in oauth2 table (MySQL).
So I had a look at the database table and - yes - I don't have this table. In my case it is just named "config_id".

Another issue I've seen is that I am not able to interact with my already created tokens.
For example while trying do delete them I receive this:

Backend ERROR: OTRS-CGI-00 Perl: 5.30.0 OS: linux Time: Sat Jan 21 22:29:22 2023

Message: Error fetching token for token config with ID 1.

RemoteAddress: 8x.x.x.x
RequestURI: /otrs/index.pl?Action=AdminOAuth2TokenManagement;Subaction=DeleteTokenConfig;ID=2

Traceback (7159):
Module: Kernel::System::OAuth2Token::HasRefreshTokenExpired Line: 1107
Module: Kernel::Output::HTML::Notification::OAuth2TokenManagementTokenExpired::Run Line: 92
Module: Kernel::Output::HTML::Layout::NavigationBar Line: 3332
Module: Kernel::Output::HTML::Layout::ErrorScreen Line: 1018
Module: Kernel::Modules::AdminOAuth2TokenManagement::_DeleteTokenConfig Line: 162
Module: Kernel::Modules::AdminOAuth2TokenManagement::Run Line: 65
Module: Kernel::System::Web::InterfaceAgent::Run Line: 1144
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler Line: 39
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32

also using the OTRS Kernel modules for interacting with the OAUTH2 tokens are throwing errors regarding the missing field "token_config_id".

Is it safe just to rename the field to the value OTRS is trying to address?
Do you have an idea what to do here?

Everything else seems to work fine.
Any help is appreciated, thank you in advance!

[zzz]

Hey,

I assume, you've skipped some steps on the update.

Please ensure that you went through every step from the official documentation, especially going through every minor release (6 -> 6.1 -> 6.2 -> 6.3 -> 6.4), and that you've executed the migration script on every step.

— Emin

[pswarz]

Hi Emin,

thank you for your reply.
I've been following the update process as described in the official documentation.

Anyway - I'm open to repeat the steps.
Is it safe to have a "downgrade"? Or what would be the best way to repeat the steps?

Thank you!

[zzz]

The best thing would be to start over again from a backup.

Could you post the output of this query?

Code: Select all

SHOW COLUMNS FROM oauth2_token;

After checking my apache logs I've found error messages in OAUTH2 module that the field "token_config_id" is missing in oauth2 table (MySQL).
So I had a look at the database table and - yes - I don't have this table. In my case it is just named "token_id".

Does that mean you have the table “oauth2_token” but your column is called “token_config_id” instead of “token_id”, or does it mean that your table is called “token_id”? Neither would make sense to me.

The table gets created with the migration script for Znuny 6.3. Maybe you can dig deeper in your web server logs for older error messages – especially from the time of executing the migration script.

— Emin

[pswarz]

Hi Emin,

this is the output of the command:

Code: Select all

mysql> SHOW COLUMNS FROM oauth2_token;
+-------------------------------+-------------+------+-----+---------+----------------+
| Field                         | Type        | Null | Key | Default | Extra          |
+-------------------------------+-------------+------+-----+---------+----------------+
| id                            | int         | NO   | PRI | NULL    | auto_increment |
| config_id                     | int         | NO   | UNI | NULL    |                |
| authorization_code            | mediumtext  | YES  |     | NULL    |                |
| token                         | mediumtext  | YES  |     | NULL    |                |
| token_expiration_date         | datetime    | YES  |     | NULL    |                |
| refresh_token                 | mediumtext  | YES  |     | NULL    |                |
| refresh_token_expiration_date | datetime    | YES  |     | NULL    |                |
| error_message                 | mediumtext  | YES  |     | NULL    |                |
| error_description             | mediumtext  | YES  |     | NULL    |                |
| error_code                    | text        | YES  |     | NULL    |                |
| create_time                   | datetime    | NO   |     | NULL    |                |
| create_by                     | int         | NO   | MUL | NULL    |                |
| change_time                   | datetime    | NO   |     | NULL    |                |
| change_by                     | int         | NO   | MUL | NULL    |                |
| dbcrud_uuid                   | varchar(36) | YES  | UNI | NULL    |                |
+-------------------------------+-------------+------+-----+---------+----------------+
15 rows in set (0,00 sec)

Please find an example for an error here:

Code: Select all

$ bin/otrs.Console.pl Admin::OAuth2TokenManagement::ListTokenInformation
Token config 'ExchangeOnline' (valid, ID 2)
[Mon Jan 23 08:05:46 2023] otrs.Console.pl: DBD::mysql::st execute failed: Unknown column 'token_config_id' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 695.
ERROR: OTRS-otrs.Console.pl-Admin::OAuth2TokenManagement::ListTokenInformation-00 Perl: 5.30.0 OS: linux Time: Mon Jan 23 09:05:46 2023

 Message: Unknown column 'token_config_id' in 'field list', SQL: '
        SELECT
            authorization_code, change_by, change_time, create_by, create_time, error_code, error_description, error_message, id, refresh_token, refresh_token_expiration_date, token, token_config_id, token_expiration_date
        FROM
            oauth2_token WHERE token_config_id = ? LIMIT 1'

 Traceback (33865):
   Module: Kernel::System::DBCRUD::DataGet Line: 653
   Module: Kernel::System::Console::Command::Admin::OAuth2TokenManagement::ListTokenInformation::Run Line: 60
   Module: (eval) Line: 461
   Module: Kernel::System::Console::BaseCommand::Execute Line: 455
   Module: Kernel::System::Console::InterfaceConsole::Run Line: 81
   Module: bin/otrs.Console.pl Line: 37

        No token record found for this token config.

Token config 'OTRS' (valid, ID 1)
[Mon Jan 23 08:05:46 2023] otrs.Console.pl: DBD::mysql::st execute failed: Unknown column 'token_config_id' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 695.
ERROR: OTRS-otrs.Console.pl-Admin::OAuth2TokenManagement::ListTokenInformation-00 Perl: 5.30.0 OS: linux Time: Mon Jan 23 09:05:46 2023

 Message: Unknown column 'token_config_id' in 'field list', SQL: '
        SELECT
            authorization_code, change_by, change_time, create_by, create_time, error_code, error_description, error_message, id, refresh_token, refresh_token_expiration_date, token, token_config_id, token_expiration_date
        FROM
            oauth2_token WHERE token_config_id = ? LIMIT 1'

 Traceback (33865):
   Module: Kernel::System::DBCRUD::DataGet Line: 653
   Module: Kernel::System::Console::Command::Admin::OAuth2TokenManagement::ListTokenInformation::Run Line: 60
   Module: (eval) Line: 461
   Module: Kernel::System::Console::BaseCommand::Execute Line: 455
   Module: Kernel::System::Console::InterfaceConsole::Run Line: 81
   Module: bin/otrs.Console.pl Line: 37

        No token record found for this token config.

$

As you see it is referring to the column "token_config_id" which does not exist.

[skullz]

It supposed to be token_config_id

https://github.com/znuny/Znuny/blob/242 ... les.pm#L89

[zzz]

Hello Pascal,

Just try to change the column manually to token_config_id. I still don't see any hint why that happened, and it's the first time I see this, but that could be enough to get your system going again.

Get sure to delete the cache:

Code: Select all

bin/otrs.Console.pl Maint::Cache::Delete

— Emin

[pswarz]

Hi everyone,

thank you for your input.
I'll give it a try this evening and let you know if this has solved the issue!

[pswarz]

Hi everyone,

altering the column "config_id" to "token_config_id" in oauth2_token table did the job.
I was able to create OAUTH2-Token.

However if I try to fetch Mails with OAUTH2 and IMAPTLS i receive this error now:

Code: Select all

$ bin/otrs.Console.pl Maint::PostMaster::MailAccountFetch

Spawning child process to fetch incoming messages from mail accounts...

outlook.office365.com (IMAPTLS)...
IMAPTLS: Connection to outlook.office365.com closed.

ERROR: OTRS-otrs.Console.pl-Maint::PostMaster::MailAccountFetch-00 Perl: 5.30.0 OS: linux Time: Mon Jan 23 19:39:05 2023

 Message: Error while retrieving the messages 'IMAPTLS': Could not select:


 Traceback (22510):
   Module: Kernel::System::MailAccount::IMAP::_Fetch Line: 347
   Module: Kernel::System::MailAccount::IMAP::Fetch Line: 157
   Module: Kernel::System::MailAccount::MailAccountFetch Line: 649
   Module: (eval) Line: 171
   Module: Kernel::System::Console::Command::Maint::PostMaster::MailAccountFetch::Run Line: 163
   Module: (eval) Line: 461
   Module: Kernel::System::Console::BaseCommand::Execute Line: 455
   Module: Kernel::System::Console::InterfaceConsole::Run Line: 81
   Module: bin/otrs.Console.pl Line: 37

outlook.office365.com (IMAPTLS)...
IMAPTLS: Connection to outlook.office365.com closed.

ERROR: OTRS-otrs.Console.pl-Maint::PostMaster::MailAccountFetch-00 Perl: 5.30.0 OS: linux Time: Mon Jan 23 19:39:08 2023

 Message: Error while retrieving the messages 'IMAPTLS': Could not select:


 Traceback (22510):
   Module: Kernel::System::MailAccount::IMAP::_Fetch Line: 347
   Module: Kernel::System::MailAccount::IMAP::Fetch Line: 157
   Module: Kernel::System::MailAccount::MailAccountFetch Line: 649
   Module: (eval) Line: 171
   Module: Kernel::System::Console::Command::Maint::PostMaster::MailAccountFetch::Run Line: 163
   Module: (eval) Line: 461
   Module: Kernel::System::Console::BaseCommand::Execute Line: 455
   Module: Kernel::System::Console::InterfaceConsole::Run Line: 81
   Module: bin/otrs.Console.pl Line: 37

Done.

Any ideas?

[zzz]

Does the user who created the token have access to the mailbox?