Enterprise Recon 2.1

All Documentation > ER 2.1 > Scan Locations (Targets) Overview > Add Targets > Add Server Target > Databases

Databases

This section covers the following topics:

Supported Databases
Licensing
Requirements
DBMS Connection Details
Add a Database Target Location
Remediating Databases
Scanning the Data Store
InterSystems Caché Connection Limits
Tibero Scan Limitations
Teradata FastExport Utility Temporary Tables erecon_fexp_*
Allow Remote Connections to PostgreSQL Server

Supported Databases

IBM DB2 11.1 and above.
IBM Informix 12.10.
NEW InterSystems Caché 2017.2 and above.
MariaDB.
Microsoft SQL 2005 and above.
NEW MongoDB 4.0 and above.
MySQL.
Oracle Database 9 and above.
PostgreSQL 9.5 and above.
Sybase/SAP Adaptive Server Enterprise 15.7 and above.
Teradata 14.10.00.02 and above.
Tibero 6.

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.

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.

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.

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

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

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

InterSystems Caché NEW

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

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

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: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 In Microsoft SQL Server, a "database" may also be referred to as a "catalog".

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

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

MongoDB NEW

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

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

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

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

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 with database runtime components 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.

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

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

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

From the New Scan page, Add Targets.
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. Click Done.

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: 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 ER2 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. See DBMS Connection Details for database-specific Agent requirements. For optimal performance, use an Agent installed on the database server.

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

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.

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.

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:

Data store files are locked during the normal operation of a live database. Unlocking the data files requires the database to be taken offline.
Scanning data store files will match ghost records, and may include data that has already been removed from the live database.
Encrypted data files are not scanned as they are considered secure — but you may still want to scan the live database itself for sensitive data.

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.

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

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

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 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.
Save the file and restart the PostgreSQL service.