Database Data Stores

• The tables in the PosgresQL database must have a Primary Key (PK), otherwise the scan results may be incomplete.

• PostgreSQL by default blocks remote connections to the PostgreSQL server, so you have to configure it to allow remote connections. For instructions, see Allowing Remote Connections to PostgreSQL Server.

• To connect to Microsoft SQL DDC requires the ODBC drivers to be installed in the same environment as the DDC agent. If DDC cannot find a suitable agent, make sure that these drivers are installed. If necessary, upgrade them to the latest available version. Thus, if your MSSQL Server is configured with TLS 1.2 only, install the ODBC Driver 17 (or newer) for MSSQL Server.

• Before adding an Oracle database, make sure that you have the schema name or the database and service name to hand. For information on how to get this information, see Obtaining the Oracle Configuration Details.

Adding Database Data Stores

Use the Add Data Store wizard to add a database type data store. Adding a database data store involves the steps described in the following sections.

1. Select Store Type

1. In the Select Store Type screen of the wizard select Database in the Select Data Store Category.

2. From the Select Database Type drop-down list select:

   • IBM DB2: Select to add an IBM DB2 database.

   • Oracle: Select to add an Oracle database

   • Microsoft SQL: Select to add a Microsoft SQL database.

   • PostgreSQL: Select to add a PostgreSQL database.

   • SAP HANA: Select to add a SAP HANA database.

   • MySQL: Select to add a MySQL database.

   • MongoDB: Select to add a Mongo DB database.

3. Click Next to go on to the Configure Connection screen.

2. Configure Connection

1. In the Configure Connection screen of the wizard, provide the following configuration details for your data store:

   • IBM DB2

     IBM DB2 is a relational database providing data management and analytics capabilities for transactional workloads.

     Note

     DB2: Windows Agent built-in drivers are required to connect to a DB2 data store.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for IBM DB2 is 50000.

     • In the Database field, specify the name of the database service.

     • In the Authentication part, specify valid user credentials, User and Password.

     In effect, after you specify the hostname and port, the path syntax for a IBM DB2 data store target should look like this:

     • For a specific database: <database[:<port>]> For example: DDCDB:65535

     • For a specific schema: <database[:<port>]/schema> For example: DDCDB:65535/HRAdmin

     • For a specific table: <database[:<port>]/schema/table> For example: DDCDB/HRAdmin/Employees

   • ORACLE

     Note

     Windows and Linux Agent built-in drivers are required to connect to an Oracle data store.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for Oracle is 1521.

     • In the Database field, specify the name of the database service.

       Use a schema nameSCHEMA or a database name and service nameDB(SERVICE_NAME=XXX). For example:
       * Schema name: HR
       * Database name and service name: MYDB(SERVICE_NAME=XE)
       If you are using Oracle 12x, or if the Oracle database displays a TNS: protocol adapter error, you must specify a database and service name in the Database field. For example: HR(SERVICE_NAME=XE)
       

       Note

       Due to a known limitation, when creating an Oracle DB data store the agents cannot be assigned if the Schema (which is defined in the Database field) does not contain any tables.

     • In the Authentication part, specify valid user credentials, User and Password.

     For details of the configuration of the Oracle data store, see Oracle Database.

   • MICROSOFT SQL

     Microsoft SQL Server is a relational database management system designed for storing and retrieving data as requested by other software applications.

     Note

     Windows host ODBC drivers are sufficient to connect to a MS SQL data store. ODBC Drivers version 17 are required to support TLS 1.2 connections.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for Microsoft SQL is 1433.

     • In the Database field, specify the name of the database service.

     • In the Authentication part, specify valid user credentials, User and Password.

     The path syntax for a Microsoft SQL data store target should look like this:

     • For all locations: [:<port>] For example: Leave the Path blank, or :65535

     • For a specific database: <database[:<port>]> For example: DDCDB:65535

     • For a specific schema: <database[:<port>]/schema> For example: DDCDB:65535/HRAdmin

     • For a specific table: <database[:<port>]/schema/table> For example: DDCDB:65535/HRAdmin/Employees

     • To scan a specific SQL Server instance (with multiple instances running): <database(instance=<instance_name>)[:<port>][/schema][/table]> For example: DDCDB(instance=MsSQLInst2):65535/HrAdmin/Employees

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

     • For all locations: (paged=false)[:<port>] For example: Leave the Path blank, or (paged=false):65535

     • For a specific database: <database(paged=false)[:<port>]> For example: DDCDB(paged=false):65535

     • For a specific schema: <database(paged=false)[:<port>]/schema> For example: DDCDB(paged=false):65535/HRAdmin

     • For a specific table: <database(paged=false)[:<port>]/schema/table> For example: DDCDB(paged=false):65535/HRAdmin/Employees

   • POSTGRESQL

     Note

     Windows and Linux agent built-in drivers are required to connect to a PostgreSQL data store. The built-in driver does not support password authentication with 'scram-sha-256' method.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for PostgreSQL is 5432.

     • In the Database field, specify the name of the database service.

     • In the Authentication part, specify valid user credentials, User and Password.

     For details of the configuration of the PostgreSQL data store, see PostgreSQL.

   • SAP HANA

     Note

     Windows Agent built-in drivers are required to connect to a SAP Hana data store. If the Agent host has SAP HANA ODBC drivers installed, the Agent will use those drivers instead of its built-in drivers.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for SAP HANA is 30015.

     • In the Database field, specify the name of the database service.

     • In the Authentication part, specify valid user credentials, User and Password.

   • MYSQL

     Note

     Windows and Linux Agent built-in drivers are required to connect to a MySQL data store. The built-in driver does not support password authentication with 'caching_sha2_password' method.

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for MySQL is 3306.

     • In the Authentication part, specify valid user credentials, User and Password.

   • MONGODB

     • Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.

     • Specify Port of the database server. The port must be a number between 1 and 65535. The default port for MongoDB is 27017.

     • In the Authentication Database field, specify the name of the database service.

     • User and Password - specify the Username, password and authentication database in the following manner:

       • Username: <authentication_database>/<user_name>
         Example: pgdb1/user1
       • Password: <password>
         Example: myPassword123

2. The Agent Selection section allows you to specify the minimum and maximum number of proxy agents when adding a datastore. Employing a group of agents instead of a single agent to run the scan should improve the scan execution time.

   In the Select Number of Agents menu set the number of agents for the datastore:

   • Minimum: - Set the minimun number of agents to use to scan the datastore. At least that number of proxy agents must be able to connect to the datastore.

   • Maximum: - Set the maximum number agents to use to scan the datastore.

   Warning

   • As there is no limit on the number of minimum and maximum agents that you can set, you should exercise caution so that you do not impact the system performance by using too many resouces for a single scan.
   • You will not be able to add a datastore if the minimum number of agents cannot be assigned.
   • A scan will fail if the assigned agent is unavailable after adding the datastore.
   • The minimum number of agents must be less than or equal to the maximum number of agents.

3. In the Add Label: field, add an agent label, by entering a label or removing and existing label. Agent labels represent the agent capabilities.

4. Click Next to go to the General Info screen.

3. General Info

1. Configure the General Info part per the information in General Info.

2. Click Next to go to the Add Tags & Access Control screen.

4. Add Tags & Access Control

1. Configure the Tags & Access Control par per the information in Tags & Access Control.

2. Click Save. The newly created data store appears on the Data Stores page. By default, data stores are displayed in alphabetic order by name. Depending on the number of entries per page, you might need to navigate to other pages to view the newly created data store.

Oracle Database

Oracle Database is a database commonly used for running online transaction processing, data warehousing, and mixed database workloads. The table below lists the default and recommended settings for the Oracle data store, with examples.

Obtaining the Oracle Configuration Details

• To find the schema for the current user you can run this query:

  SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
  

• To find the schema (or owner) for a particular table, you can run:

  SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS 
  WHERE OBJECT_TYPE = 'TABLE' 
  AND OBJECT_NAME = '[your table]';
  

• To find all tables for a particular schema (or owner), you can run:

  SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'
  AND OWNER = '[your schema]';
  

• To get the information about the service name contact your Oracle database administrator.

PostgreSQL

PostgreSQL is a free and open-source relational database management system designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. The table below lists the default and recommended settings for the PostgreSQLOracle data store, with examples.

Allowing 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. Open pg_hba.conf in a text editor, as root.

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
   

   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:
   

   Note

   host database_A scan_user 172.17.0.0/24 md5

4. Open the postgresql.conf file and modify the Connections and Authentication section.

   You should change the #listen_addresses = 'localhost' line to this:

   listen_addresses = '*'
   

   Tip

   You can also use a specific IP address of the PostgreSQL server to listen on, instead of the global *.

5. Save the file and restart the PostgreSQL service.