A database node represents a single database from an SQL level. CI types such as users, roles and table schema are included in the scan. A Windows connection manager is required. Once this is in place, ODBC connections are made from the connection manager to the target database.

Prerequisites

  1. To scan a database node you will need a Windows connection manager.
  2. The ODBC connection string (and a database username/password) for the database you would like to scan.
  3. The Windows connection manager will need to be able to communicate to the database using the port specified in the ODBC connection string.

Installing ODBC Drivers

You can manage ODBC drivers in Windows using the ODBC Data Source Administrator (64-bit) application.

The “Drivers” tab displays a list of the currently installed ODBC drivers. Refer to the steps below to install the correct ODBC driver before proceeding.

Microsoft SQL Server

64-bit ODBC drivers come pre-installed on Windows Server 2012 and 2008. Additional installation steps are not typically necessary.

For SQL database nodes running on Microsoft Azure, the 64-bit Microsoft SQL Server 2012 Native Client driver is required. This driver can be found here, in the expanded “Install Instructions” subsection, under the “Microsoft® SQL Server® 2012 Native Client” subheading.

MySQL

You can download the MySQL Connector/ODBC driver from here. A free Oracle login account is required to download this driver.

Oracle

You can download the “Oracle ODAC 12.1.0.2.1 x64” driver from here. You will need to accept the license agreement and log in using your Oracle account before you can download this driver.

PostgreSQL

You can download the PostgreSQL ODBC driver from here.

DB2

You can download the IBM DB2 ODBC CLI driver from here.

Adding

  1. You can add a database node to UpGuard by clicking on the Add Node button from the Nodes page and then clicking on the Database node type.

    w400

  2. The following form allows you to specify the details for your database node.

    database-02

    database-03

User Permissions

The user specified in the ODBC connection string must be able to select on the following tables/catalogs to be able to completely scan a database node.

Database Tables/Catalogs
DB2 syscat.columns, syscat.indexes, syscat.roles, syscat.routines, syscat.tables, sysibm.sysuserauth, sysibmadm.dbcfg
Microsoft SQL information_schema.routines, information_schema.columns, sys.databases, sys.database_principals, sys.indexes, sys.objects, sys.schemas
MySQL information_schema.columns, information_schema.global_variables, information_schema.statistics, mysql.user, “SHOW PROCEDURE STATUS”,
Oracle ALL_DB_LINKS, ALL_INDEXES, ALL_MVIEWS, ALL_OBJECTS, ALL_SEQUENCES, DBA_PROFILES, DBA_TABLES, DBA_TAB_COLUMNS, DBA_USERS, ROLE_SYS_PRIVS, SYS.REGISTRY$HISTORY, V$PARAMETER
Postgres information_schema.columns, pg_catalog.pg_roles, pg_catalog.pg_user, pg_indexes, pg_settings, pg_proc
Tags: database odbc