Enterprise Recon 2.10.0

Download ER 2.10.0 User Guide (PDF)

Databases

This section covers the following topics:

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.
Using a different database version?

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.
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.
Recommended Least Privilege User Approach

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
  • Windows Agent with database runtime components
Path Syntax
  • Specific database: <database[:<port>]>
    Example: GLDB:9999
  • Specific schema: <database[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table: <database[:<port>]/schema/table>
    Example: GLDB/HRAdmin/Employees
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
  • Windows Agent with database runtime components (ER2 2.0.26 and above)
  • Windows Agent (ER2 2.0.26 and above)
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:

  • IBM Informix Connect (IConnect) 4.10
  • IBM Informix Client SDK (CSDK) 4.10

Both clients are included in the IBM Informix Software Bundle installer.
Path Syntax
  • Specific database: <instance/database[:<port>]>
    Example: ol_informix1210:9999/stores_demo
  • Specific schema: <instance/database[:<port>]/schema>
    Example: ol_informix1210/stores_demo/userA
  • Specific table: <instance/database[:<port>]/schema/table>
    Example: ol_informix1210/stores_demo/userA/customers
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
  • Windows Agent with database runtime components
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.
  • Username: <user_name>
    Example: user1
  • Password: <password>
    Example: myPassword123
Path Syntax

To scan the InterSystems Caché relational database model, use the following syntax:

  • Specific namespace: <namespace[:<port>]>
    Example: GLDB:9999
  • Specific schema: <namespace[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table:<namespace[:<port>]/schema/table>
    Example: GLDB:9999/HRAdmin/Employees

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).

  • Specific namespace: <namespace(DI=FALSE)[:<port>]>
    Example: GLDB(DI=FALSE):9999
  • Specific schema: <namespace(DI=FALSE)[:<port>]/schema>
    Example: GLDB(DI=FALSE):9999/HRAdmin
  • Specific table: <namespace(DI=FALSE)[:<port>]/schema/table>
    Example: GLDB(DI=FALSE):9999/HRAdmin/Employees

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
  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent with database runtime components
  • Linux Agent
Path Syntax
  • All locations: [:<port>]
    Example: Leave the Path blank, or :9999
  • Specific database: <database[:<port>]>
    Example: hr:9999
  • Specific table: <database[:<port>]/table>
    Example: hr/employees

Pagination is enabled by default when scanning MariaDB databases. To disable pagination, set the option (paged=false).

  • All locations: (paged=false)[:<port>]
    Example: (paged=false)
  • Specific database: <database(paged=false)[:<port>]>
    Example: hr(paged=false):9999
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
  • Windows Agent with database runtime components
  • Windows Agent
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

  • Username: <database_user_name>
  • Password: <database_user_password>
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
From ER2 2.0.21, Windows authentication is supported for Microsoft SQL 2008 and above.

  • Username: <Windows_domain>\<Windows_user_name>
  • Password: <Windows_user_password>
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

  • All locations: [:<port>]
    Example: Leave the Path blank, or :9999
  • Specific database: <database[:<port>]>
    Example: GLDB:9999
  • Specific schema: <database[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table: <database[:<port>]/schema/table>
    Example: GLDB:9999/HRAdmin/Employees
  • Scan a specific SQL Server instance (where multiple are running): <database(instance=<instance_name>)[:<port>][/schema][/table]>
    Example: GLDB(instance=MsSQLInst2):9999/HrAdmin/Employees

Pagination is enabled by default when scanning Microsoft SQL databases. To disable pagination, set the option (paged=false).

  • All locations: (paged=false)[:<port>]
    Example: Leave the Path blank, or (paged=false):9999
  • Specific database: <database(paged=false)[:<port>]>
    Example: GLDB(paged=false):9999
  • Specific schema: <database(paged=false)[:<port>]/schema>
    Example: GLDB(paged=false):9999/HRAdmin
  • Specific table: <database(paged=false)[:<port>]/schema/table>
    Example: GLDB(paged=false):9999/HRAdmin/Employees

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
  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent with database runtime components
  • Linux Agent
Username and Password Syntax

Use the correct syntax for the Username and Password fields according to your MongoDB authentication method:

No authentication required

  • Username: <leave blank>
  • Password: <leave blank>

Username, password and authentication database

  • Username: <authentication_database>/<user_name>
    Example: pgdb1/user1
  • Password: <password>
    Example: myPassword123
Path Syntax
  • All locations: [:<port>]
    Example: Leave the Path blank, or GLDB:9999
  • Specific database: <database[:<port>]>
    Example: hr:9999
  • Specific table: <database[:<port>]/<collection>
    Example: hr/employees
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
  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent with database runtime components
  • Linux Agent
Path Syntax
  • All locations: [:<port>]
    Example: Leave the Path blank, or :9999
  • Specific database: <database[:<port>]>
    Example: hr:9999
  • Specific table: <database[:<port>]/table>
    Example: hr/employees

Pagination is enabled by default when scanning MySQL databases. To disable pagination, set the option (paged=false).

  • All locations: (paged=false)[:<port>]
    Example: (paged=false)
  • Specific database: <database(paged=false)[:<port>]>
    Example: hr(paged=false):9999
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
  • Windows Agent with database runtime components
  • Linux 3 Agent with database runtime components
  • Linux 4 Agent with database runtime components
Libraries Requires the following libraries to be installed on the Linux 3 Agent host: sudo apt-get install libaio1 libaio-dev
Path Syntax
  • All locations: [:<port>]
    Example: Leave the Path blank, or :9999
  • Specific schema: <schema[:<port>]>
    Example: HR:9999
  • Specific table: <schema[:<port>]/table>
    Example: HR/EMPLOYEES

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

  • Scan a specific schema or table using service name: <schema(SERVICE_NAME=<ServiceName>)[:port]/table>
    Example: HR(SERVICE_NAME=GLDB)/EMPLOYEES
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
  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent with database runtime components
  • Linux Agent
Username and Password Syntax Use the following syntax for the Username and Password fields for MD5 and SCRAM-SHA-256 password-based authentication methods.
  • Username: <user_name>
    Example: user1
  • Password: <password>
    Example: myPassword123
Path Syntax
  • Specific database: <database[:<port>]>
    Example: gldb:9999
  • Specific schema: <database[:<port>]/schema>
    Example: gldb:9999/hr
  • Specific table: <database[:<port>]/schema/table>
    Example: gldb/hr/employees
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
  • Windows Agent with database runtime components
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

  • Username: <database_user_name>
    Example: pgdb1-user1
  • Password: <password>
    Example: myPassword123
Path Syntax

  • Specific database: <database[:<port>]>
    Example: GLDB:9999
  • Specific schema: <database[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table: <database[:<port>]/schema/table>
    Example: GLDB:9999/HRAdmin/Employees
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
  • Windows Agent with database runtime components
  • Windows Agent
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:

  • ASE Express Edition
  • ASE Developer's Edition
Path Syntax
  • Specific database: <database[:<port>]>
    Example: GLDB:9999
  • Specific schema: <database[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table: <database[:<port>]/schema/table>
    Example: GLDB/HRAdmin/Employees
  • Scan a specific Sybase instance (where multiple are running): <database(instance=<instance_name>)[:<port>][/schema][/table]>
    Example: GLDB(instance=Inst2):9999/HrAdmin/Employees
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
  • Windows Agent with database runtime components
  • Windows Agent
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
  • (Not recommended) Scan all locations: [:<port>]
    Example: Leave the Path blank, or :9999
  • Specific user: <user_name[:<port>]>
    Example: userA:9999
  • Specific table belonging to user: <user_name[:<port>]/table>
    Example: userA:9999/accounts
  • Specific database: <database[:<port>]>
    Example: hr
  • Specific table: <database[:<port>]/table>
    Example: hr/employees
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
  • Windows Agent with database runtime components (ER2 2.0.24 and above)
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
  • Specific database: <database[:<port>]>
    Example: GLDB:9999
  • Specific schema: <database[:<port>]/schema>
    Example: GLDB:9999/HRAdmin
  • Specific table: <database[:<port>]/schema/table>
    Example: GLDB/HrAdmin/Employees

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>:

  • MSWIN949 (default)
  • UTF-8
  • UTF-16

To specify the encoding that the Target database is using, use the following syntax:

  • Specific database: <database(encoding=<character_set>)[:<port>]>
    Example: GLDB(encoding=UTF-8):9999
  • Specific schema: <database(encoding=<character_set>)[:<port>]/schema>
    Example: GLDB(encoding=UTF-8)/HRAdmin
  • Specific table: <database(encoding=<character_set>)[:<port>]/schema/table>
    Example: GLDB(encoding=UTF-8)/HRAdmin/Employees
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

  1. From the New Scan page, Add Targets.
  2. In the Select Target Type dialog box, select Server.
  3. In the Enter New Target Hostname field, enter the host name of your database server.
  4. Click Test. If ER2 can connect to the Target, the button changes to a Commit button.
  5. In the Select Types dialog box, click on Database.
  6. In Database, select the DBMS type running on your database server.

  7. In the next window, enter the database connection settings. Fill in the following fields:
    Dialog box to configure the path, credentials and proxy agent for a Microsoft SQL database Target.

    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:

    • New Credential Label: Enter a descriptive label for the credential set.
    • New Username: User name for the database.
    • New 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.
  8. Click Test. If ER2 can connect to the Target, the button changes to a Commit button.
  9. 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:

  1. 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.
  2. As root, open pg_hba.conf in a text editor.

  3. 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 md5

  4. Save the file and restart the PostgreSQL service.