--- title: "SQL Toolkit" description: "" url: "https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit/index.html" --- ## Overview The SQL Toolkit plugin executes database queries that can display the output of the query in the dataview and run custom queries to monitor database data. For more information about the SQL Toolkit plugin and configuration, see [SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html). This plugin can connect to and extract data from the following supported databases: - MySQL or MariaDB. - Microsoft SQL Server - Oracle - SAP ASE, formerly known as Sybase ASE. - PostgreSQL. - IBM DB2. For the complete list of supported database server versions, go to [Geneos Compatibility Matrix](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html). A typical view constructed using the SQL Toolkit plugin consists of a dataview whose columns and rows are entirely determined by the query. This works well for use cases that require periodical queries on databases, such as: - Security administrators who check and review audit logs recorded in the database server. - Service Level Managers who get alerted on application incidents. - Administrators who view the quality metrics inserted in the database servers. In addition, below is an example of SQL Toolkit dataview where you can see the headline variables: ![SQL Toolkit dataview](https://docscdn.itrsgroup.com/geneos/images/plug-ins/sql-toolkit-postgresql-dataview1-headlines-6-3.png) To connect to the database, the Netprobe requires third-party database client libraries to run the SQL Toolkit plugin. These libraries are usually included in the database installation.
Headline Description
queryStatus

displays the status of the query. The following are the possible values:

  • Initializing - displays when the plugin starts up.

  • Running - indicates that a query is currently executing.

  • Idle - waiting for the query to execute.

queryDuration Displays the time in seconds that the current query took on its last run. The time is set to 0 when the status is Idle.
rowsReturned Shows the number of rows returned in the dataview.
## Intended audience This guide is intended for experienced Geneos users who want to configure the SQL Toolkit plugin to execute database queries. As a user, you should be familiar with SQL or other database, and with the administration of Netprobe and DB libraries that are required to run the SQL Toolkit sampler. ## Prerequisites The following requirements must be met prior to the configuration and setup of the SQL Toolkit plugin: - You must use the supported versions of Active Console, Gateway, and Netprobe. For guidance, see [Supported Geneos versions in 6.x Compatibility Matrix](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html). - You must have the servers of the Active Console, Gateway, and Netprobe running on the supported operating systems. For guidance, see [Operating system support in 6.x Compatibility Matrix](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html). - You must connect your Active Console to the Gateway where this plugin is configured. - The Netprobe user must have a read-access permission to the monitored file. ## Configure the Netprobe and database client libraries Depending on what database your Netprobe needs to connect to, you may have to amend your environment to expose the appropriate libraries to the Netprobe. The Netprobe indirectly loads these libraries depending on what database the plugin has been configured to connect to. For more information on supported libraries for each platform, see [Required client libraries in Databases](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/databases/index.html#required-client-libraries). You must first configure your selected database and define its required variables in the Netprobe start script before you can set up your SQL Toolkit sampler in Gateway Setup Editor. The configuration steps for each database changes depending on the operating system you are using. **Note**  The Netprobe start script is found in `netprobe/templates` directory of your Netprobe package. ### Linux - Ensure the database libraries and paths are defined in the Netprobe start script. A Netprobe start script is where you define the required variables to run a Netprobe. - Log in to the Linux server where the Netprobe is running. - Use any Secure Shell or SSH client to access the server and to modify the Netprobe start script. ### Windows - Ensure the required Windows environment variables and libraries are defined in the system properties. - Log in the Windows server where the Netprobe is running. - Access the actual Windows server. ### AIX **Caution** Some IBM AIX hosts still use the `LIBPATH` variable when loading shared libraries. For backward compatibility, the path to the required libraries must be added to the `LIBPATH` variable. ### MySQL database For more information on supported MySQL libraries for each platform, see [Required client libraries in Databases](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/databases/index.html#required-client-libraries). **Note** The following authentication plugins are supported: - `mysql_native_password` - `caching_sha2_password` The `caching_sha2_password`authentication plugin can only be used with secure connections. To do this, the MySQL server should be configured to use secure connections, and the SSL mode in the SQL Toolkit configuration should be set to `Preferred`, `Required`, `Verify_ca`, or `Verify_identity`. #### Linux When MySQL is installed via packaged installation, it automatically adds `/usr/lib64/mysql` to the list of directories (`/etc/ld.so.conf.d`) where the dynamic linker searches for libraries. This way, even if you do not set `LD_LIBRARY_PATH`, the Netprobe can find the library in `/usr/lib64/mysql`. If you need to set `LD_LIBRARY_PATH`, follow these steps: 1. Locate the MySQL client library file. 2. Add these variables to the Netprobe start script using a text editor.
Variable Value
MYSQL_LIB export MYSQL_LIB=/usr/lib64/mysql
LD_LIBRARY_PATH export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB}
3. Add the **MYSQL\_LIB** variable. 4. Enter the path of the library file in the **MYSQL\_LIB** variable: ``` MYSQL_LIB=/usr/lib64/mysql ``` 5. Enter the **MYSQL\_LIB** in the **LD\_LIBRARY\_PATH** variable: ``` export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB} ``` 6. Save the changes to the Netprobe start script. 7. Exit the editor. 8. Restart the Netprobe. #### Windows 1. Modify the **Path** variable of the Windows host. 2. Locate the MySQL client library file – `libmysql.dll`. Below is an example that shows you where to locate the MySQL file:
Variable Value
Path <existing path>;C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib
``` C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib>dirVolume in drive C has no label.Volume Serial Number is 1846-3FFADirectory of C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib10/25/2017 04:59 PM ..07/18/2017 12:07 AM 4,879,360 libmysql.dll07/18/2017 12:07 AM 26,956 libmysql.libC:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib> ``` **Caution** If you are also using a Maria in your machine and added its path in the **Path** variable, then you must move the MySQL to a higher directory to avoid conflict. 3. Click **Start** on your computer. 4. Right-click **Computer**, and click **Properties**. 5. Click **Advanced system settings**. 6. Click **Environment Variables**. 7. Click the **Path** system variable. ![Path system variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path.png) 8. Click **Edit…** to append the path of the `libmysql.dll` file ![system variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path_edit.png) 9. Click **OK** to save the changes in the **Edit System Variable** screen. 10. Click **OK** to save the changes in the **Environment Variables** screen. 11. Restart the Netprobe. **Success** You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to [Connect to a MySQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-mysql-database). #### IBM AIX Run the following export command in your machine before running the Netprobe: ``` export LIBPATH=/opt/freeware/lib/gcc/powerpc-ibm-aix7.2.0.0/8/pthread:/opt/freeware/lib64 ``` If you are using Netprobe version 6.2.x. or older, then you must run the following additional export command: ``` export GENEOS_MYSQL_CLIENT_LIBRARY=libmysqlclient.a\(libmysqlclient.so.21\) ``` **Caution** Some IBM AIX hosts still use the `LIBPATH` variable when loading shared libraries. For backward compatibility, the path to the required libraries must be added to the `LIBPATH` variable. **Success** You have successfully defined the database library paths in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to a MySQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-mysql-database). ### Microsoft SQL Server database **Note** MS SQL is not supported in an ARM64 environment. Instead, MS SQL is designed to run on AMD64 or x64 processors. #### Linux 1. Install the MS SQL Server ODBC 18 driver by following the instructions in the Microsoft website. Select the instruction that matches your Linux version. 2. Execute this command to verify whether the installation is successful: ``` odbcinst -q -d -n "ODBC Driver 18 for SQL Server" ``` The command displays the message below once the installation is successful: ``` [ODBC Driver 18 for SQL Server] Description=Microsoft ODBC Driver 18 for SQL Server Driver==/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 UsageCount=1 ``` 3. Copy or symlink the `/etc/odbcinst.ini` to `/opt/microsoft/msodbcsql18/lib64` folder: ``` sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql18/lib64/odbcinst.ini ``` **Note** Symlink or symbolic link is a reference to another file or directory. 4. Add these variables to the Netprobe start script using a text editor. Follow the variables and their values in the table:
Variable Value
SQLSERVER_LIB /opt/microsoft/msodbcsql18/lib64
ODBCHOME ${SQLSERVER_LIB}
ODBCSYSINI ${SQLSERVER_LIB}
ODBCINSTINI odbcinst.ini
GENEOS_MSSQL_DRIVER 'ODBC Driver 18 for SQL Server'
LD_LIBRARY_PATH <Existing path>:${SQLSERVER_LIB}
5. Add the variables and their corresponding values: ``` export SQLSERVER_LIB=/opt/microsoft/msodbcsql18/lib64 export ODBCHOME=${SQLSERVER_LIB} export ODBCSYSINI=${SQLSERVER_LIB} export ODBCINSTINI=odbcinst.ini export GENEOS_MSSQL_DRIVER='ODBC Driver 18 for SQL Server' ``` The **SQLSERVERLIB** contains the path of the MS SQL Server client library file – `libmsodbcsql-18.2.so.1.1`. 6. Add the **SQLSERVER\_LIB** in the **LD\_LIBRARY\_PATH** variable: ``` export LD_LIBRARY_PATH=${NP_LIB}:${SQLSERVER_LIB} ``` 7. Save the changes to the start script. 8. Exit the editor. 9. Restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to an SQL Server database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-sql-server-database). #### Windows ##### Windows Server 2019 1. Verify if the Microsoft OLE DB Driver is installed in your system. To check, the `msoledbsql.dll` or `msoledbsql19.dll`, and `msoledbsqlr.rll` should be in `%SYSTEMROOT%\system32\`. 2. If the OLD DB Driver does not exist, then download and install the OLE DB Driver from Microsoft’s website. 3. After installing the driver, restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to an SQL Server database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-sql-server-database). ##### Windows Server 2018 and older **Note**  You may use either the OLE DB Driver or the Microsoft SQL Server Native Client for Windows Server 2018 and older. 1. Verify if the Microsoft SQL Server Native Client is installed in your system. To check, the `sqlncli*.dll`should be in `%SYSTEMROOT%\System32` or `%SYSTEMROOT%\SysWOW64`. 2. If the Microsoft SQL Server Native Client does not exist, then download and install the Microsoft SQL Server Native Client from Microsoft’s website. 3. After installing the client, restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to an SQL Server database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-sql-server-database). #### AIX Microsoft does not currently provide AIX drivers which allow connecting to MS SQL Server running on Windows. #### Solaris Microsoft does not currently provide Solaris drivers which allow connecting to MS SQL Server running on Windows. ### Oracle database #### Linux 1. Locate the Oracle library file - `libclntsh.so`. Below is an example that shows you where to locate the Oracle library file: ``` [root@pabo lib]# pwd /usr/lib/oracle/21/client64/lib [root@pabo lib]# ls -l libclntsh* lrwxrwxrwx. 1 oracle oinstall 12 Aug 9 22:27 libclntsh.so.10.1 -> libclntsh.so lrwxrwxrwx. 1 oracle oinstall 12 Aug 9 22:27 libclntsh.so.11.1 -> libclntsh.so -rwxr-xr-x. 1 oracle oinstall 58804016 Aug 9 22:28 libclntsh.so.12.1 [root@pabo lib]# ``` 2. Locate the `tnsnames.ora` file. Below is an example of script that shows you the `tnsnames.ora` file: ``` [root@pabo admin]# pwd /usr/lib/oracle/21/client64/network/admin [root@pabo admin]# ls -l total 20 -rw-r--r--. 1 oracle oinstall 350 Aug 9 22:29 listener.ora drwxr-xr-x. 2 oracle oinstall 4096 Aug 9 22:24 samples -rw-r--r--. 1 oracle oinstall 373 Oct 31 2013 shrept.lst -rw-r--r--. 1 oracle oinstall 195 Aug 9 22:29 sqlnet.ora -rw-r--r--. 1 oracle oinstall 359 Aug 9 22:34 tnsnames.ora ``` 3. Add these variables to the Netprobe start script using a text editor. Refer to the table below:
Variable Value
ORACLE_HOME /usr/lib/oracle/21/client64
TNS_ADMIN /usr/lib/oracle/21/client64/network/admin
ORACLE_LIB /usr/lib/oracle/21/client64/lib
LD_LIBRARY_PATH <Existing path>:${ORACLE_LIB}
4. Add the **ORACLE\_HOME** variable. 5. Enter the path of the lib folder that contains the `libclntsh.so` file in the **ORACLE\_HOME** variable. 6. Export the **ORACLE\_HOME** variable: ``` export ORACLE_HOME=/usr/lib/oracle/21/client64 ``` 7. Add the **TNS\_ADMIN** variable. 8. Export the **TNS\_ADMIN** variable. For example: ``` export TNS_ADMIN=/usr/lib/oracle/21/client64/network/admin ``` 9. Add the **ORACLE\_LIB** variable. 10. Enter the path of the folder which contains the `libclntsh.so` file in the **ORACLE\_LIB** variable. 11. Export the **ORACLE\_LIB** variable: ``` export ORACLE_LIB=/usr/lib/oracle/21/client64/lib ``` 12. Add the variables **ORACLE\_LIB** in the **LD\_LIBRARY\_PATH** variable: ``` export LD_LIBRARY_PATH=${NP_LIB}:${ORACLE_LIB} ``` 13. Exit the editor. 14. Restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to an Oracle database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-oracle-database). #### Windows 1. Locate the Oracle client library file - `oci.dll`. 2. Locate the `tnsnames.ora` file. 3. Add the Path system variable of the Windows host:
Variable Value
Path <Existing path>;C:\database_libs\oracle\lib
ORACLE_HOME

C:\database_libs\oracle

Note: The ORACLE_HOME variable should be set one directory higher where theoci.dll library file is located.
TNS_ADMIN C:\database_libs\oracle
4. Click **Start** on your computer. 5. Right-click **Computer**, and click **Properties**. 6. Click **Advanced system settings**. 7. Click **Environment Variables**. 8. Click the **Path** system variable. ![Path system variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path.png) 9. Click **Edit…** to append the path to oci.dll file. ![System variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path_edit.png) 10. Click **OK** to save the changes in the **Edit System Variable** screen. 11. Add the **ORACLE\_HOME** system variable in the Windows host. - Click **New...** in the System variables group. - Enter the **ORACLE\_HOME** in the **Variable name** field. - Enter the path of the `oci.dll` file in the **Variable value** field. - Click **OK** to save the changes in the **New System Variable** screen. - Click **OK** to save the changes in the **Environment Variables** screen. 12. Add the **TNS\_ADMIN** system variable in the Windows host. - Click **New...** in the System variables group. - Enter the TNS\_ADMIN in the **Variable name** field. - Enter the path of the `tnsnames.ora` file in the **Variable value** field. - Click **OK** to save the changes in the **New System Variable** screen. - Click **OK** to save the changes in the **Environment Variables** screen. **Note** You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to [Connect to an Oracle database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-oracle-database). #### AIX Export the following variables: ``` export ORACLE_HOME=/usr/local/oracle/instantclient_19_3_ppc64 export TNS_ADMIN=/usr/local/oracle/instantclient_19_3_ppc64 export LIBPATH=$ORACLE_HOME:$LIBPATH ``` #### Setting up Oracle connection with Kerberos authentication This section provides a configuration guide for setting up the Oracle connection with Kerberos authentication, enabling connectivity between SQL Toolkit and Gateway Database Logging. ##### Preparing the Active Directory Server To prepare the Active Directory server, do the following: 1. Create a service account in the Active Directory for the database server to validate the Kerberos tickets. This user does not need any specific rights but enable **password never expires**. 2. Ensure that you deselect the account option **Use Kerberos DES Encryption types for this account** and select the option **Do not require Kerberos preauthentication** for this user. 3. Make sure that the **SPN** is set to the correct realm. For example: `setspn -A /@ ` 4. Extract the keytab file for this designated user, so that we can create tickets without inputting a password. To do this, use the following command: ```ktpass -princ /@ -crypto all -pass -mapuser @ -out v5srvtab``` 5. Copy the file in the database server and store it in the `/etc/v5srvtab` directory. ##### Setting up the Oracle Server 1. Generate a Kerberos ticket, which will be used to connect to the Kerberos server for ticket validation.To do this: `$ORACLE_HOME/bin/okinit -k -t /etc/v5srvtab /` 2. Update the following files: - `sqlnet.ora` ```ora # sqlnet.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/sqlnet.ora # Generated by Oracle configuration tools. # Debug/Tace logs can be removed SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5) TRACE_LEVEL_SERVER = ADMIN SQLNET.KERBEROS5_CLOCKSKEW = 6000 SQLNET.KERBEROS5_CONF = /etc/krb5.conf SQLNET.KERBEROS5_CONF_MIT = true LOG_DIRECTORY_SERVER = /opt/oracle/logging DIAG_ADR_ENABLED = OFF SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle ``` - `tnsnames.ora` ```ora # tnsnames.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCLPDB1= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) ) LISTENER_ORCLPDB1 = (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521)) ``` - `/etc/krb5.conf` ```conf # Configuration snippets may be placed in this directory as well includedir /etc/krb5.conf.d/ includedir /var/lib/sss/pubconf/krb5.include.d/ [logging] default = FILE:/var/log/krb5libs.log kdc = FILE:/var/log/krb5kdc.log admin_server = FILE:/var/log/kadmind.log [libdefaults] default_realm = QAMNL.COM [realms] QAMNL.COM = { kdc = ksad2.qamnl.com admin_server = ksad2.qamnl.com default_domain = QAMNL.COM } [domain_realm] qamnl.com = QAMNL.COM .qamnl.com = QAMNL.COM ``` 3. Additional configurations can be included within the libdefaults section, such as: - ticket_lifetime = 24h - renew_lifetime = 7d ##### Setting up the Oracle Client 1. Update the following files: - `sqlnet.ora` ``` SQLNET.KERBEROS5_CONF = /etc/krb5.conf SQLNET.KERBEROS5_CONF_MIT = true ADR_BASE = /opt/oracle SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, KERBEROS5) TRACE_LEVEL_CLIENT = ADMIN SQLNET.KERBEROS5_CLOCKSKEW = 6000 LOG_DIRECTORY_SERVER = /opt/oracle/logging DIAG_ADR_ENABLED = OFF SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle ``` - `tnsnames.ora` ``` ORCLPDB1= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) ) LISTENER_ORCLPDB1 = (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521)) ``` - `/etc/krb5.conf` ``` includedir /etc/krb5.conf.d/ includedir /var/lib/sss/pubconf/krb5.include.d/ [logging] default = FILE:/var/log/krb5libs.log kdc = FILE:/var/log/krb5kdc.log admin_server = FILE:/var/log/kadmind.log [libdefaults] default_realm = QAMNL.COM [realms] QAMNL.COM = { kdc = ksad2.qamnl.com admin_server = ksad2.qamnl.com default_domain = QAMNL.COM } [domain_realm] qamnl.com = QAMNL.COM .qamnl.com = QAMNL.COM ``` 3. Additional configurations can be included within the libdefaults section, such as: - ticket_lifetime = 24h - renew_lifetime = 7d #### Troubleshooting Oracle with Kerberos authentication errors 1. `ERROR: SQL-TOOLKIT[sql] Fail - ORA-12638`: Credential retrieval failed - Regenerate Kerberos Ticket on Oracle Server using the command: `$ORACLE_HOME/bin/okinit -k -t /etc/v5srvtab /` - Obtain a Kerberos ticket-granting ticket (TGT) using `kinit` on Oracle Clients 2. `ERROR: SQL-TOOLKIT[sql] Fail - ORA-12641`: Authentication service failed to initialize - Obtain a Kerberos ticket-granting ticket (TGT) using `kinit` on Oracle Clients - Verify KRB5CCNAME environment variable does not point to a null credential cache, better to unset. - Once TGT has been issued, copy the credential cache to the current gateway working directory as geneos_krb5 3. `ERROR: SQL-TOOLKIT[sql] Fail - OCIEnvNlsCreate != OCI_SUCCESS(6)` - Verify `ORACLE_HOME` environment variable has been exported with the correct path ### PostgreSQL database Netprobe requires the PostgreSQL client libraries to communicate with the PostgreSQL database server. The path to these client libraries should be included in the machine's path environment variable. **Note** - PostgreSQL is not supported in IBM AIX platforms. - PostgreSQL connections configured with SSL or TLS are not supported in SUSE 12 platforms. #### Linux 1. Locate the PostgreSQL client library file - `libpq.so`. Below is an example that shows you where to locate the PostgreSQL client library file: ``` [root@pabo admin]# pwd /usr/lib/postgresql/14/lib [root@pabo admin]# ls -l total 10928 -rw-r--r-- 1 500 sandbox 17120 Mar 23 2015 libcom_err.so.3 -r-xr-xr-x 1 500 sandbox 2135635 Mar 23 2015 libcrypto.so.1.0.0 -rw-r--r-- 1 500 sandbox 152714 Mar 23 2015 libecpg.a -rw-r--r-- 1 500 sandbox 18852 Mar 23 2015 libecpg_compat.a -rwxr-xr-x 1 500 sandbox 23327 Mar 23 2015 libecpg_compat.so.3.6 lrwxrwxrwx 1 500 sandbox 14 Apr 11 2017 libecpg.so -> libecpg.so.6.6 -rwxr-xr-x 1 500 sandbox 254702 Mar 23 2015 libedit.so.0 -rw-r--r-- 1 500 sandbox 415515 Mar 23 2015 libgssapi_krb5.so. drwxrwxr-x 2 500 sandbox 4096 Mar 23 2015 pkgconfig drwxrwxr-x 3 500 sandbox 4096 Mar 23 2015 postgresql ``` 2. Add and export the following variables in the Netprobe start script:
Variable Value
POSTGRES_LIB /usr/lib/postgresql/14/lib
LD_LIBRARY_PATH <Existing path>:${POSTGRES_LIB}
3. Save the changes done to the start script. 4. Exit the text editor. 5. Restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to a PostgreSQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-postgresql-database). #### Windows 1. Modify the Path system variable of the Windows host. ``` existing path; C:\Program Files\PostgreSQL\psqlODBC\bin ``` 2. Locate the PostgreSQL client library file - `libpq.dll`. Below is an example of script that shows you the PostgreSQL file: ``` C:\Program Files\PostgreSQL\psqlODBC\bin>dir Volume in drive C has no label. Volume Serial Number is 1846-3FFA Directory of C:\Program Files\PostgreSQL\psqlODBC\bin 04/11/2017 06:43 PM DIR. . 04/11/2017 06:43 PM DIR.. 03/17/2017 02:15 PM 31,232 capi.dll 03/17/2017 02:15 PM 1,659,904 libeay32.dll 03/17/2017 02:15 PM 1,690,490 libiconv-2.dll 11 File(s) 5,591,085 bytes 2 Dir(s) 3,842,527,232 bytes free C:\Program Files\PostgreSQL\psqlODBC\bin ``` 3. Click **Start** on your computer. 4. Right-click **Computer**, and click **Properties**. 5. Click **Advanced system settings**. 6. Click **Environment Variables**. 7. Click the **Path** system variable. ![Path system variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path.png) 8. Click **Edit…** to append the path of the `libmysql.dll` file. ![System variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path_edit.png) 9. Click **OK** to save the changes in the **Edit System Variable** screen. 10. Click **OK** to save the changes in the **Environment Variables** screen. **Note** You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to [Connect to a PostgreSQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-postgresql-database). #### Solaris 1. Locate the PostgreSQL client library file - `libpq.so`. 2. Add and export the following variables in the start script:
Variable Value
POSTGRES_LIB /usr/postgres/9.4-pgdg/lib
LD_LIBRARY_PATH <Existing path>:${POSTGRES_LIB}
3. Save the changes done to the start script. 4. Exit the text editor. 5. Restart the Netprobe. **Note** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor. #### Setting up PostgreSQL connection with Kerberos authentication This section describes how to configure Kerberos authentication for PostgreSQL in SQL Toolkit. Starting with Geneos 7.11.0, SQL Toolkit supports Kerberos authentication for PostgreSQL in one of two mutually exclusive modes: - Windows SSPI — enable **Use Windows authentication** on the PostgreSQL connection. Do not configure **Advanced** > **Kerberos**. - GSSAPI — configure **Advanced** > **Kerberos** (keytab, credential cache, or system default). Do not enable **Use Windows authentication**. If both are configured, Kerberos authentication is disabled and the connection falls back to password authentication. For more information about the configuration fields, see [Connect to a PostgreSQL database](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-postgresql-database) and [Advanced tab — Kerberos](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#kerberos). ##### Platform prerequisites For Linux (GSSAPI): - Kerberos client tools (`kinit`, `klist`) on the Netprobe host `PATH`. - A readable keytab or credential cache file, as configured. - Typically, `KRB5_CONFIG` set to the realm configuration file (`krb5.conf`). For Windows (GSSAPI): - MIT Kerberos for Windows (KfW) installed when you use Kerberos with GSSAPI. - The KfW `bin` directory added to `PATH` before the Windows System32 directory, so that `kinit` and `klist` refer to MIT Kerberos. The Windows built-in `klist` is not equivalent. - Netprobe and its PostgreSQL client library (`libpq`) built with GSSAPI support. For Windows (SSPI): - A PostgreSQL server configured to accept SSPI (for example, the `pg_hba.conf` method `sspi` or an equivalent method). - Netprobe runs under a Windows identity permitted by the server. MIT Kerberos for Windows is not required for SSPI-only authentication. - If the Netprobe runs as a Windows service, see [Additional steps for Windows Netprobe service](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#additional-steps-for-windows-netprobe-service). ##### GSSAPI credential modes | Mode | Configuration summary | Typical use | |------|----------------------|-------------| | Keytab | **Advanced** > **Kerberos** > **Credentials** > **Keytab** (keytab path and principal) | Unattended Netprobe; Geneos renews tickets by using `kinit` | | Credential cache | **Advanced** > **Kerberos** > **Credentials** > **Credential cache** > **Ccache Path** | Pre-provisioned ticket cache (for example, from a vault or login script) | | System default | **Advanced** > **Kerberos** enabled with no **Credentials** child (and no legacy **Principal** or **Key Tab**) | Ambient process ticket cache (`KRB5CCNAME` or platform default) | **Note** For GSSAPI connections, set the sampler **User Name** to the PostgreSQL role name (often the short principal name). If **User Name** is blank, `libpq` might use the Netprobe OS user, which can cause `pg_hba.conf` mapping to fail. Geneos does not ship Kerberos client tools. Keytab mode requires `kinit` and `klist` on `PATH` for ticket renewal. Credential cache and system default modes do not run `kinit`. The ticket cache must already contain a valid ticket when the sampler connects. ### Sybase ASE (SAP ASE) database The plugin behaviour depends on the Sybase client library referenced in these environment variables. If wide table and columns are used, Sybase client library must met the version supported by Geneos. #### Linux 1. Locate the Sybase client library file – `libsybintl_r64.so`. Below is an example that shows you where to locate the Sybase client library file: ``` [root@pabo]# pwd /opt/database_clients/sybase/OCS-15_0/lib [root@pabo]# ls -l total 25720 -rwxr-xr-x 1 root root 219202 Dec 8 2011 libsybblk64.a -rwxr-xr-x 1 root root 136044 Dec 8 2011 libsybblk64.so -rwxr-xr-x 1 root root 219564 Dec 8 2011 libsybblk_r64.a -rwxr-xr-x 1 root root 83511 Dec 8 2011 libsybcs.so -rwxr-xr-x 1 root root 1220366 Dec 8 2011 libsybct64.a -rwxr-xr-x 1 root root 56235 Dec 8 2011 libsybdldap64.so.15.7.0.1 -rwxr-xr-x 1 root root 50541 Dec 8 2011 libsybdldap.so.15.7.0.1 -rwxr-xr-x 1 root root 865832 Dec 8 2011 libsybfssl64.so.15.7.0.1 ``` 2. Locate the Sybase `lib3p64` folder. For example: ``` [root@pabo OCS-15_0]# pwd /opt/database_clients/sybase/OCS-15_0 [root@pabo OCS-15_0]# ls -l total 2900 drwxr-xr-x 2 root root 4096 Jun 3 2014 bin drwxr-xr-x 2 root root 4096 Nov 4 2016 config drwxr-xr-x 2 root root 4096 Sep 4 2013 devlib drwxr-xr-x 3 root root 4096 Jul 3 16:29 lib3p64 drwxr-xr-x 5 root root 4096 Sep 4 2013 sample drwxr-xr-x 2 root root 4096 Sep 4 2013 scripts drwxr-xr-x 3 root root 4096 May 28 2014 sybhelp drwxr-xr-x 2 root root 4096 Sep 4 2013 xappdefaults ``` 3. Locate the Sybase interfaces file. For example: ``` [root@pabo sybase]# pwd /opt/database_clients/sybase [root@pabo sybase]# ls -l total 68 drwxr-xr-x 8 root root 4096 Jun 9 00:30 backup drwxr-xr-x 59 root root 4096 Sep 4 2013 charsets -rwxr-xr-x 1 root root 567 Sep 4 2013 SYBASE.env drwxr-xr-x 2 root root 4096 Sep 4 2013 Sybase_Install_Registry -rwxr-xr-x 1 root root 841 Sep 4 2013 SYBASE.sh -rwxr-xr-x 1 508 515 1212 Jul 23 2014 sybinit.err drwxr-xr-x 4 root root 4096 Sep 4 2013 sybuninstall ``` This database uses the libraries that comes with the SAP ASE database. Add the following variables in the Netprobe start script:
Variable Value
SYBASE /path/to/sybase/home
SYBASE_LIB /path/to/sybase/lib
SYBASE_LIB3P64 /path/to/sybase/lib3p64
LD_LIBRARY_PATH <Existing path>:${SYBASE_LIB}:${SYBASE_LIB3P64}
4. Add the **SYBASE** variable. 5. Enter the path of the folder which contains the interfaces file in the **SYBASE** variable. 6. Export the **SYBASE** variable: ``` export SYBASE=/opt/database_clients/Sybase ``` 7. Add the **SYBASE\_LIB** variable. 8. Enter the path of the folder which contains the `libsybintl_r64.so` file in the **SYBASE\_LIB** variable. 9. Export the **SYBASE\_LIB** variable: ``` export SYBASE_LIB=/opt/database_clients/sybase/OCS-15_0/lib ``` 10. Add the **SYBASE\_LIB3P64** variable. 11. Enter the path of the `lib3p64` folder in the **SYBASE\_LIB3P64** variable: ``` export LD_LIBRARY_PATH=${NP_LIB}:${SYBASE_LIB}:${SYBASE_LIB3P64} ``` 12. Add the **SYBASE\_LIB** and **SYBASE\_LIB3P64** variables into the **LD\_LIBRARY\_PATH** variable: 13. Save the changes done to the start script. 14. Exit the text editor. 15. Add the following to `/opt/sap/locale/locales.dat`. ``` locale = en_UK, us_english, iso_1 ``` 16. Add the following to the interface file. ``` GENEOSDB_AWS master tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000 query tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000 ``` 17. Run the following command: ``` source /opt/sap/./SYBASE.sh ``` 18. Restart the Netprobe. **Success** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to [Connect to a Sybase database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-sybase-database). #### Windows 1. Configure the Windows environment and Sybase client. 2. Locate the Sybase `lib3p64` folder. 3. Locate the Sybase interfaces file, `sql.ini`. 4. Modify the **Path** system variable of the Windows host. 5. Click **Start** on your computer. 6. Right-click **Computer**, and click **Properties**. 7. Click **Advanced system settings**. 8. Click **Environment Variables**. 9. Click the **Path** system variable. ![Environment Variables](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path.png) 10. Click **Edit…** to append the path to `libsybct64.dll` and `lib3p64` file. ![System variable](https://docscdn.itrsgroup.com/geneos/images/netprobe/database-images/sql_toolkit_plugin_environment_variable_path_edit.png) 11. Click **OK** to save the changes in the **Edit System Variable** screen. 12. Click **OK** to save the changes in the **Environment Variables** screen. 13. Add the SYBASE system variable in the Windowshost. - Click **New...** in the System variables group. - Enter the SYBASE name in the **Variable name** field. - Enter the path of the folder where the `ini` folder is located in the **Variable value** field. - Click **OK** to save the changes in the **New System Variable** screen. - Click **OK** to save the changes in the **Environment Variables** screen. 14. Restart the Netprobe. **Success** You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to [Connect to a Sybase database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-sybase-database). #### AIX ``` export SYBASE=/opt/sybase export SYBASE_LIB=/opt/sybase/OCS-15_0/lib export SYBASE_LIB3P64=/opt/sybase/OCS-15_0/lib3p64 export LIBPPATH=$SYBASE_LIB:$SYBASE_LIB3P64:$LIBPATH ``` #### Configure Sybase using OCS ##### Linux 1. In the Netprobe start script, add the variable **SYBASE\_OCS**. The value must be `OCS_`. ``` export SYBASE_OCS=OCS-15_0 ``` 2. Export the **SYBASE\_OCS** variable. 3. Save the script. 4. Add the following to `/opt/sap/locale/locales.dat`. ``` locale = en_UK, us_english, iso_1 ``` 5. Add the following to the interface file. ``` GENEOSDB_AWS master tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000 query tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000 ``` 6. Run the following command: ``` source /opt/sap/./SYBASE.sh ``` 7. Restart the Netprobe. ##### Windows 1. Configure the Windows environment. 2. Add the SYBASE system variable in the Windows host. 3. Click **Start** on your computer. 4. Right-click **Computer**, and then click **Properties**. 5. Click **Advanced system settings**. 6. Click **Environment Variables**. 7. Click **New...** in the System variables group. 8. Enter the SYBASE\_OCS in the **Variable name** field. 9. Enter the OCS version in the **Variable value** field. 10. Click **OK** to save the changes in the **New System Variable** screen. 11. Click **OK** to save the changes in the **Environment Variables** screen. 12. Restart the Netprobe. 13. Modify the **Application Name** setting in your SQL Toolkit sampler.
Field Description
SYBASE_OCS Containing the OCS_* folder name.
Example: SYBASE_OCS=OCS-15_0
ocs.cfg Includes the desired Netprobe application name in the file.
Example: [Netprobe] CS_SEC_ENCRYPTION = CS_TRUE
Application Name Same value defined in the ocs.cfg file.
The runtime configuration file `ocs.cfg` is used by Client-Library applications to set the following: - Property values - Server option values - Server capabilities - Debugging options By using `ocs.cfg`, applications eliminate the need to call the routines to set the values. This allows you to change the application settings without recompiling the code. **Note** By default, Client-Library does not read the `ocs.cfg`. However, all Client-Library based applications attempt to read the file if the file name exists in `$SYBASE/$SYBASE_OCS/config`. The application must set the properties to enable Client-Library to use this file. ### IBM Db2 database #### Linux 1. Locate the client library file - `libdb2.so`. Below is an example of an IBM Db2 Data server file: ``` $ cd DB2LIB is located at /opt/ibm/db2/V11.5/lib64 $ ls -l libdb2.so* lrwxrwxrwx. 1 root root 12 Jun 11 2019 libdb2.so -> libdb2.so.1 -r-xr-xr-x. 1 bin bin 42566992 Jun 11 2019 libdb2.so.1 ``` 2. Locate the home directory of the Db2 Data Server Client instance created to connect to the target Db2 database: ``` $ cd /home/db2inst1/sqllib/ $ ls -l total 120 drwxr-sr-x. 2 db2inst1 db2iadm1 4096 Apr 25 21:40 adm drwxr-s---. 2 db2inst1 db2iadm1 4096 Apr 25 21:40 backup lrwxrwxrwx. 1 root db2iadm1 31 Apr 25 21:40 bin -> /opt/lib/database/db2/V10.5/bin /opt/lib/database/db2/V10.5/conv drwxrwsr-t. 2 db2inst1 db2iadm1 4096 Apr 25 21:40 ctrl drwxrwsr-t. 2 db2inst1 db2iadm1 4096 Apr 25 21:40 ctrlha -rwxr-xr-x. 1 db2inst1 db2iadm1 12147 Apr 25 21:40 db2cshrc drwxrwsrwt. 2 db2inst1 db2iadm1 4096 Apr 25 21:40 db2dump -rwxr-xr-x. 1 db2inst1 db2iadm1 7962 Apr 26 00:50 db2profile ``` **Note** The instance name will be the value of the variable **DB2INSTANCE**. The path will be the value of the variable **DB2DIR**. 3. Add and export the following variables in the Netprobe start script:
Variable Value
DB2Instance export DB2INSTANCE=db2inst1
DB2DIR export DB2DIR=/home/${DB2INSTANCE}/sqllib
DB2LIB export DB2LIB=${DB_CLIENT_HOME}/db2/V10.5/lib64
LD_LIBRARY_PATH export LD_LIBRARY_PATH=${DB2LIB}
4. Save the changes done to the start script. 5. Exit the text editor. 6. Run the following command: ``` source /home/db2inst1/sqllib/db2profile ``` 7. Restart the Netprobe. **Note** You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to[Connect to a Db2 database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-db2-database). #### Windows 1. Locate the client library file - `db2cli64.dll`: ``` C: >cd C:\database_libs\db2\SQLLIB\lib C:>dir Volume in drive C has no label. Volume Serial Number is 1846-3FFA Directory of C:\database_libs\db2\SQLLIB\lib 04/26/2018 05:31 PM DIR . 04/26/2018 05:31 PM DIR .. 05/30/2013 12:13 AM 634,362 db2api.lib 05/30/2013 12:13 AM 2,546 db2apie.lib 05/30/2013 12:13 AM 4,964 db2psmd.lib 04/26/2018 05:31 PM DIR Win32 6 File(s) 835,144 bytes 3 Dir(s) 5,418,590,208 bytes free ``` 2. Modify the **Path** variable of the Windows host. 3. Edit the **Path** system variable in the Environment Variables. 4. Click **Edit..**. to append the path of the `db2cli64.dll` file. 5. Click **OK** to save the changes in the **New System Variable** screen. 6. Click **OK** to save the changes in the **Environment Variables** screen. **Note**  If your database is already attached to a node, then you need to uncatalog them in order to successfully connect to the DB server by running the following: ``` db2 uncatalog database db2 uncatalog database ``` For example: ``` db2 uncatalog database SAMPLE db2 uncatalog database DB2INST1 ``` **Success** You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to [Connect to a Db2 database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-db2-database). #### AIX If you wish to run the Netprobe on a different machine to that on which the Db2 server is running, then you would need to download and install the Db2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the Netprobe environment, so that the client libraries are available to it. ``` export DB2LIB=/opt/db2/V11.5/lib64 export LIBPATH=$DB2LIB:$LIBPATH ``` #### Solaris If you wish to run the Netprobe on a different machine to that on which the Db2 server is running, then you would need to download and install the Db2 Client package from IBM's website. You should then follow the instructions in the "Quick Beginnings for DB2 Clients" document to set-up communications between the client and the server. This document can be found on IBM's website. Finally, you would need to configure the Netprobe environment, so that the client libraries are available to it. ``` setenv LD_LIBRARY_PATH /opt/IBM/db2/V9.7/lib32 ``` ## Configure the SQL Toolkit sampler For more information on plugin configuration, see [SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html). 1. Open your Gateway Setup Editor 2. Go to **Samplers** > **New Sampler** to create a new sampler. 3. Input a name for your new sampler. 4. Select `SQL Toolkit` in the **Plugin** dropdown list. ![SQL Toolkit sampler](https://docscdn.itrsgroup.com/geneos/images/plug-ins/sql-toolkit-sampler-6-3.png) 5. Select the database where you will connect the plugin to on the **Database** menu. For more information on the database configuration in your SQL toolkit sampler, see the following: - [Connect to a Db2 database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-db2-database) - [Connect to a MySQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-mysql-database) - [Connect to an Oracle database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-oracle-database) - [Connect to a PostgreSQL database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-postgresql-database) - [Connect to an SQL Server database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-an-sqlserver-database) - [Connect to a Sybase database in SQL Toolkit configuration](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#connect-to-a-sybase-database) **Note** The **Database** dropdown list displays all the supported databases regardless of their availability on the underlying system the Netprobe is running on. The Administrator must ensure that the appropriate database client libraries are correctly installed and configured before expecting the selected one to work. For more information, see [Configure the Netprobe and database client libraries](#configure-thenetprobe-and-database-client-libraries). ### Adjust the maximum database connections The probe has a default value of `10`, which means that no more than ten simultaneous open connections can be made to all databases configured on the Netprobe. Before the sampler configuration starts, you can limit the total number of database for each Netprobe. This limit is set to ensure the configured database plugins do not consume all available database sessions. To configure the `maxDatabaseConnections` in Netprobe: 1. In Gateway Setup Editor, open the host Netprobe where the SQL Toolkit is running. 2. Go to the **Managed entities** to verify the host Netprobe. This will resolve the following error: `Error: SQL-Toolkit Fail maximum connections have been exceeded (0)`. 3. Click the **Advanced** tab in the **Probe** section. 4. Enter the desired number of connections in the **Max database connections** field. This sets the maximum amount of connections that a Netprobe can make to any database. For example, if: - One Managed Entity belonging to one Netprobe uses three SQL Toolkit plugins. - One Sybase plugin and one Oracle plugin. - Two Managed Entities belonging to one Netprobe uses the same SQL Toolkit plugin. This setup uses ten database connections in total. If the Netprobe has the default value of `10`, then there is no additional database connections can be made. ### Set SQL Toolkit DB authentication To require authentication when configuring the database connection, set your username and password: 1. Enter your credentials in the **Connection** > **User Name** and **Connection** > **Password** fields. 2. Select AES 256 bit Encryption on the **Password** menu to set an encrypted password. 3. Confirm the password, and click **OK**. ## Manage unsupported data types SQL Toolkit plugin does not support the following data types: - `SA_dtUnknown` — Unknown - `SA_dtInterval` — Interval (MySQL specific) - `SA_dtLongBinary` — Long binary data - `SA_dtBLob` — BLob data - `SA_dtCLob` — CLob data - `SA_dtCursor` — Oracle REF CURSOR - `SA_dtSpecificToDBMS` — server-specific and not interpreted by SQLAPI++ If you use any of these data types to execute database queries, Active Console will unable to display the queries correctly: ![](https://docscdn.itrsgroup.com/geneos/images/plug-ins/sql-toolkit-unsupported-datatypes-dataview.png) In the example above, the `COL2` column contains a `clob` data type in Oracle database. To manage queries that return unsupported data types in SQL Toolkit, you may rewrite the query with SQL functions that convert the data to readable formats. For example: 1. You can use the `cast` and `substr` functions for conversion. The `substr` function is use to reduce the output to `1000` characters if the source data is huge. 3. Configure the SQL query in the sampler: ``` SELECT col1, cast(substr(col2,1,1000) as varchar(1000)) AS col2 FROM clob_table ``` **Success** Active Console now displays the dataview with the readable formats. ![](https://docscdn.itrsgroup.com/geneos/images/plug-ins/sql-toolkit-datatypes-dataview.png) ### Interval data type SQL Toolkit plugin does not support the `Interval` data type specific to MySQL and Oracle databases. Active Console might display the following error messages: - MySQL database — `unsupported::interval`. - Oracle — `ORA-01406: fetched column value was truncated`. If you encounter this problem, you may update to newer version of Netprobe.