SQL Configuration Options

An additional set of local.cfg options are used to tell Agents which SQL databases to scan, and how to connect to them. An example local.cfg including SQL DB connection info is as follows:

{
    \"agent\" : {
        \"server\" : \"111.222.333.444\",
    },
    \"scan_databases\" : {
        \"db_scan_name\" : {
            \"q_module\" : \"mysql\",
            \"connect\"  : \"host=172.16.2.14;user=fred;passwd=fred123;db=db_crm\"
        },
        \"an_sqlserver_db\" : {
            \"q_module\" : \"sqlserver\",
            \"connect\"  : \"Server=172.16.2.14;Database=db_working;User Id=sa;Password=pass123;\",
            \"topnrows\" : 70000
        },
        \"another_sqlserver_db\" : {
            \"q_module\" : \"sqlserver\",
            \"connect\"  : \"DRIVER={SQL Server};SERVER=127.0.0.1;Trusted_Connection=no;DATABASE=AdventureWorks2008R2;UID=sa;PWD=blahblah\"
        },
        \"db_scan_msaccess\" : {
            \"q_module\" : \"msaccess\",
            \"connect\"  : \"Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\path\\to\\db.mdb\"
        },
        \"ora_aws_test\" : {
             \"q_module\" : \"oracle\",
             \"connect\" : \"host=previous-db-oracle-se1-test-jhsdkjfhdf.ap-southeast-2.rds.amazonaws.com;user=exampleuser;password=weriuoewyr;SID=ORCL\"
        }
    }
}

NOTE: Backslashes in JSON must be doubled up for escaping purposes, otherwise obscure error messages may result.

Scanner Settings

Common sql_scanner settings are as follows:

topnrows
Limits the number of rows to scan. This translates into ‘SELECT TOP n’ in MS SQL, for example.

Suitable values for this setting are best derived by your card data analysts and SQL DBAs working together to determine a sampling policy. It is not recommended to set this to an unduly high number or leave it out altogether as large tables could result in unrealistic scan times.

Database Settings

Database-specific settings (in scan_databases) are as follows:

db_scan_name
“quasar-centric” alias name for the database being scanned. This is user defined, and appears in hits paths and report outputs.

q_module
Type of database. Supported DB types are mysql, sqlserver, and msaccess.

connect
The database ODBC connection string.

See http://connectionstrings.com for more information.

topnrows
As above. This is a database-specific override of the common setting.