Documentation Version: er2.0.28-docs-1.1

Databases

Direct remedial action for live databases is limited. See Remediating Databases for more information.

This section covers the following topics:

Supported Databases

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.

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.

Recommended 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

See Databases to connect to the database using an ODBC data source.

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.

Recommended Proxy Agents
  • Windows Agent with database runtime components (ER2 2.0.26 and above)
  • Windows Agent (ER2 2.0.26 and above)
Licensing IBM Informix Targets are licensed by data allowance. See Licensing for more information.
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

See Databases to connect to the database using an ODBC data source.

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.

Recommended Proxy Agents
  • Windows Agent with database runtime components
  • Windows Agent
  • 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
In MariaDB, a "database" may also be referred to as a "schema".

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

In Microsoft SQL Server, a "database" may also be referred to as a "catalog".

See Databases to connect to the database using an ODBC data source.

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.

Recommended Proxy Agents
  • Windows Agent with database runtime components
  • Windows Agent
  • 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
In MySQL, a "database" may also be referred to as a "schema".

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

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

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
  • Linux 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".

See Databases to connect to the database using an ODBC data source.

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
Licensing Teradata Targets are licensed by data allowance. See Licensing for more information.
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

Others Teradata scans may create temporary tables in the default database. See Teradata FastExport Utility Temporary Tables erecon_fexp_* 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.
Licensing Teradata Targets are licensed by data allowance. See Licensing for more information.
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
Others Tibero scans currently have a few limitations. See Tibero Scan Limitations for more information.

Add a Database Target Location

  1. From the New Search page, Add Targets.
  2. In the Enter New Target Hostname field, enter the host name of your database server.
  3. Click Test. If ER2 can connect to the Target, the button changes to a Commit button. Click Commit.
  4. On the left of the Select Types dialog, select Database.
  5. In Database, select the DBMS type running on your database server. Click Done.
  6. 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.

    Windows Authentication for Microsoft SQL

    From ER 2.0.21, Windows authentication is supported for Microsoft SQL 2008 and above.

    To use Windows authentication, enter your Windows account credentials:

    • Username: Windows domain and username in the <domain_name\user_name> format.
    • Password: Windows password.

    For more information on Windows or SQL Server authentication modes, see Choose An Authentication Mode.

    Proxy Details

    Select an Agent.

    Agent Requirements

    See DBMS Connection Details for database-specific Agent requirements.

    For optimal performance, use an Agent installed on the database server.

  7. Click Test. If ER2 can connect to the Target, the button changes to a Commit button.
  8. Click Commit to add the Target.

Remediating Databases

Direct remediation is not supported for database Targets. This means that you cannot perform these remedial actions:

However, you can mark locations in the scan results of your database location for further action. For details, see Remediation.

Scanning the Data Store

Instead of running a live database scan, you can run a scan on data store files. This is done by running a Local Storage and Local Memory Target location scan on the data files themselves.

This is not recommended, as:

ER2 records up to the first million primary keys of rows containing matches. After one million primary keys, it continues scanning and recording matches but does not record any more primary keys.

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 Temporary Tables erecon_fexp_*

A Teradata scan may create temporary tables that 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 Target database and stores it in memory, 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
    Secure 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

  4. Save the file and restart the PostgreSQL service.