ODBC User Guide

General Information

The Microsoft Open Database Connectivity (ODBC) based scanning feature in Enterprise Recon (version-1.16) product provides capability to scan local and remote databases from Windows hosts.

This User Guide provides detailed information about using this feature.

 

System Overview

ODBC based scanning can be used to scan supported local or remote databases as depicted in Figure-1.
Figure 1: Components in ODBC Scanning

figure1.png

As shown in Figure-1, the Enterprise Recon Console application requests the ODBC scan on a particular Node server running Microsoft Windows. This scan request is forwarded to the Enterprise Recon Master Server, which then schedules the scan on the requested Node Server. The Node Server connects to the database server in accordance with the ODBC Connection string passed to it from the Enterprise Console, and attempts to read the database content for scanning. The Enterprise Recon Node agent running on the Node server uses the appropriate installed ODBC driver. If the node agent is running on a 64 bit operating system, then the node agent will run the 64 bit scanning engine. If the node agent is running on a 32 bit operating system, then the node agent runs the 32 bit scanning engine. The implication of this is that if a 64 bit operating system is being used, the 64 bit ODBC drivers must be used, as otherwise compatibility problems may be encountered when a 64 bit scanning engine attempts to use 32 bit ODBC drivers.

 

System Requirements and Special Notes

  1. To use the Enterprise Recon ODBC scanning capabilities, the Enterprise Recon Node selected for the scan must be running Microsoft Windows.
  2. The User or System Data Source Name (DSN) must exist in Microsoft ODBC Data Source Administrator (MODSA) with the appropriate ODBC driver provided by the Database vendor installed on the node server from which you want to run this ODBC based scan. Steps to configure User or System DSN are provided in Section-8.
  3. The architecture of the ODBC driver must match that of Enterprise Recon node agent. The 32-bit version of the application requires 32-bit ODBC drivers and the 64-bit application requires 64-bit ODBC drivers.
  4. The firewall on the database server should be configured to accept the incoming connections on the Database service port from the scanning Node agent server.
  5. The User ID used for connecting to database must have SELECT (read-only) privileges on all the schemas and tables for querying the data.
    • A successful connection to database with a User ID does not necessarily guarantee that the User ID has the required privileges to query the data within the database.
    • The User ID should have remote connection privileges in addition to the SELECT privileges for the database to be scanned. Please refer to individual database server manuals to enable these privileges.
  6. Enterprise Recon is designed to reduce the load on the database and network while scanning. For limiting the number of rows fetched, Enterprise Recon uses the primary keys in tables to paginate through the tables.
    • Due to a limitation within Microsoft SQL Server, it is advisable to limit the rows scanned (using Row limit per table option) when there are tables without primary keys in the database as the pagination feature will be unavailable in this case.
  7. By default, the Enterprise Recon node agent runs under the SYSTEM account. Thus, for the scanning engine to use the correct DSN it is necessary to create the DSNs in System DSNtab.
    • However, if the you have changed the enterprise recon service configuration to run the node agent under a user profile other than SYSTEM, the DSNs must be created in the User DSN tab.
  8. On 64 bit Windows operating systems, two MODSA exist.
    • A 64 bit version of MODSA
      This is the default MODSA and is accessible from Start Menu -> Administrative Tools.
      This is a shortcut to %windir%\system32\odbcad32.exe. Usually %windir% is set to point to C:\Windows.
      If you are creating the DSN for 64 bit ODBC drivers, you must use this version of MODSA.
    • A 32 bit Version of MODSA
      This is C:\Windows\SysWOW64\odbcad32.exe and is not available in Start Menu directly.
      If you are creating the DSN for 32 bit ODBC drivers, use this MODSA.
    • If you fail to use the correct version of MODSA, the DSN won't be visible to Enterprise Recon and the database connection cannot be made.
      This is explained in the guide Managing Data Sources http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362(v=vs.85).aspx

 Supported Databases

  1. Oracle
  2. Microsoft SQL Server
  3. Sybase Adaptive Server Enterprise
  4. MySQL
  5. PostgreSQL
  6. IBM DB2
  7. Microsoft Access
  8. Sqlite3

 

How does it work?

This Enterprise Recon feature uses the ODBC drivers installed on a Windows based Node server to connect to and query the data from the target database. A valid connection string generated using the MODSA utility is required for a successful database connection.
CAUTION: ODBC scan selects all the data from tables. It is advisable to limit the number of rows scanned using Row limit per table option.

 

Using ODBC based scanning in Enterprise Recon

ODBC based scanning can be initiated by following these steps in Enterprise Recon.

  1. Launch the Enterprise Recon Console and navigate to the scan configuration tab (Scanning->Scan Hosts Now).
  2. Select Specific Directory in Scan Parameters.
    • The text entry field Root path will appear below the row of radio buttons.
  3. Enter an ODBC connection string
    • Detailed information about how to form ODBC Connection string is provided in Section-8.
    • The ODBC connection string is used to make a connection to the database via the installed ODBC driver on the Node agent host.
    • An optional Row limit per table parameter may be specified which limits the number of rows scanned.
  4. Select the nodes on which to run the scan. Note that only nodes pre-configured with the specified DSN or ODBC drivers should be selected.
  5. Click OK button at the right hand corner to start the scan.

 

How to create an ODBC Connection string suitable for Enterprise Recon

This section provides examples of creating sample ODBC Connection string (ODBCCS).
ODBCCS contains a prefix ODBC:// and the DSN that is configured correctly in MODSA.
According to the database you want to scan and the login ID (e.g. SYSTEM) under which node agent is running, create the User or System DSN in MODSA using the steps given below.

It is out of the scope of this guide to cover all possible DSN configuration for all databases and database versions.
You are advised to create the possible DSN according to your database configuration by working closely with the your DBA.

Oracle

Connections to Oracle are "TNS" based. The DSN settings should specify the parameters that are compliant with the TNS setup for your Oracle database.
The following set of steps show how to create a suitable DSN for connecting to an Oracle 10g Express Edition Database Server.

Configuring a DSN for Oracle 10g Express Edition Database

Syntax: ODBC://DSN=<DSN_NAME>;

  1. Launch the program Data Sources (ODBC) by navigating along Windows Start Menu -> All Programs -> Administrative Tools
    • If your node agent is on a 64 bit Windows operating system, please ensure that the 64 bit Oracle ODBC drivers are installed on the node server.
  2. The Microsoft ODBC Data Source Administrator window will open.
  3. If your node agent is running as SYSTEM user (the default), then click on System DSNtab.
    • If your node agent is running as non-SYSTEM user, then click on User DSN tab.
  4. Click Add button
  5. The Create New Data Source wizard will open.
  6. Select the Oracle Driver to use for this DSN and click the Finish button.
    The Oracle ODBC Driver Configuration wizard will open.
  7. Enter the top four fields and click the Test Connection button to test the DSN.
  8. Save the configuration if the connection succeeds.
    NOTE: For more information about configuring the DSN, use the Help provided by clicking on Help button.
    1. Using this DSN and form the ODBCCS according to the syntax given above.
    • Brief information about the fields to be populated:
      • Data Source Name: This DSN can be used in forming DSN based ODBCCS shown above. Using the example DSN (Oracle10_DSN) shown in Step-5, a sample ODBCCS will look like: ODBC://DSN=Oracle10_DSN
      • TNS Service Name: Its format is OracleDBServerName:PortNumber/InstanceName. Before configuring this DSN, the network database services must be configured so that there is an entry for each TNS Service Name. For this follow the steps given in the Help page. The help page is opened when the "Help" button on the Oracle ODBC Driver Configuration dialog box is clicked.
      • User ID: This field specifies the User ID and password (in the format : User/Pass ) to be used to connect to the database. Passwords must be specified in clear text.
      • If the correct password is not entered here, Enterprise Recon will be unable to connect to the database.

    Microsoft SQL Server

    SQL Server DSN strings differ depending on whether the target database is SQL Server (MSSQL) or SQL Server Express (MSSQLX).
    MSSQL and MSSQLX provide two forms of authentication mechanisms:

    1. Windows NT authentication using the network login ID. This is also called "Trusted Connection".
    2. SQL Server authentication using a login ID and password entered by the user.
      The SQL Server Authentication primer http://msdn.microsoft.com/en-us/library/ms144284.aspx provides detailed information on these mechanisms.

    Special notes for SQL Server

    If Enterprise Recon node agent is running under SYSTEM account and SQL Server is running in Windows NT Authentication mode, then the login to SQL Server fails.
    There are two solutions to successfully connect to SQL Server for scanning:

    1. Configuring the Enterprise Recon node agent to run under non-SYSTEM account:
      • Re-configure the Enterprise Recon node agent service to run under a Windows login. Your SQL Server DBA must have associated this Windows login with a SQL Server login ID.
      • In this case, SQL Server is assumed to be running in Windows NT authentication mode and you will have to choose Windows NT Authentication mode in Create New Data Source wizard in DSN creation Step No. 7 in Section-8.2.2 below.
      • While creating the DSN for this connection, login to Windows host using this same login ID which is associated with a SQL Server login ID.
    2. Configuring the SQL Server to run in mixed mode, i.e. SQL Server and Windows Authenticationmode:
      • Work with your SQL Server DBA to know about the current authentication mode in which SQL Server is running.
      • If SQL Server is running in Windows NT authentication mode (and if you do NOT want to adapt the option 1), then perform the changes according to guide Change Server Authentication Mode http://msdn.microsoft.com/en-us/library/ms188670.aspx.
      • Create a user in SQL Server according to guide Create a Login http://msdn.microsoft.com/en-us/library/aa337562.aspx. This userid and its password will be used for connecting to SQL Server from Enterprise Recon.
      • Changing to mixed mode authentication may have few implications. Two major implications of this are:
        • After changing this setting, SQL Server will have to be restarted (as already mentioned in the guide).
        • Since, SQL Authentication requires complex password according to password policy, users that do not meet this password criteria may face issues in connecting to SQL Server after this authentication mode change.
    3. Using the file scanning feature of Enterprise Recon
      • Enterprise Recon is capable of file level decoding of SQL Server databases. ODBC related configuration issues can be avoided by performing a file level scan of an offline version of the SQL Server database, or a back up copy created using the "Full Backup" and "Copy Only" options of the Microsoft SQL Server backup tool.

    The next set of steps show how to create a valid ODBCCS for connecting to MSSQL and MSSQLX Database Servers.

    Configuring a DSN for SQL Server 2008 and SQL Server Express 2008

    Syntax: ODBC://DSN=<DSN_NAME>; DataBase=<dbname>;
    Steps to configure a DSN to MSSQL and MSSQLX:

    1. Launch the program Data Sources (ODBC) by navigating to Windows Start Menu -> All Programs -> Administrative Tools
      • If your node agent is on 64 bit Windows operating system, then ensure that the 64 bit Microsoft SQL Server ODBC drivers are installed on the node server.
    2. The Microsoft ODBC Data Source Administrator window will open.
    3. If your node agent is running as SYSTEM user (the default), then click on System DSNtab.
      • If your node agent is running not as SYSTEM user, then click on User DSN tab.
    4. Click the Add button
    5. The Create New Data Source wizard will open.
    6. Select the appropriate ODBC Driver for the SQL Server database, and click the Finish button.
    7. The Create a New Data Source to SQL Serverwizard will open.
      • Enter the Name (DSN) and Description fields. Select the appropriate SQL Server to be scanned from the list.
      • Note the different naming styles for Server names for SQL Express and SQL Server databases. If this drop down list does not show any databases, then enter the full name of the database server that you wish to scan.
      • Click Next button to proceed to the next step.
    8. Select the appropriate authentication mechanism according to the Special notes for SQL Server for ODBC Scanningmentioned above.
      • Click the Next button to proceed to the next step.
    9. Select the database to connect to if you do not want to scan the default database "master".
      • Click the Next button to proceed to the next step.
    10. Populate the information (if required) in the wizard and click the Finish button.
      • The Test Data Source wizard will open.
    11. Click the Test Data Source button to test this data source.
    12. Save this data source configuration if the test succeeds.
      • NOTE: For more information about configuring the DSN, use the Help provided by clicking on Help button.
    13. The ODBCCS is formed using this DSN as below:
      • If you are using Windows authentication mode (or trusted connection), then ODBCCS will be simply:
        ODBC://DSN=<DSN_NAME>;
      • If you are using SQL Server authentication mode, then you need to provide the login id and password registered in SQL Server:
        ODBC://DSN=<DSN_NAME>;uid=<login-id>;pwd=<PASS>;
    • Brief information about the fields to be populated:
      • Name: This is the DSN entered in the Create a New Data Source to SQL Server wizard. It can be used in forming DSN based ODBCCS shown above.
      • Server: This field differs depending on whether you are connecting to SQL Server or SQL Server Express. If you are connecting to SQL Server, this will be the server name as shown in the list in this wizard. If you are connecting to SQL Server Express, this will be servername\SQLEXPRESS, like MASK\SQLEXPRESS as shown in Step-5.
      • Authentication mechanism: Select the appropriate authentication mechanism and provide the required details.
      • Default database: By default, if you do not select any database, on connecting to MSSQL and MSSQLX, server provides master database. Change this to the appropriate database that you want to scan.
      • Help: Use the Help button to get the Help about ODBC Driver and DSN configuration for SQL Server.

    IBM DB2

    The following steps show how to create an ODBCCS for connecting to an IBM DB2 database.

    Configuring a DSN for IBM DB2 database server

    Steps to configure a DSN to IBM DB2:

    1. Launch the program Data Sources (ODBC) by navigating to Windows Start Menu -> All Programs -> Administrative Tools
      • If your node agent is on 64 bit Windows operating system, then ensure that 64 bit IBM ODBC drivers are installed on the node server.
    2. The Microsoft ODBC Data Source Administrator window will open.
    3. If your node agent is running as the SYSTEM user (the default), then click on System DSNtab.
      • If your node agent is running not as the SYSTEM user, then click on User DSN tab.
    4. Click the Add button
    5. The Create New Data Source wizard will open.
    6. Select the appropriate ODBC Driver for the IBM DB2 database to be scanned and click the Finish button.
    7. The ODBC IBM DB2 Driver - Addwizard will open.
      • Enter the DSN of your choice, and select the database to connect to.
      • Click Add button to enter more details.
      • The CLI/ODBC Settings - IBM DB2 wizard will open.
    8. Enter the User ID and Password and select Save password.
      • Populate the information in other tabs based on your IBM DB2 database configuration.
      • Click the OK button, test, and save the DSN configuration.
    9. Using this DSN form the ODBCCS according to the syntax given above.

    Sybase

    The following steps show how to create ODBCCS string for the Sybase Adaptive Server Enterprise (ASE) database server.

    Configuring a DSN for Sybase ASE database server

    Steps to configure a DSN to Sybase ASE:

    1. Launch the program Data Sources (ODBC) by navigating along Windows Start Menu -> All Programs -> Administrative Tools
    2. The Microsoft ODBC Data Source Administrator window will open.
    3. Click the Add button on User DSN tab and the Create New Data Source wizard will open.
    4. Select the Adaptive Server Enterprise driver and click the Finish button.
    5. The Adaptive Server Enterprise wizard will open.
    6. Enter the required information in the various fields according to your Sybase ASE database configuration.
    7. Test the connection and save the configuration.
    8. Using this DSN and form the ODBCCS according to the syntax given above.

    MySQL

    The following set of steps detail the procedure for creating an ODBCCS string for MySQL Community Edition database server.

    Configuring a DSN for MySQL community server

    Use the MySQL ODBC Connection guide http://dev.mysql.com/doc/refman/5.6/en/connector-odbc-configuration-dsn-windows.html for adding a DSN to MySQL in MODSA.
    Using this MySQL DSN, ODBCCS can be formed as:
    ODBC://DSN=<MYSQL_DSN_NAME_AS_IN_MODSA>;

    PostgreSQL

    Configuring a DSN for PostgreSQL database server

    Use the Postgresql FAQ http://psqlodbc.projects.postgresql.org/faq.html for adding a DSN to Postgresql in MODSA.

    Row limit per table option:

    Production level databases often contain many tables with millions of records. To avoid the impact of querying such a large quantity of data, the amount of data scanned can be limited by specifying a number of rows in ODBC connection string.
    This optional parameter should be appended to the connection string separated by ? (question mark) and ended with ;.
    ODBC://DSN=<DSN_NAME_AS_IN_MODSA>;?rows=<ROWS>;

    • If this option is not included while scanning the Microsoft SQL Server and if there are tables without primary key, default row limit per table of 1024 is assumed to avoid queries that may run longer on huge tables. As a result of this, you will get an error message "Reporting limit of row scans reached" in the scanning report for tables without primary key.
    • If this option is not included while scanning the databases other than Microsoft SQL Server, all the rows in all the accessible tables will be scanned.

    Error Reporting

    The errors that can occur in this scanning are reported in the data scan report. The major errors and the messages reported are provided in this section.

    1. Scan initiated on unsupported database.
      This error occurs when scan is initiated on an unsupported database. Supported databases are listed in Section-4. No scan will be performed and the following error message will be reported:
      Database specified by DSN is not supported for scanning
      Remedy: Request the scan on a supported database.
    2. Architecture mismatch
      If the user attempts to scan the 64-bit (/ 32-bit) DSN with 32-bit (/64-bit) application, no scan will be performed and the following error will be reported:
      The specified DSN contains an architecture mismatch between the Driver and Application
      Remedy: Use the DSN (and ODBC driver) that matches the application.
    3. Database connection related error
      This error may occur if the server name is incorrect, server does not exist or database is unreachable. No scan will be performed and the following error message will be reported:
      Connection to specified DSN failed, check if server name is correct and database is running
      Remedy: Ensure that server name is correct, server exists and database is reachable.
      • If you get this error while running scan on SQL Server (or SQL Server SQLEXPRESS), ensure that you have implemented one of the two options specified in Special notes for SQL Server in Section-8.2.
    4. User id and/or password related errors
      This error may occur if user name or password are incorrect, the user name does not exist on the database, or the server does not exist. No scan will be performed and the following error message will be reported:
      User or password is incorrect or server does not exist
      Remedy: Ensure that user name, password are correct, user name exists on the database to be scanned and database server exists.
    5. User has no SELECT privileges on meta data schemas
      This error may occur if the user does not have SELECT privileges on meta data schemas. This SELECT privilege is required to get the list of all the schemas and tables available in this database. The connection may succeed, however no scan will be performed as application cannot read the table data.
      Connection to database successful, but no scannable tables found, check SELECT permissions
      Remedy: Ensure that user name provided in ODBC string has the SELECT privileges on meta tables. E.g. SHOW DATABASES privilege in MySQL.
    6. DSN not found
      This error occurs when the DSN is not created in MODSA or driver name included in the connection string does not exist on the system. No scan will be performed and the following error message will be reported:
      Data source name not found and no default driver specified
      Remedy: Configure the DSN or install the appropriate driver as outlined in Section-8.1.
    7. No data in table or no SELECT privileges on tables
      This error occurs if the user name has no SELECT privileges on physical tables, or the user name has SELECT privileges on physical tables but there is no data in these tables. No scan will be performed and the following error message will be reported:
      Connection to database successful, but either user has no SELECT permissions on table or there is no data in table
      Remedy: Check the user privileges and tables for data content.
    8. Query prepare failures
      This error relates to ODBC driver related errors because of which application could not retrieve the required information (meta data) about the tables to be scanned or failed to prepare the query on server side. No scan will be performed and the following error message will be reported:
      Failed in retrieving columns, primary key or in preparing query
      Remedy: Check the available system memory and ODBC driver logs for more information on the failure. If the problem persists, contact GroundLabs support with the appropriate information.
    9. Reporting limit of row scans reached
      This is not error. This information message will be reported in the report when application finishes scanning the number of rows specified in the Row limit per table option mentioned above. This message suggests that the scan was successful, however not all table rows were scanned due to the predefined limit. If there are any matches they will be made available in the report.
      Reporting limit of row scans reached

    ODBC Driver compatibility matrix

    While testing this feature with the various supported databases, few issues were noticed with the ODBC drivers. To avoid these issues while scanning, it is recommended to use the following the versions of ODBC drivers if you face any issues with the versions other than listed below.

    Oracle ODBC Drivers

    Enterprise Recon ODBC scanning feature is tested extensively with Oracle 10g and 11g Express Edition databases. The ODBC Support matrix is as below.

    S.No.

    Architecture

    Oracle Database Server

    ODBC Driver Version

    ODBC Driver type

    Remarks

    1

    64 bit

    10g Express Edition

    10.02.00.04

    10g Oracle Instant Client

    Works fine. Driver available at Instant Client Downloads for Microsoft Windows (x64), http://www.oracle.com/technetwork/topics/winx64soft-089540.html

    2

    64 bit

    10g Express Edition

    11.02.00.03

    11g Oracle Instant Client

    ODBC Driver has defects which causes crash during scanning. Recommend using the driver package mentioned in S.No.1

    3

    64 bit

    11g Express Edition

    10.02.00.04

    10g Oracle Instant Client

    Works fine. Driver package same as S.No.1

    4

    64 bit

    11g Express Edition

    11.02.00.03

    11g Oracle Instant Client

    Works fine. Driver available at Instant Client Downloads for Microsoft Windows (x64), http://www.oracle.com/technetwork/topics/winx64soft-089540.html

    5

    32 bit

    10g Express Edition

    10.02.00.01

    10g Oracle Express Edition Client

    Works fine. Use the Oracle Database 10g Express Client available at http://www.oracle.com/technetwork/topics/winx64soft-089540.html

    6

    32 bit

    10g Express Edition

    11.02.00.03

    11g Oracle Instant Client

    ODBC Driver has defects which causes crash during scanning. Recommend using the driver package mentioned in S.No.5

    7

    32 bit

    11g Express Edition

    10.02.00.01

    10g Oracle Express Edition Client

    Works fine. Driver package same as S.No.5

    8

    32 bit

    11g Express Edition

    11.02.00.03

    11g Oracle Instant Client

    Works fine. Driver available at Instant Client Downloads for Microsoft Windows (x64),

    ODBC Uri format

    A sample uri format should be

    odbc://dsn=xe_client;usr=weeyoung;pwd=xxxxxxx;

    odbc://dsn=xe_client;usr=weeyoung;pwd=xxxxxxx;?rows=1000;

    odbc://dsn=xe_client;usr=weeyoung;pwd=xxxxxxx;//APEX_040000/WWV_FLOW_LIST_ITEMS/;?rows=1000;

    Points to note

    1. If a table is specified and there is credentials set; the tables should be after the credentials.
      1. odbc://dsn=xe_client;usr=weeyoung;pwd=xxxxxxx;//APEX_040000/WWV_FLOW_LIST_ITEMS/ is correct
      2. odbc://dsn=xe_client;//APEX_040000/WWV_FLOW_LIST_ITEMS/;usr=weeyoung;pwd=xxxxxxx; is wrong
    2. The db name should be in this format //<db_name>/<table_name>/
      1. There must be a // in front
      2. There must be a ending slash / at the back
      3. There must be both db and table. Only db name does not work
    3. The row limit should be have the following format ?rows=<no>;
      1. There must be a ?
    4. There must be a ; at the end of the uri.
Have more questions? Submit a request

0 Comments

Article is closed for comments.