Configure OTOBO with Oracle to use wallet, is it possible?

[Alvaro Cordero Retana]

Hello,

I have a request to change the way otobo connects to oracle DB to use encription of the otobo password via Oracle Wallet.

Is that possible, has anyone configure it that way?

Also when configuring a password policy for otobo user to lock after 3 failed logins, it gets locked inmediately, even if it connects (I meanm when the policy is not in place the system works fine, no failed connections are seen.

Regards

[bes]

Hi Alvaro,

this is the first time I heard of anybody running OTOBO with Oracle. What are your experiences? Did you have to make any adaptions? As a OTOBO developer I’m not aware of any hindrances of running OTOBO under Oracle, but I also know that we do not explictitly test that case.

I don’t use Oracle Wallet myself. But I have found https://www.freelists.org/post/oracle-l/perl-DBDOracle-and-external-password-store,4    According to that link, you only have specify the TNS service name in the database DSN and pass user and password as empty strings. This needs to be configured in the file _Kernel/Config.pm.

Something like:

$Self->{DatabaseUser} = '';

$Self->{DatabasePw} = 'some-pass';

$Self->{DatabaseDSN} = "DBI:Oracle://MY_SERVICE_NAME";

Of course it makes sense to test first with sqlplus whether connection with Oracle Wallet works in the first place.

Best regards.

Bernhard

 

[Alvaro Cordero Retana]

Hello Bernhard,

Thank you for your reply, I will look at that link and see how it goes.

In regards of running OTOBO with Oracle, we had couple of installations of OTRS running Oracle so we did the migration process. It was kind of tricky, we had to remove few empty registers and indexes, especially in article and article_data mime, but at the end it worked fine.. It tooks about 6 hours to complete the entire process on a DB 35 GB size, of course it was a development system.

After the migration it did not convert some sequences and triggers so those had to be converted manually by the DB Admin, those showed up while running the console CheckDB command.

Error: The following sequences with possible wrong names have been found. Please rename them manually.
se_article_data_otrs_chat
se_groups
Error: The following triggers with possible wrong names have been found. Please rename them manually.
se_article_data_otrs_chat_t
se_groups_t

It also becomes sensitive to some policies for example if it has a policy to lock the DB user after some failed login attempts, it actually locks it almost immediately, so we are still investigating that part, in the mean while that policy was disabled.

In regards to wallet configuration, we will have a test session tomorrow and I will let you know the outcome of it.

[Alvaro Cordero Retana]

Hello,

I have an update.

We managed to make wallet work and be able to connect to the database with sqlplus and also by creating a script in perl to make test the connection, so perl is set to work as expected.

With Otobo it is different, we tried modifying the config to not pass the user name and password to the connection string. It uses the wallet configuration and gets the user from there, but returns error on user name or password.

The perl script is configured as follow

#/bin/perl

use DBI;

my $dbh = DBI->connect(‚dbi:Oracle:otobo_wallet‘,“,“,{RaiseError=>1,PrintError=>1}) || die( $DBI::errstr . „\n
“ );
if ($dbh ne undef) {
my $sth = $dbh->prepare(qq{SELECT ‚Connected as: ‚||user FROM DUAL});
$sth->execute();
print $sth->fetchrow_array().“\n“;
$sth->finish();
$dbh->disconnect || warn $dbh->errstr;
}

Could it be something to change in OTOBO’s Module DB.pm?

 

Regards

[bes]

Hi Alvaro,

I did a quick check on a fresh installation under Docker. There I set user and password to the empty string und dumped the connection parameters just before the connection. The empty strings were passed thru without any changes. So there must be another reason why the connection did not work.

Did the error message state a reason for the failed connect? Did you have to set special environment variables for making your test script work?

Best regards,

Bernhard

 

[Alvaro Cordero Retana]

Hello, we did few more tests and some changes in the wallet configuration and now we are able to connect to the database using the wallet configuration with no issues using the console command

But while this works fine, the application continues to give errors related to Name Resolution.  So it seems to me that while otobo can connect apache requires something additional to be able to work.

Any ideas.

[bes]

Hi Alvaro,

I still think that some environment variable is set differently when running as the web server. I propose that you take a look at the dump of the environment after the failed connect.

[bes]

Hi again,

the wordfence checks in this forum were apparently too strict. So were are experimenting with the setting. So, here is the previous patch again, but as text instead of a screenshot.

 

bes:~/devel/OTOBO/otobo (rel-10_1)$ git diff
diff --git a/Kernel/System/DB.pm b/Kernel/System/DB.pm
index 083166bf61..ab7a35e335 100644
--- a/Kernel/System/DB.pm
+++ b/Kernel/System/DB.pm
@@ -308,6 +308,13 @@ sub Connect {
Message => $DBI::errstr,
);

+ use Data::Dumper;
+ $Kernel::OM->Get('Kernel::System::Log')->Log(
+ Caller => 1,
+ Priority => 'Error',
+ Message => Dumper(\%ENV),
+ );
+
return;
}

bes:~/devel/OTOBO/otobo (rel-10_1)$

[Alvaro Cordero Retana]

Hello,  I did add the debug lines and got the environment vars, although we passed them into apache config still could not connect

Sep  9 11:54:35 dev-OTOBO OTOBO-CGI-10[4119902]: [Error][Kernel::System::Web::InterfaceAgent::Content][Line:207]: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

The vars sent are as follow

Message: $VAR1 = {
‚DOCUMENT_ROOT‘ => ‚/var/www/html‘,
‚QUERY_STRING‘ => “,
‚REMOTE_ADDR‘ => ‚x.x.x.x‘,
‚HTTP_USER_AGENT‘ => ‚Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:104.0) Gecko/20100101 Firefox/104.0‘,
‚CONTEXT_PREFIX‘ => ‚/otobo/‘,
‚UNIQUE_ID‘ => ‚Yxt92–OC6PosZZhJxlCxAAAAAA‘,
‚SERVER_PORT‘ => ‚443‘,
‚HTTP_ACCEPT_LANGUAGE‘ => ‚es,es-ES;q=0.8,en-US;q=0.5,en;q=0.3‘,
‚PATH‘ => ‚/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin‘,
‚HTTP_UPGRADE_INSECURE_REQUESTS‘ => ‚1‘,
‚SCRIPT_FILENAME‘ => ‚/opt/otobo/bin/cgi-bin/index.pl‘,
‚SERVER_PROTOCOL‘ => ‚HTTP/1.1‘,
‚REQUEST_METHOD‘ => ‚GET‘,
‚HTTP_SEC_FETCH_MODE‘ => ’navigate‘,
‚HTTP_ACCEPT_ENCODING‘ => ‚gzip, deflate, br‘,
‚MOD_PERL‘ => ‚mod_perl/2.0.12‘,
‚HTTP_HOST‘ => ‚x.x.x.x‘,
‚MOD_PERL_API_VERSION‘ => ‚2‘,
‚SERVER_SOFTWARE‘ => ‚Apache/2.4.37 (Red Hat Enterprise Linux) OpenSSL/1.1.1k mod_perl/2.0.12 Perl/v5.26.3‘,
‚LD_LIBRARY_PATH‘ => ‚/usr/lib/oracle/19.14/client64/lib‘,
‚ORACLE_HOME‘ => ‚/usr/lib/oracle/19.14/client64‘,
‚HOME‘ => undef,
‚CONTEXT_DOCUMENT_ROOT‘ => ‚/opt/otobo/bin/cgi-bin/‘,
‚HTTP_SEC_FETCH_USER‘ => ‚?1‘,
‚HTTPS‘ => ‚on‘,
‚SERVER_ADDR‘ => ‚x.x.x.x‘,
‚HTTP_SEC_FETCH_DEST‘ => ‚document‘,
‚GATEWAY_INTERFACE‘ => ‚CGI/1.1‘,
‚NLS_LANG‘ => ‚AMERICAN_AMERICA.AL32UTF8‘,
‚REQUEST_URI‘ => ‚/otobo/index.pl‘,
‚SERVER_SIGNATURE‘ => “,
‚HTTP_ACCEPT‘ => ‚text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8‘,
‚TNS_ADMIN‘ => ‚/usr/lib/oracle/19.14/client64‘,
‚ORACLE_BASE‘ => ‚/usr/lib/oracle‘,
‚SERVER_ADMIN‘ => ’soporte@gridshield.net‘,
‚REMOTE_PORT‘ => ‚60578‘,
‚HTTP_CONNECTION‘ => ‚keep-alive‘,
‚HTTP_SEC_FETCH_SITE‘ => ’none‘,
‚SCRIPT_NAME‘ => ‚/otobo/index.pl‘,
‚SERVER_NAME‘ => ‚x.x.x.x‘,
‚REQUEST_SCHEME‘ => ‚https‘
};

[Author]

Posts