Enterprise Recon 2.9.0
Databases
This section covers the following topics:
- Supported Databases
- Licensing
- Requirements
- DBMS Connection Details
- Add a Database Target Location
- How ER2 Scans Databases
- Remediating Databases
- InterSystems Caché Connection Limits
- Tibero Scan Limitations
- Teradata FastExport Utility
- Allow Remote Connections to PostgreSQL Server
Supported Databases
- IBM DB2 11.1 and above.
- IBM Informix 12.10 and above.
- InterSystems Caché 2017.2 and above.
- MariaDB 10.3 and above.
- Microsoft SQL 2008 and above.
- MongoDB 4.0 and above.
- MySQL 5.0 and above.
- Oracle Database 9 and above.
- PostgreSQL 9.6 and above.
- SAP HANA 2.0 and above.
- Sybase/SAP Adaptive Server Enterprise 15.7 and above.
- Teradata 16.0 and above.
- Tibero 6 and above.
Ground Labs supports and tests the databases listed above. However, database versions not indicated may still work as expected.
For databases where no specific version is specified, Ground Labs' support is limited to versions the associated vendor still provides active support, maintenance and software patches for.
Licensing
For Sitewide Licenses, all scanned database Targets consume data from the Sitewide License data allowance limit.
For Non-Sitewide Licenses, database Targets require one Server & DB License per host machine, and consume data from the Server & DB License data allowance limit.
See Target Licenses for more information.
Requirements
| Component | Description |
|---|---|
| Proxy Agent |
Windows Agent with database runtime components The Windows Agent with Database Runtime Components can scan all supported databases and is recommended for scanning IBM DB2 and Oracle Databases. Windows Agents (without database runtime components) and Linux Agents To use Windows Agents (without database runtime components) and Linux Agents to scan databases, make sure the ODBC drivers for the Target database are installed on the Agent host. Specific requirements for each database type are listed in DBMS Connection Details.
|
| Database Credentials |
Your database credentials must have the minimum required privileges to access the databases, schemas, or tables to be scanned. Example: To scan a MySQL database, use credentials that have SELECT (data reader) permissions. |
To reduce the risk of data loss or privileged account abuse, the Target credentials provided for the intended Target should only be granted read-only access to the exact resources and data that require scanning. Never grant full user access privileges or unrestricted data access to any application if it is not required.
DBMS Connection Details
The following section describes the supported database management systems (DBMS) and the settings required for ER2 to connect to and scan them.
IBM DB2
| Settings | Description |
|---|---|
| Default Port | 50000 If connection to the database uses a port other than 50000, the [:<port>] value must be defined in the Path field. |
| Required Proxy Agents |
|
| Path Syntax |
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
IBM Informix
| Settings | Description |
|---|---|
| Default Port | 9088 If connection to the database uses a port other than 9088, the [:<port>] value must be defined in the Path field. |
| Required Proxy Agents |
|
| Proprietary Client |
You must have an IBM Informix client installed on the Agent host. Make sure that the client has been configured to connect to the target Informix database instance by running "setnet32.exe". For more information on "setnet32.exe", see IBM: Setting up the SQLHOSTS registry key with Setnet32 (Windows). The following IBM Informix clients are supported:
Both clients are included in the IBM Informix Software Bundle installer. |
| Path Syntax |
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
InterSystems Caché
| Settings | Description |
|---|---|
| Default Port | 1972 If connection to the namespace uses a port other than 1972, the [:<port>] value must be defined in the Path field. |
| Required Proxy Agents |
|
| Proprietary Client |
Requires Visual C++ Redistributable Packages for Visual Studio 2013 to be installed on the Agent host. |
| Username and Password Syntax |
Use the following syntax for the Username and Password
fields for Instance Authentication and LDAP Authentication methods.
|
| Path Syntax |
To scan the InterSystems Caché relational database model, use the following syntax:
Delimited Identifiers Support for delimited identifiers is enabled by default when scanning InterSystems Caché Targets. If the Support Delimited Identifiers setting is disabled for InterSystems Caché SQL, set the option (DI=FALSE).
If you encounter an "IDENTIFIER expected" error, set the option (DI=FALSE). |
| Path Case Sensitivity | The path syntax is case-sensitive. |
| Others | Each InterSystems Caché license permits a limited number of connections. See InterSystems Caché Connection Limits for more information. |
MariaDB
| Settings | Description |
|---|---|
| Default Port | 3306 If connection to the database uses a port other than 3306, the [:<port>] value must be defined in the Path field. |
| Required Proxy Agents |
|
| Path Syntax |
Pagination is enabled by default when scanning MariaDB databases. To disable pagination, set the option (paged=false).
In MariaDB, a "database" may also be referred to as a
"schema".
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
Microsoft SQL Server
| Settings | Description |
|---|---|
| Default Port | 1433 If connection to the database uses a port other than 1433, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
Requires the Microsoft ODBC Driver for SQL Server to be installed on
the Windows Proxy Agent host for
ER2 to connect
to the database.
|
| Username and Password Syntax |
Use the correct syntax for Username and Password fields according to your Microsoft SQL Server authentication method: SQL Server Authentication
SQL Server Authentication must be used if the Windows Proxy Agent
does not reside on the same host as the Microsoft SQL database server.
Windows Authentication
Windows Authentication is only supported if the Windows Proxy Agent
resides on the same host as the Microsoft SQL database server.
For more information on Windows or SQL Server Authentication,
see Choose an Authentication Mode.
|
| Path Syntax |
Pagination is enabled by default when scanning Microsoft SQL databases. To disable pagination, set the option (paged=false).
In Microsoft SQL Server, a "database" may also be referred to as
a "catalog".
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
MongoDB
| Settings | Description |
|---|---|
| Default Port | 27017 If connection to the database uses a port other than 27017, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
|
| Username and Password Syntax |
Use the correct syntax for the Username and Password fields according to your MongoDB authentication method: No authentication required
Username, password and authentication database
|
| Path Syntax |
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
MySQL
| Settings | Description |
|---|---|
| Default Port | 3306 If connection to the database uses a port other than 3306, the [:<port>] value must be defined in the Path field. |
| Required Proxy Agents |
|
| Path Syntax |
Pagination is enabled by default when scanning MySQL databases. To disable pagination, set the option (paged=false).
In MySQL, a "database" may also be referred to as a
"schema".
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
Oracle Database
| Settings | Description |
|---|---|
| Default Port | 1521 If connection to the database uses a port other than 1521, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
|
| Libraries | Requires the following libraries to be installed on the Linux 3
Agent host:
sudo apt-get install libaio1 libaio-dev |
| Path Syntax |
Connect using a fully qualified domain name (FQDN) When adding an Oracle Database as a Target location, you may need to enter the fully qualified domain name (FQDN) of the database server instead of its host name. Oracle 12x/TNS: protocol adapter error If you are using Oracle 12x, or if the Oracle database displays a "TNS: protocol adapter error", you must specify a SERVICE_NAME
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
PostgreSQL
| Settings | Description |
|---|---|
| Default Port | 5432 If connection to the database uses a port other than 5432, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
|
| Username and Password Syntax |
Use the following syntax for the Username and Password
fields for MD5 and SCRAM-SHA-256 password-based authentication methods.
|
| Path Syntax |
PostgreSQL by default blocks remote connections to the PostgreSQL
server. To configure the PostgreSQL to allow remote connections, see
Allow Remote Connections to PostgreSQL Server.
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
SAP HANA
| Settings | Description |
|---|---|
| Default Port | 30015 If connection to the database uses a port other than 30015, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
If the Agent host has SAP HANA ODBC drivers
installed, the Agent will use those drivers instead of its built-in
database runtime components.
|
| Username and Password Syntax |
Basic authentication with database user name and password
|
| Path Syntax |
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
Sybase / SAP ASE
| Settings | Description |
|---|---|
| Default Port | 3638 If connection to the database uses a port other than 3638, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
|
| Proprietary Client |
You must set up the data source to connect to Sybase/SAP ASE proprietary database software. On the Proxy Agent machine, install a Sysbase/ASE client to provide the ODBC drivers that ER2 can use to connect to the database. Examples of Sybase/ASE clients:
|
| Path Syntax |
In Sybase ASE, a "database" may also be referred to as a "catalog".
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
Teradata
| Settings | Description |
|---|---|
| Default Port | 1025 If connection to the database uses a port other than 1025, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
|
| Proprietary Client |
Requires Teradata Tools and Utilities 16.10.xx. Install the Teradata Tools and Utilities on the Agent host.
You may need to restart the Agent host after installing Teradata Tools
and Utilities.
|
| Path Syntax |
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
| Others | Teradata scans may create temporary tables in the default database. See Teradata FastExport Utility for more information. |
Tibero
| Settings | Description |
|---|---|
| Default Port | 8629 If connection to the database uses a port other than 8629, the [:<port>] value must be defined in the Path field. |
| Recommended Proxy Agents |
If the Agent host has Tibero 6 ODBC drivers installed, the Agent will
use those drivers instead of its built-in database runtime components.
|
| Path Syntax |
You can specify the encoding used by the Target database with the (encoding=<character_set>) option. If not specified, the default MSWIN949 character set will be used. You can specify the following values for <character_set>:
To specify the encoding that the Target database is using, use the following syntax:
|
| Path Case Sensitivity | The path syntax is case-sensitive. |
| Others | Tibero scans currently have a few limitations. See Tibero Scan Limitations for more information. |
Add a Database Target Location
- From the New Scan page, Add Targets.
- In the Select Target Type dialog box, select Server.
- In the Enter New Target Hostname field, enter the host name of your database server.
- Click Test. If ER2 can connect to the Target, the button changes to a Commit button.
- In the Select Types dialog box, click on Database.
- In Database, select the DBMS type running on your database server.
-
In the next window, enter the database connection settings. Fill in the following fields:
Field Description Path Enter path details of the database.
See DBMS Connection Details for information on the Path syntax to use.Credential Details If you have stored the credentials, select from Stored Credentials. If not, enter:
- Credential Label: Enter a descriptive label for the credential set.
- Username: User name for the database.
- Password: Password for the database.
Proxy Details Select an Agent. See DBMS Connection Details for database-specific Agent requirements.
For optimal performance, use an Agent installed on the database server. - Click Test. If ER2 can connect to the Target, the button changes to a Commit button.
- Click Commit to add the Target.
How ER2 Scans Databases
How ER2 scans databases is dependent on several factors, including (but not limited to) the database type, and the presence of primary key (PK) / unique index columns.
For certain databases, ER2 defaults to the offset-limit approach to iterate through all table rows, using the table's (sorted) PK or unique index column for pagination.
For databases such as IBM DB2, IBM Informix, InterSystems Caché, SAP HANA, Sybase/SAP Adaptive Server Enterprise, Tibero, and Oracle, by default ER2 performs unbounded queries to retrieve data during scans. However, in scenarios where the buffer limit for the Proxy Agent is not sufficient to store the retrieved data for the whole table, and the table has either a PK or unique index column, ER2 uses the offset-limit approach instead.
The scanning approach may differ for databases in certain conditions. For example, unbounded queries are used for Microsoft SQL databases when no PK or unique index columns are defined, and for Teradata databases when the FastExport utility is available. For Oracle databases, ER2 limits the number of rows being queried when the pagination option is enabled.
In instances where both the unbounded query and offset-limit approaches are not possible, ER2 only scans the first N number of rows in a database table.
Remediating Databases
Direct remediation is not supported for database Targets. This means that you cannot perform these remedial actions:
- Mask all sensitive data.
- Quarantine.
- Delete permanently.
- Encrypt file.
However, you can mark locations in the scan results of your database location for further action. For details, see Remediation.
InterSystems Caché Connection Limits
In ER2, each connected node agent requires one connection to the InterSystems Caché server. When running a Distributed Scan, each connected proxy agent in the Agent Group requires a separate connection.
Intersystems Caché permits a certain number of connections per user license. If the number of connections exceeds the maximum, another license unit will be consumed, if available. See the Caché Documentation for information on how to prevent the consumption of more than one license unit per user.
Tibero Scan Limitations
In a Target Tibero database, tables and columns with case sensitive names will be skipped during the scan. For example, if a table in the Target Tibero database is named "TABLE_ONE", it will be scanned. If a table in the Target Tibero database is named "table_One", it will be skipped during the scan.
Teradata FastExport Utility
A Teradata scan may create temporary tables that are named erecon_fexp_<YYYYMMDDHHMMSS><PID><RANDOM>. Do not remove these tables while the scan is in progress.
These temporary tables are created by the Teradata FastExport utility to temporarily store FastExport metadata. The utility extracts data from the Teradata database and stores it in memory (spool space), where the scanning engine reads and scans it. No data from the database is written to disk by the scanning engine.
The temporary tables are automatically removed when a scan completes. If a scan fails or is interrupted by an error, the temporary tables may remain in the database. In this case, it is safe to delete the temporary tables.
Allow Remote Connections to PostgreSQL Server
PostgreSQL by default blocks all connections that are not from the PostgreSQL database server itself. This means that to scan a PostgreSQL database, the Agent must either be installed on the PostgreSQL database server itself (not recommended), or the PostgreSQL server must be configured to allow remote connections.
To configure a PostgreSQL server to allow remote connections:
- On the PostgreSQL database server, locate the pg_hba.conf configuration file. On a Unix-based server, the file is usually found in the /var/lib/postgresql/data directory.
- As root, open pg_hba.conf in a text editor.
-
Add the following to the end of the file:
# Syntax: # host <database_name> <postgresql_user_name> <agent_host_address> <auth-method> host all all all md5Secure configuration
The above configuration allows any remote client to connect to the PostgreSQL server if a correct user name and password is provided. For a more secure configuration, use configuration statements that are specific to a database, user or IP address. For example: host database_A scan_user 172.17.0.0/24 md5. - Save the file and restart the PostgreSQL service.