Documentation Version: er2.0.26-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 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 SELECT (data reader) access to the catalogs, schemas, or tables to be scanned.

DBMS Connection Details

The following table lists the supported databases and the settings required for ER2 to connect to and scan them:

DBMS Connection Details
Oracle Database

Default port: 1521

Recommended Agents:

  • Windows Agent with database runtime components

Path syntax:

  • Scan all locations: [:port].

    For example: Leave the Path blank, or :9999.

  • Specific catalog: <schema[:port]>

    For example: schema_1

  • Scan specific table: <schema[:port]/table>

    For example: schema_1/table_1

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 catalog or table using service name:

<schema(SERVICE_NAME=<ServiceName>)[:port][/table]>

For example, schema_1(SERVICE_NAME=GLAB)/table_1


IBM DB2

Default port: 50000

Recommended Agents:

  • Windows Agent with database runtime components

Path syntax:

  • Specific catalog: <database[:port]>

    For example: db_name

  • Specific schema: <database[:port]/schema>

    For example: db_name/schema1

  • Specific table: <database[:port]>[/schema]/table>

    For example: db_name/schema1/table1

Microsoft SQL Server

Default port: 1433

Recommended Agents:

  • Windows Agent with database runtime components

Path syntax:

  • Specific catalog: <catalog[:port]>

    For example: catalog_A

  • Specific schema: <catalog[:port]/schema>

    For example: catalog_A/schema_1

  • Specific table: <catalog[:port]>/schema/table>

    For example: catalog_A/schema_1/table_1

  • Scan a specific SQL Server instance (where multiple are running):

    <catalog(instance=<instance_name>)[:port]>[/schema[/table]]

    For example:

    catalog_A(instance=mssql_instance_1)/schema_1/table_1

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

Default port: 3306

Recommended Agents:

  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent

Path syntax:

  • Scan all locations: [:port].

    For example: Leave the Path blank, or :9999.

  • Specific database: <database[:port]>

    For example: database_A

  • Specific table: <database[:port]>/table>

    For example: database_A/table_1

In MySQL, a "database" may also be referred to as a "schema".
PostgreSQL

Default port: 5432

Recommended Agents:

  • Windows Agent with database runtime components
  • Windows Agent
  • Linux Agent

Path syntax:

  • Specific catalog: <catalog[:port]>

    For example: catalog_A

  • Specific schema: <catalog[:port]/schema>

    For example: catalog_A/schema_1

  • Specific table: <catalog[:port]>/schema/table>

    For example: catalog_A/schema_1/table_1

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

Default port: 3638

Recommended 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 catalog: <database[:port]>

    For example: database_A

  • Specific schema: <database[:port]/schema>

    For example: database_A/schema_1

  • Specific table: <database[:port]>/schema/table>

    For example: database_A/schema_1/table_1

  • Scan a specific Sybase instance (where multiple are running):

    <database(instance=<instance_name>)[:port]>[/schema[/table]]

    For example:

    database_A(instance=sybase_instance_1)/schema_1/table_1

In Sybase ASE, a "database" may also be referred to as a "catalog".
Teradata

Default port:1025

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

    For example: Leave the Path blank, or :9999.

  • Specific user: <user_name[:port]>

    For example: db_user

  • Specific table belonging to user: <user_name[:port]/table>

    For example: db_user/table_1

  • Specific database: <database[:port]>

    For example, database_A

  • Specific table in database: <database[:port]/table>

    For example, database_A/table_1

Other notes

Teradata scans may create temporary tables in the default database. See Teradata FastExport Utility Temporary Tables erecon_fexp_* for more information.

Tibero

Default port: 8629

Recommended 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

Tibero Targets are licensed by data allowance. See Licensing for more information.

Path syntax

  • Specific database: <database[:port]>
    For example, database_A
  • Specific schema in database: <database[:port]/schema>
    For example, database_A/schema_A
  • Specific table in database: <database[:port]/schema/table>
    For example, database_A/schema_A/table_1

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]>
    For example, database_A(encoding=UTF-8)
  • Specific schema in database: <database(encoding=<character_set>)[:port]/schema>
    For example, database_A(encoding=UTF-8)/schema_A
  • Specific table in database: <database(encoding=<character_set>)[:port]/schema/table>
    For example, database_A(encoding=UTF-8)/schema_A/table_1

Other notes

Tibero scans current have a few limitations. See Tibero Scan Limitations for more information.

IBM Informix

Default port: 9088

Recommended 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]>
    For example, ol_informix1210/stores_demo
  • Specific schema in database: <instance/database[:port]/schema>
    For example, ol_informix1210/stores_demo/user_A
  • Specific table in database: <instance/database[:port]/schema/table>
    For example, ol_informix1210/stores_demo/user_A/customer

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 the 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: 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, only add configuration statements that are specific. For example: host database_A scan_user 172.17.0.0/24 md5

    pg_hba.conf

    The pg_hba.conf file accepts statements that follow this syntax:

    # Syntax: # host <database_name> <postgresql_user_name> <agent_host_addr> <method>
  4. Save the file and restart the PostgreSQL service.