Enterprise Recon ODBC Reporting

PRO ER2 ODBC Reporting is only available in Enterprise Recon PRO. To find out more about upgrading your ER2 license, please contact Ground Labs Licensing. See Subscription License for more information.

Introduction

Enterprise Recon 2.10.0 ODBC Reporting feature is a standard interface for integrating Enterprise Recon with ODBC-ready client applications, including Business Intelligence (BI) reporting tools such as Microsoft Power BI, Excel, SAP Crystal Reports, and more.

The ODBC Driver provides read-only connectivity to comprehensive Enterprise Recon data through a set of Data Tables that can be used to build tailored reports or dashboards to get valuable insight into the sensitive data risks across your organization. You also have the flexibility to programmatically extract Enterprise Recon data using your preferred ODBC command-line tools (e.g. Windows PowerShell).

The ER2 ODBC Reporting feature supports common SQL commands, allowing you to execute custom SQL queries to retrieve only the data that you need.

For information and instructions on using the ODBC Reporting feature, read the following topics:

• Requirements
• Downloading the ODBC Driver
  • Verify Checksum for ODBC Driver Package File
• Installing the ODBC Driver
• Creating A User DSN
• Verifying the Enterprise Recon Data Source
• Testing the ODBC Connection

Requirements

The following requirements must be met to use the Enterprise Recon 2.10.0 ODBC Reporting feature to access ER2 data:

Downloading the ODBC Driver

To download the ER2 ODBC Driver:

1. Log into the ER2 Web Console.
2. From the top navigation bar, go to Settings > Analysis > ODBC Driver Downloads.

3. On the ODBC Driver Downloads page, download the 64-bit (recommended) or 32-bit Windows ODBC Driver installer.

   Select the appropriate 64-/32-bit ODBC Driver installer based on the client application version.
4. (Optional) Verify the checksum of the downloaded ODBC Driver package file.

Verify Checksum for ODBC Driver Package File

Requires: OpenSSL package.

You can determine the integrity of the downloaded ODBC Driver MSI package by verifying the checksum before installing the ODBC Driver.

1. Download the ODBC Driver MSI package.
2. Run the commands in a terminal to generate the hash value for the ODBC Driver MSI package.

   • MD5 hash (128-bit)

     # Syntax: openssl md5 <path to ODBC Driver MSI package> openssl md5 ./er2_2.2.0-windows-x64-odbc-driver.msi
     Example MD5 hash: f65a2cd26570ddb7efb6a2a4318388ac

   • SHA1 hash (160-bit)

     # Syntax: openssl sha1 <path to ODBC Driver MSI package> openssl sha1 ./er2_2.2.0-windows-x64-odbc-driver.msi
     Example SHA1 hash: 33bcd6678580ae38a03183e94b4038e72b8f18f4

   • SHA256 hash (256-bit)

     # Syntax: openssl sha256 <path to ODBC Driver MSI package> openssl sha256 ./er2_2.2.0-windows-x64-odbc-driver.msi
     Example SHA256 hash: 1ee094a222f7d9bae9015ab2c4ea37df71000556b3acd2632ee27013844c49da

3. In the ER2 Web Console, go to the Settings > Analysis > ODBC Driver Downloads page. The Hash column lists the expected hash values for each ODBC Driver MSI package.

4. Compare the generated hash values from Step 2 with the expected hash values listed in the Web Console; both hash values should be equal.

   If the hash values do not match, check that your network connection is stable, download the ODBC Driver MSI package again from the Web Console, and verify the checksums again. If the issue still persists, contact Ground Labs Technical Support.

Installing the ODBC Driver

The default MSI installation package installs the ER2 ODBC Driver and constructs a System Data Source Name (DSN). The System DSN ("Enterprise Recon 64 System" or "Enterprise Recon 32 System") sets the driver path but does not pre-configure the Master Server address, user account login name or password.

See Creating A User DSN for more information on pre-configuring the connecting string parameters for the ER2 data source.

To install the ER2 ODBC Driver:

1. If there is a previous version of the ODBC Driver installed, remove it first.
2. Run the downloaded 64-/32-bit ODBC Driver installer on the 64-bit Windows host and click Next.
3. In the Choose Setup Type screen, click Install.
4. In the Ready to Install screen, click Install to begin the installation.
5. Click Finish to complete the installation.

You are now ready to Connect to Enterprise Recon Data Source.

Creating A User DSN

You can create a User DSN to pre-configure the Master Server hostname or IP address, ER2 user account login name, and ER2 user account password.

To create a User DSN to connect to ER2:

1. Install the ODBC Driver.

2. Run the following command in the Windows Command Prompt window:

   # Syntax: "C:\Program Files (x86)\Ground Labs\Enterprise Recon 2\ODBC64\er2_odbcinst.exe" -add <data source name> -server <Master Server hostname or IP address> -username <ER2 user account login name> -password <ER2 user account password> "C:\Program Files (x86)\Ground Labs\Enterprise Recon 2\ODBC64\er2_odbcinst.exe" -add "ER2 64 User DSN" -server "10.52.100.230" -username "admin" -password "adminPassword123"

   Option	Description
   add	Specify a name for the User DSN.
   server	Optional parameter. Specify the Master Server hostname or IP address.
   username	Optional parameter. Specify the ER2 user account login name.
   password	Optional parameter. Specify the ER2 user account password.

You are now ready to Connect to Enterprise Recon Data Source.

Verifying the Enterprise Recon Data Source

You can verify that the ER2 System or User DSN is created successfully on the client application host machine.

1. From the Start menu, search for "ODBC Data Sources" to launch the 64-/32-bit ODBC Data Source Administrator. Make sure to use the ODBC Data Source Administrator with the same bitness (64-/32-bit) as the installed ODBC Driver.
2. Click on the System DSN tab.
3. Check that "Enterprise Recon 64 System" or "Enterprise Recon 32 System" is listed under System Data Sources.
4. (Optional) Click on the User DSN tab and check that the created User DSN is listed under User Data Sources.

Alternatively, you can use Windows PowerShell commands to check the installation.

1. Launch Windows PowerShell.

2. In the Windows PowerShell console, run the following command to get the name and the platform (64-/32-bit) of the installed driver:

   # Get-OdbcDriver -Name "<default System DSN or newly created User DSN>" Get-OdbcDriver -Name "Enterprise Recon*"
   

Testing the ODBC Connection

Once the ODBC Driver has been installed, you can test the ODBC connectivity to the ER2 Master Server using the Windows PowerShell commands below.

1. Launch the Windows PowerShell console.

2. Initialize a new instance of the OdbcConnection class.

   $masterServerConnection = New-Object System.Data.Odbc.OdbcConnection

3. Specify the connection string to connect to the ER2 data source:

   # $masterServerConnection.connectionstring = "DSN=<System or User DSN>;SRV=<Master Server hostname or IP address>;UID=<User account login name>;PWD=<User account password>" $masterServerConnection.connectionstring = "DSN=Enterprise Recon 64 System;SRV=10.52.100.230;UID=admin;PWD=admin123456"
   See Connection Requirements for more information about the data source name and connection string.

4. Open a connection to the ER2 data source with the connection string properties defined in the previous step.

   $masterServerConnection.Open()

5. Get the ER2 data source properties. The State property should be "Open".

   Write-Output $masterServerConnection

6. Execute a SQL query to retrieve the DATA_GROUP table. The response should return the Group ID, name, and comments for all the Groups available on the Master Server.

   $sqlCommand = New-Object System.Data.Odbc.OdbcCommand("SELECT * FROM DATA_GROUP",$masterServerConnection) $dataTable = New-Object System.Data.DataTable $dataTableAdapter = New-Object System.Data.Odbc.OdbcDataAdapter($sqlCommand) $numRecords = $dataTableAdapter.fill($dataTable) Write-Output "Number of Groups: ${numRecords}" Write-Output $dataTable

7. Close the connection to the ER2 data source.

   $masterServerConnection.Close()

Alternatively, you can check the ER2 ODBC data source connection by executing the commands as a Windows PowerShell script.

$masterServerConnection = New-Object System.Data.Odbc.OdbcConnection Write-Output "" Write-Output "Getting Enterprise Recon Master Server connection information . . ." Write-Output "" $dsn = Read-Host 'Data source name (e.g. Enterprise Recon 64 System) ' $server = Read-Host 'Hostname or IP address ' $userid = Read-Host 'Username ' $password = Read-Host 'Password ' -AsSecureString $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password) $unsecurePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) $masterServerConnection.connectionstring = "DSN=$dsn;SRV=$server;UID=$userid;PWD=$unsecurePassword" $masterServerConnection.Open() if ($masterServerConnection.State -eq 0) { Write-Output "Not connected to Enterprise Recon Master Server." Write-Output "Check that you have provided the correct IP address, ODBC driver bit value, user name and password." Write-Output "Exiting . . ." Write-Output "" $masterServerConnection.Close() Exit } Write-Output $masterServerConnection $sqlCommand = New-Object System.Data.Odbc.OdbcCommand("SELECT * FROM DATA_GROUP",$masterServerConnection) $dataTable = New-Object System.Data.DataTable $dataTableAdapter = New-Object System.Data.Odbc.OdbcDataAdapter($sqlCommand) $numRecords = $dataTableAdapter.fill($dataTable) Write-Output "Number of Groups: ${numRecords}" Write-Output $dataTable $masterServerConnection.Close()