db#oci: Access Database via tnsnames.ora / LDAP Naming Services

[TQQEU]

We have an Oracle database in addition to our Icinga2/Icingaweb2 installation, from which we import data using the Icinga-Director.

Here is an anonymized example configuration from icingaweb2-resources.ini:

[oracle_#REMOVED#_PROD]
type = "db"
db = "oci"
port = "1521"
host = "#REMOVED#"
dbname = "#REMOVED#/#REMOVED#.PROD"
username = "#REMOVED#"
password = "#REMOVED#"
charset = "utf8"
use_ssl = "0"

It's possible to access the database via the command line by entering something like "sqlplus USER@DB_ALIAS" if the ALIAS is defined in the tnsnames.ora file (see https://www.orafaq.com/wiki/Tnsnames.ora).
Another option is the use of LDAP naming services (see https://docs.oracle.com/cd/E23824_01/html/821-1455/overview-1.html).

We now need to be able to use the configuration via tnsnames.ora or LDAP naming services - so the connect string will consist only of the service name.
The specification of a port and server name should be optional - if they are not present, resolution should occur through tnsnames.ora (if available) or LDAP naming services.

OCI8, the php lib used (https://www.php.net/manual/en/book.oci8.php) should fundamentally supports this, but currently not the Director.

Our Environment

• Director version (System - About): 1.10.2
• Icinga Web 2 version and modules (System - About): 2.11.4
• Icinga 2 version (icinga2 --version): 2.13.5-1
• Operating System and version: RHEL 8.5.0-10 / Linux Kernel 4.18.0
• Webserver, PHP versions: Apache 2.4.37 / PHP 8.0.13

[lippserd]

@TQQEU: Can you please try switching the database type/driver from oci to oracle?

I also created PR #5063 which no longer renders host mandatory for oci database resources.

[TQQEU]

Hi,

we recently checked the functionality of this new feature, and found out, that while "host" is now optional for oci database resources (5063), the setting do not work out to create an actual database-connection:

Neither tnsnames.ora nor the use of LDAP naming services is working here, as we tested both with the same results (sqlplus does work by either way perfectly).

[lippserd]

Hi @TQQEU,

Where is your tnsnames.ora file located? I did a quick test and PHP does this in order to find the file:

access("~/.tnsnames.ora", F_OK)     = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/21/client64/lib/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/21/client64/lib/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

Best regards,
Eric

[TQQEU]

Hi @lippserd,

sorry for the delay.

our tnsnames.ora is located in /opt/oracle/base/tnsadmin/tnsnames.ora.
Might you share your PHP-based testing method?

Another file ldap.ora in the same directory defines how LDAP is used at our environement.
I checked: We have the necessary file- and directory-rights to allow access and read both files "for everyone".

[slalomsk8er]

@TQQEU did you set TNS_ADMIN to /opt/oracle/base/tnsadmin/ in the environment of your PHP processes?

[TQQEU]

Hi @slalomsk8er,
Hi @lippserd,

I'm still not sure if this was the case before, but I made sure that TNS_ADMIN is now set via the web server configuration and therefore should now be set for PHP, too.

The result has not changed so far.

I would still like to test where PHP is looking for things. It would be very helpful if you could share your approach from #5062 (comment).

[slalomsk8er]

@TQQEU the output in #5062 (comment) looks like it comes from strace.

[lippserd]

strace php -r 'new PDO("oci:dbname=sid", "username", "password", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);' 2>&1 | grep tnsnames

Note that this is CLI and not web context, so actual paths may differ depending on the environment variables.

Proof that setting TNS_ADMIN works:

TNS_ADMIN=/opt/oracle/base/tnsadmin strace php -r 'new PDO("oci:dbname=sid", "username", "password", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);' 2>&1 | grep tnsnames

For web you could set the env variable in php-fpm's www pool config:

env[TNS_ADMIN] = /opt/oracle/base/tnsadmin

[TQQEU]

Hi @lippserd,

Many thanks for your help!

Today I managed to get php to use the configuration file, which (contrary to my original expectations) did not work via the web server configuration, but via the php-fpm configuration.

I can now confirm: There is currently no program error here, only a configuration problem. We're happy to close this issue.

[lippserd]

Hi @TQQEU,

Glad it worked and thanks for the feedback. I think it might be worth to mention this environment variable somewhere in our docs: #5199

Best regards,
Eric