Skip to main content

SQL Connector

Structured Query Language (SQL) is a programming language used to manage and manipulate data stored in relational databases. There are several popular relational database management systems that support SQL, including MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. Each of these systems has its own syntax and specific features, but they all use SQL as their primary language for managing and querying data.

If your organization uses a relational database to store asset or vulnerability information, you can use the SQL Connector in Brinqa to import your data. Brinqa can process, analyze, and visualize such data to construct a unified view of your attack surface and strengthen your cybersecurity posture.

This document details the information you must provide for the connector to assess your database and retrieve data. See create a data integration for step-by-step instructions on setting up the integration.

Connection settings​

When setting up a data integration, select SQL Connector from the Connector drop-down. If you cannot find the connector in the drop-down, make sure that you have installed it first. You must provide the following information:

  • Server: To establish a secure tunnel and maintain connectivity between your database and the Brinqa Platform, you may need to install a Brinqa Agent on the machine and create a data server for it. If this is required, select the data server that you have created.

  • Connection URL: The URL to connect to your database.

    note

    If you're using Java Naming and Directory Interface (JNDI), enter the JNDI context instead and select the Specified connection URL is a JNDI context option.

  • Username and Password: Specify the username and password of a database account, which must have permissions to log in to the database and return data. If using private key-based authentication, these fields are not required.

  • Private key: Enter the private key used for secure authentication to the SQL database. This key is typically utilized in SSH or SSL protocols to establish a secure, encrypted connection. The key must be in the P8/PEM (Privacy Enhanced Mail) format. This is required if using private key-based authentication instead of username and password.

  • Private key passphrase: Enter the passphrase that unlocks your private key. This field should only be filled if the private key is encrypted with a passphrase. If the private key does not have a passphrase, leave this field empty.

  • Test query: Enter the query used to test connectivity to the database. For additional access control testing, this could be the same as the Schema query. However, since this query is called often, it should be optimized for the particular database.

    • The Test query can serve to verify that the account being used has the necessary permissions to access the database. This ensures secure and authorized connection to the database.

  • Test query, retry: Specify the number of times to retry the test query. The default is three times.

  • Test query, timeout: Specify the number of seconds before the test query times out and retries. The default is 60 seconds.

  • Schema query: Enter the query used to determine the schema. This query is used to optimize the detection of the schema. It should be a copy of the data query with a limit of 1.

  • Schema query, retry: Specify the number of times to retry the schema query. The default is three times.

  • Schema query, timeout: Specify the number of seconds before the schema query times out and retries. The default is 60 seconds.

  • Data query: Enter the query to search for database records. To optimize data retrieval, use ? as a last-modified timestamp.

    • For example: You have a table called user_data and set the last_seen column as the Last modified field. Your data query might be:

      SELECT * FROM user_data WHERE last_seen > ?

    • This instructs the SQL Connector to retrieve only records where the last_seen date is later than the last successful synchronization timestamp.

  • Data query, retry: Specify the number of times to retry the data query. The default is three times.

  • Data query, timeout: Specify the number of seconds before the data query times out and retries. The default is 60 seconds.

  • Unique id fields: A comma-separated list of fields that uniquely identify a row returned by the data query.

  • Last modified field: (Optional) Specify the field or column used to determine changes since the last sync. See the "Data query" example for details. The field must resolve to a UTC timestamp (e.g., 2024-01-23T12:45:00Z).

  • Boolean fields: (Optional) A comma-separated list of fields that are of type Boolean, but not natively supported.

Supported SQL databases​

The SQL Connector supports a variety of databases, each requiring a specific Java Database Connectivity (JDBC) driver. Below are the supported databases along with example connection URLs for each:

DatabaseJDBC DriverExample Connection URL
DB2 (IBM DB2)DB2 JDBC Driver (e.g., db2jcc.jar)jdbc:db2://<host>:<port>/<database>
JTDS (for Microsoft SQL Server)jTDS JDBC Driverjdbc:jtds:sqlserver://<host>:<port>/<database>
MariaDBMariaDB JDBC Driver (Connector/J)jdbc:mariadb://<host>:<port>/<database>
MSSQL (Microsoft SQL Server)Microsoft JDBC Driver for SQL Server (e.g., mssql-jdbc.jar)jdbc:sqlserver://<host>:<port>;databaseName=<database>
MySQLMySQL JDBC Driver (Connector/J)jdbc:mysql://<host>:<port>/<database>
PostgreSQLPostgreSQL JDBC Driver (PgJDBC)jdbc:postgresql://<host>:<port>/<database>
SnowflakeSnowflake JDBC Driverjdbc:snowflake://<account>.snowflakecomputing.com/?db=<database>&schema=<schema>&warehouse=<warehouse>

Ensure that the appropriate JDBC driver for your database is installed and accessible to the Brinqa SQL Connector. For each database, replace host, port, and database in the connection URLs with the respective values for your environment. The port is typically a default value specific to each database type, such as 5432 for PostgreSQL or 3306 for MySQL, but it may differ depending on your setup.

In the case of Snowflake, replace account, schema, and warehouse in the connection URL with the appropriate details from your Snowflake environment, as these values are specific to your Snowflake configuration.

Types of data to retrieve​

The SQL Connector retrieves database records and maps to the data model you specify in the Target type field.

info

The SQL Connector does not currently support operation options for the types of data it retrieves.

For detailed steps on how to view the data retrieved from the SQL Connector in the Brinqa Platform, see How to view your data.

APIs​

The SQL Connector does not use any API endpoints.

Changelog​

The SQL connector has undergone the following changes:

3.1.10

  • Code clean up and general maintenance.

3.1.9

  • Fixed an issue where the connector sync was failing when using Active Directory credentials. As a result, support has been added for the following MSSQL Active Directory authentication methods:

    • ActiveDirectoryPassword
    • ActiveDirectoryIntegrated
    • ActiveDirectoryInteractive
    • ActiveDirectoryServicePrincipal
    • ActiveDirectoryServicePrincipalCertificate

3.1.8

  • Fixed a memory leak during sync.

3.1.7

  • Added support for Boolean since MySQL/MariaDB lacks native support for them.

3.1.5

  • Used the 'Instant' Java class for better date and time handling.

  • Improved messages for specific JDK versions.

  • Introduced support for SQL array data types.

  • Simplified the fields specified in integration configuration.

  • Added support for the Snowflake database.

3.1.4

Last updated on