Install ODBC connectors

Oracle and MySQL ODBC connectors are only supported on Linux and Solaris platforms.

Install MySQL ODBC connector on Linux

Create the database and grant user privileges. On the host where MySQL server is installed, run:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.45 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
            
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
            
Database changed
mysql> create database pbtest;
Query OK, 1 row affected (0.00 sec)
            
mysql> create user 'testuser'@'locahost' identified by '<passwd>';
Query OK, 0 rows affected (0.00 sec)
            
mysql> grant all on pbtest.* to 'testuser' identified by '<passwd>';
Query OK, 0 rows affected (0.00 sec)
            
mysql> grant all on pbtest.* to 'root' identified by ‘<passwd>';
Query OK, 0 rows affected (0.00 sec)
    
mysql> ^DBye

The steps below are to be done on the log server, where only MySQL ODBC connector needs to be installed. The example below is from a CentOS 6 system:
```
# yum install mysql-connector-odbc
```

ℹ️
Note
mysql-connector-odbc version 5.3.14 is not supported.

In pb.settings set:

eventdestinations authevt=odbc=MySQL
odbcinidir  /opt/pbul/etc

If you are using mysql-connector-odbc version 5.3.x with SSL enabled on MySQL server or mysql-connector-odbc version 8.0.x (with or without SSL), in pb.settings file, you need to configure the setting loadssllibs to yes.

cat /etc/pb.settings | grep loadssllibs
loadssllibs                         yes

Copy /etc/odbcinst.ini to /opt/pbul/etc.
Make sure the libraries are set correctly (if 64-bit machine, driver64 and setup64).

✅
Example

[MySQL] 
Description= ODBC for MySQL xml:space="preserve">
Driver= /usr/lib/libmyodbc5.so
Setup= /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64= /usr/lib64/libodbcmyS.so
FileUsage = 1
UsageCount = 3
trace = no
tracefile = stderr

Create /opt/pbul/etc/odbc.ini:

[MySQL]
Description = ODBC for MySQL
Driver = MySQL
server = <ip-address>
port = 3306
user = root
password = <passwd>
database  = pbtest
trace = no
tracefile = stderr

ℹ️
Note
In the file above, is the IP address of the host where the database was created (in step 1). is the root password as set up on that host.

On some Linux platforms, such as Debian, you might also need to add the following highlighted line to /etc/systemd/pblighttpd.service. In the example below, /usr/local/unixODBC is where unixODBC was installed, and /usr/local/MySQL/lib is where the MySQL libraries libmyodbc5.so is located:

✅
Example

# cat /etc/systemd/system/pblighttpd.service
[Unit]
Description=BeyondTrust PowerBroker REST services
After=network.target
        
[Service]
ExecStart=/usr/lib/beyondtrust/pb/rest/sbin/pblighttpd-svc
ExecReload=/bin/kill -HUP $MAINPIDEnvironment="LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/usr/local/unixODBC/lib:/usr/local/MySQL/lib:"

[Install]
WantedBy=multi-user.target

Restart pblighttpd.
Run pbrun and verify using:
```
# pblog --odbc -f MySQL
```
Check pbrest.log and other logs to make sure there is no error.

Install MySQL ODBC on Solaris

The MySQL ODBC connector can be installed on Solaris 10 and 11 (sparc and x86).

To install the database/server, follow the MySQL instructions (or see above on Linux).

ℹ️
Note
PMUL binaries are 32-bit. You must ensure a 32-bit version of ODBC is installed on Solaris 10 and 11.

Install the MySQL package (output of the command is in the attached file):

# pkgadd -d http://get.opencsw.org/now
# /opt/csw/bin/pkgutil -U -u -y
# /opt/csw/bin/pkgutil -y -i myodbc

ℹ️
Note
Mysql-connector-odbc version 5.3.14 is not supported.

In pb.settings set:

eventdestinations authevt=odbc=MySQL
odbcinidir  /opt/pbul/etc

cat /etc/pb.settings | grep loadssllibs
loadssllibs                         yes

Configure Odbc.ini:

[MySQL]
Description = ODBC for MySQL
Driver = MySQL
server = <ip_of_MYSQL_host>
port = 3306
user = root
password = <passwd>
database = <database>

Create /opt/pbul/etc/odbc.ini:

[MySQL]
Description  = ODBC for MySQL
Driver = /opt/csw/lib/libmyodbc3.so
FileUsage = 1
UsageCount = 3
trace = no
tracefile = stderr

Set LD_LIBRARY_PATH to:

LD_LIBRARY_PATH=/opt/csw/lib:/usr/lib:/lib:/usr/local/lib

Restart pblighttpd.

Install Oracle ODBC connector on Linux

On the Oracle database/server, log in to the server where the Oracle database is installed as root, then su to oracle, and create your user (replace by your name) as follows:

✅
Example

# su - oracle
Last login: Wed Sep  4 17:28:54 PDT 2019 from jurel.pbse.lab on pts/0
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 5 15:21:24 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user mdavis identified by mdavis;
User created.
SQL> GRANT CONNECT,RESOURCE,DBA to mdavis;
Grant succeeded.
SQL> GRANT UNLIMITED TABLESPACE to mdavis;
Grant succeeded.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Run the following steps on the log server, where only the Oracle ODBC connector needs to be installed. Steps shown below are on a CentOS 6 system:

Download Oracle InstantClient (basic and ODBC) from https://www.oracle.com/database/technologies/instant-client/downloads.html.

Download the file to a directory. For example, /tmp/Oracle.

On RHEL 6, install oracle-instantclient-basic… and oracle-instantclient-odbc v18.5. Version 19.3 does not work on RHEL 6, but works on RHEL 7:

# cd /tmp/Oracle
# yum install oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

Install unixODBC needed by Oracle ODBC:

# yum install unixODBC

unixODBC is installed in /usr/lib64.

In pb.settings set:

eventdestinations authevt=odbc=Oracle
odbcinidir  /opt/pbul/etc

Create /etc/tnsnames.ora:

# cat /etc/tnsnames.ora
ORCLCDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=moonfish)(PORT=1521)))(CONNECT_DATA=(SID=ORCLCDB)))

ℹ️
Note
The tnsnames.ora file needs to be in /etc. Copying it elsewhere and setting environment variable TNS_ADMIN to the new directory does not work.

Create /opt/pbul/etc/odbcinst.ini:

[oracle]
Description = Oracle 18
Driver      = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
ServerName  = ORCLCDB

Create /opt/pbul/etc/odbc.ini:

[oracle]
Description = Oracle
Driver      = oracle
DSN         = ORCLCDB
ServerName  = ORCLCDB
UserID      = mdavis
Password    = mdavis

Use the user name created in step 1.

The following library is required for pblighttpd to connect to the Oracle ODBC: /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1. With Oracle ODBC, some of the paths of the dependent libraries are not set and setting LD_LIBRARY_PATH doesn’t work. The following error is displayed:

Sep  6 09:08:18 [12974] 6339.32 Failed to connect to ODBC DSN 'Oracle' - [unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1' : file not found

⚠️
Important
Solution 1
Set the library path system wide in ld.so. For that, create a file oracle-instantclient.conf with the path in it, and do the following:
# cat oracle-instantclient.conf
/usr/lib/oracle/18.5/client64/lib
# cp oracle-instantclient.conf /etc/ld.so.conf.d
# ldconfig
Verify it’s loaded:
# ldconfig -p|grep sqora
libsqora.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
Solution 2
Use patchelf to set the path for all the Oracle libraries.
If patchelf is not on your machine, download patchelf rpm from [https://pkgs.org/search/?q=patchelf](https://pkgs.org/search/?q=patchelf).
Do the following:
# service pblighttpd stop
# cd /usr/lib/oracle/18.5/client64/lib
# for i in *.so*
do
patchelf --set-rpath /usr/lib/oracle/18.5/client64/lib $i
done
# service pblighttpd start
Starting pblighttpd-svc service.

ℹ️
Note
You might prefer the second solution because the lib path is set only for the Oracle libraries and is not system-wide.

To use oracle SSL authentication, create sqlnet.ora file, update TNS_NAMES configurations for pblighttpd and restart pblighttpd.

Create /etc/sqlnet.ora.

#cat /etc/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_SERVER_DN_MATCH = no
WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /home/oracle/app/oracle/wallet)) )

ls /etc/*.ora
/etc/sqlnet.ora  /etc/tnsnames.ora

If pblighttpd is running as deamon: edit the /etc/init.d/pblighttpd file.

Add an entry to export the TNS_ADMIN environmental variable.

✅
Example

cat /etc/init.d/pblighttpd | grep TNS_ADMIN
TNS_ADMIN=/etc/
export PBLIGHTTPD_PRG PBLIGHTTPD_ROOT PBLIGHTTPD_PID PBLIGHTTPD_BIN PBLIGHTTPD_CONF RETVAL PATH TNS_ADMIN

If it is a service, edit /etc/systemd/system/pblighttpd.service and add the text below the [Service] tag.

✅
Example

cat /etc/systemd/system/pblighttpd.service
[Unit]
Description=BeyondTrust PowerBroker REST services
After=network.target
[Service]
ExecStart=/usr/lib/beyondtrust/pb/rest/sbin/pblighttpd-svc
ExecReload=/bin/kill -HUP $MAINPID
Environment=TNS_ADMIN=/etc/
[Install]
WantedBy=multi-user.target

ℹ️
Note
TNS_ADMIN is the location from where odbc drivers will read sqlnet.ora and tnsnames.ora.

Restart pblighttpd.

Run pbrun and verify using:

# pblog --odbc -f Oracle

ℹ️
Note
Export TNS_NAME=/etc/ to use oracle SSL connections.

Install Oracle ODBC connector on Solaris

The following install was on x86 Solaris 10. The steps are similar on a Sparc Solaris 10 and on Solaris 11. Download the appropriate ZIP files (instantclient-basic and instantclient-odbc) to a directory. For example, /tmp/Oracle.

For Solaris Sparc 10, the 2 hosts pbul-qa-spsol11z-01 and pbul-qaspsol11z-02 already have Oracle ODBC installed. When installing EPM-UL, you must create odbc.ini in /opt/pbul/etc.

ℹ️
Note
PMUL binaries are 32-bit. You must ensure 32-bit versions of unixODBC and Oracle ODBC connectors are installed on Solaris 10 and 11.

Step 1 is the same as for Linux. Use the existing Oracle database/server and create your user.

Run the following steps on the log server:

# pkgadd -d http://get.opencsw.org/now
# export PATH=$PATH:/opt/csw/bin
# pkgutil -U -u -y
# pkgutil -i -y unixodbc
# mkdir /opt/oracleODBC
# cd /opt/oracleODBC/
# unzip /tmp/Oracle/instantclient-basic-solaris.x32-18.3.0.0.0dbru.zip
# unzip /tmp/Oracle/instantclient-odbc-solaris.x32-18.3.0.0.0dbru.zip
# unzip /tmp/Oracle/instantclient-sqlplus-solaris.x32-18.3.0.0.0dbru.zip

In pb.settings set:

eventdestinations authevt=odbc=Oracle
odbcinidir  /opt/pbul/etc

Create /etc/tnsnames.ora:

# cat /etc/tnsnames.ora
ORCLCDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=moonfish)(PORT=1521)))(CONNECT_DATA=(SID=ORCLCDB)))

ℹ️
Note
The tnsnames.ora file needs to be in /etc. Copying it elsewhere and setting TNS_ADMIN to the new directory does not work.

Create /opt/pbul/etc/odbcinst.ini:

[oracle]
Description = Oracle 18
Driver      = /opt/oracleODBC/instantclient_18_3/libsqora.so.18.1
ServerName  = ORCLCDB

Create /opt/pbul/etc/odbc.ini:

[oracle]
Description = Oracle
Driver      = oracle
DSN         = ORCLCDB
ServerName  = ORCLCDB
UserID      = mdavis
Password    = mdavis

Use the user name you created in step 1.

Set LD_LIBRARY_PATH and TNS_ADMIN for the pblighttpd service:

svccfg -s application/security/pblighttpd setenv LD_LIBRARY_PATH /opt/oracleODBC/instantclient_18_3:/opt/csw/lib
svccfg -s application/security/pblighttpd setenv TNS_ADMIN /etc
svcadm refresh pblighttpd
svcadm restart pblighttpd

Set LD_LIBRARY_PATH in the environment for pblog to work. Add the following to /etc/profile:

LD_LIBRARY_PATH="/usr/lib:/lib:/opt/oracleODBC/instantclient_18_3:/opt/csw/lib"
export LD_LIBRARY_PATH
TNS_ADMIN=/etc
export TNS_ADMIN

⚠️
Important
Make sure /usr/lib is first in LD_LIBRARY_PATH.

To use oracle SSL authentication, create sqlnet.ora file, update TNS_NAMES configurations for pblighttpd and restart pblighttpd.

Create /etc/sqlnet.ora.

#cat /etc/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_SERVER_DN_MATCH = no
WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /home/oracle/app/oracle/wallet)) )

ls /etc/*.ora
/etc/sqlnet.ora  /etc/tnsnames.ora

Set the environment variable TNS_ADMIN=/etc/ to pblighttpd service.

Use the below commands to set the variable:

svccfg -s pblighttpd setenv TNS_NAME /etc/
svcadm refresh pblighttpd

Validate the configuration using below command:

svcprop -p start/environment pblighttpd

ℹ️
Note
TNS_ADMIN is the location from where ODBC drivers read sqlnet.ora and tnsnames.ora.

Restart pblighttpd.

Run pbrun and verify using:

# pblog --odbc -f Oracle

ℹ️
Note
Export TNS_ADMIN=/etc if you are using oracle SSL connections.

Check pbrest.log and other logs to make sure there is no error.

⚠️
Important

Set LD_LIBRARY_PATH with /opt/csw/lib:

When LD_LIBRARY_PATH is set to contain /opt/csw/lib without /usr/lib first, and we either validate or import pblighttpd-smf.xml, svccfg segfault on Solaris:

# LD_LIBRARY_PATH=/opt/oracleODBC/instantclient_18_3:/opt/csw/lib
# svccfg validate /usr/lib/beyondtrust/pb/rest/etc/pblighttpd-smf.xml
Segmentation Fault (core dumped)
#
# LD_LIBRARY_PATH=/opt/oracleODBC/instantclient_18_3
# svccfg validate /usr/lib/beyondtrust/pb/rest/etc/pblighttpd-smf.xml

# LD_LIBRARY_PATH=/usr/lib:/opt/oracleODBC/instantclient_18_3:/opt/csw/lib
        # svccfg validate /usr/lib/beyondtrust/pb/rest/etc/pblighttpd-smf.xml

The reason for the segfault is a library conflict in /usr/lib and /opt/csw/lib.

# ldd /usr/sbin/svccfg
libxml2.so.2 =>  /usr/lib/libxml2.so.2
libscf.so.1 =>   /usr/lib/libscf.so.1
libl.so.1 =>     /usr/lib/libl.so.1
libuutil.so.1 =>         /usr/lib/libuutil.so.1
libumem.so.1 =>  /usr/lib/libumem.so.1
libdoor.so.1 =>  /usr/lib/libdoor.so.1
libmd5.so.1 =>   /usr/lib/libmd5.so.1
libtecla.so.1 =>         /usr/lib/libtecla.so.1
libc.so.1 =>     /usr/lib/libc.so.1
libpthread.so.1 =>       /usr/lib/libpthread.so.1
libz.so.1 =>     /usr/lib/libz.so.1
libm.so.2 =>     /usr/lib/libm.so.2
libsocket.so.1 =>        /usr/lib/libsocket.so.1
libnsl.so.1 =>   /usr/lib/libnsl.so.1
libgen.so.1 =>   /usr/lib/libgen.so.1
libcurses.so.1 =>        /usr/lib/libcurses.so.1
libmp.so.2 =>    /usr/lib/libmp.so.2
libmd.so.1 =>    /usr/lib/libmd.so.1
/platform/sun4v/lib/libc_psr.so.1
/lib/libm/libm_hwcap1.so.2
/platform/sun4v/lib/libmd_psr.so.1

If LD_LIBRARY_PATH does not have /usr/lib first, we get:

# LD_LIBRARY_PATH=/opt/oracleODBC/instantclient_18_3:/opt/csw/lib
# ldd /usr/sbin/svccfg
libxml2.so.2 =>  /usr/lib/libxml2.so.2
libscf.so.1 =>   /lib/libscf.so.1
libl.so.1 =>     /usr/lib/libl.so.1
libuutil.so.1 => /lib/libuutil.so.1
libumem.so.1 =>  /lib/libumem.so.1
libdoor.so.1 =>  /lib/libdoor.so.1
libmd5.so.1 =>   /lib/libmd5.so.1
libtecla.so.1 => /usr/lib/libtecla.so.1
libc.so.1 =>     /lib/libc.so.1
libpthread.so.1 =>  /lib/libpthread.so.1
libz.so.1 =>     /opt/csw/lib/libz.so.1
libz.so.1 (SUNW_1.1) =>  (version not found)
libm.so.2 =>     /lib/libm.so.2
libsocket.so.1 => /lib/libsocket.so.1
libnsl.so.1 =>   /lib/libnsl.so.1
libgen.so.1 =>   /lib/libgen.so.1
libcurses.so.1 => /lib/libcurses.so.1>
libmp.so.2 =>    /lib/libmp.so.2
libmd.so.1 =>    /lib/libmd.so.1
/platform/sun4v/lib/libc_psr.so.1
/lib/libm/libm_hwcap1.so.2
/platform/sun4v/lib/libmd_psr.so.1

ℹ️
Note
For more information, see Install Oracle ODBC Connector on Linux.

Install ODBC connectors

Install MySQL ODBC connector on Linux

ℹ️
Note

✅
Example

ℹ️
Note

✅
Example

Install MySQL ODBC on Solaris

ℹ️
Note

ℹ️
Note

Install Oracle ODBC connector on Linux

✅
Example

ℹ️
Note

⚠️
Important

Solution 1

Solution 2

ℹ️
Note

✅
Example

✅
Example

ℹ️
Note

ℹ️
Note

Install Oracle ODBC connector on Solaris

ℹ️
Note

ℹ️
Note

⚠️
Important

ℹ️
Note

ℹ️
Note

⚠️
Important

ℹ️
Note

Install MySQL ODBC connector on Linux

ℹ️Note

✅Example

ℹ️Note

✅Example

Install MySQL ODBC on Solaris

ℹ️Note

ℹ️Note

Install Oracle ODBC connector on Linux

✅Example

ℹ️Note

⚠️Important

Solution 1

Solution 2

ℹ️Note

✅Example

✅Example

ℹ️Note

ℹ️Note

Install Oracle ODBC connector on Solaris

ℹ️Note

ℹ️Note

⚠️Important

ℹ️Note

ℹ️Note

⚠️Important

ℹ️Note

ℹ️
Note

✅
Example

ℹ️
Note

✅
Example

ℹ️
Note

ℹ️
Note

✅
Example

ℹ️
Note

⚠️
Important

ℹ️
Note

✅
Example

✅
Example

ℹ️
Note

ℹ️
Note

ℹ️
Note

ℹ️
Note

⚠️
Important

ℹ️
Note

ℹ️
Note

⚠️
Important

ℹ️
Note