SQL Connector (Deprecated)

info

This version of the SQL Connector is deprecated. While it still receives essential vulnerability updates, it is not actively developed for new features or enhancements. Brinqa encourages you to transition to the updated SQL Connector for the full range of features.

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 a 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. Required only if the private key is encrypted with a passphrase and private key-based authentication is used.

• Test query: Enter the query to test the database connection. This query must be read-only and returns one record. The default query is SELECT 1.

• Search query: Enter the query to search for database records. This query is primarily used to retrieve the entire database schema, so it needs to fetch only one record.

• Sync query: Enter the query to sync database records. This query should contain a WHERE clause with a timestamp field to sync only the modified records.

• Latest sync token query: (Optional) Enter a query to obtain the most recent synchronization token. In the majority of cases, this query isn't necessary.

• Insert query: (Optional) Enter a query to insert database records. You can use this option to push data to your database.

  For example, INSERT INTO TEST_DATA (col1, col2) VALUES (:attribute1, :attribute2);, where col1, col2 are column names in your table, and attribute1, attribute2 are attribute names in the data model specified in the Target type field. The colon (:) before the attribute name signifies that the value of the attribute is used when executing the query.

• Update query: (Optional) Enter a query to update database records. You can use this option to push data to your database.

  For example, UPDATE TEST_DATA SET col1 = :attribute1 WHERE col2 = :attribute2;, where col1, col2 are column names in your table, and attribute1, attribute2 are attribute names in the data model specified in the Target type field. The colon (:) before the attribute name signifies that the value of the attribute is used when executing the query.

• Sync timestamp field: (Optional) Specify the field or column that the connector should use to determine changes since the last sync. If this field is not defined, the connector uses the search query.

• Target type: Specify the data model in the Brinqa Platform that you want to map your data to. The default selection is Record.

• Unique field: (Optional) Specify the column that contains the unique identifier for your records. If one does not exist, it'll be generated automatically.

• Multi-value fields: (Optional) Enter a comma-separated list of columns containing multiple values. The format is col=delimiter where col is the name of the column and delimiter is the delimiter used to separate the values.

  For example, if you have a column named Ratings and it contains "High, Medium, Low" as values; then another column named Compliance and it contains "FedRAMP; HIPPA; PCI" as values, you should enter Ratings=,,Compliance=; in this field.

• Identifier fields: (Optional) Using a comma-separated list, specify the fields that are used as identifiers. To ensure there are no duplicates, it is crucial to declare the fields in the correct order.

• Text qualifier: Specify the qualifier that determines the start and end of a field. The default qualifier is the double quote (").

• Field delimiter: Specify the delimiter for the fields. The default delimiter is comma (,).

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

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

• Search query, timeout: Specify the number of seconds before the search query times out and retries. The default is 600 seconds.

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

• Sync query, timeout: Specify the number of seconds before the sync query times out and retries. The default is 600 seconds.

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

• Removes any binary type columns from wildcard queries: (Optional) If you use a wildcard character in your query to return all the columns, e.g.: SELECT * FROM Users, enable this option to exclude columns that contain binary data, which are usually image or PDF files.

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:

Database	JDBC Driver	Example Connection URL
DB2 (IBM DB2)	DB2 JDBC Driver (e.g., db2jcc.jar)	jdbc:db2://<host>:<port>/<database>
JTDS (for Microsoft SQL Server)	jTDS JDBC Driver	jdbc:jtds:sqlserver://<host>:<port>/<database>
MariaDB	MariaDB 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>
MySQL	MySQL JDBC Driver (Connector/J)	jdbc:mysql://<host>:<port>/<database>
PostgreSQL	PostgreSQL JDBC Driver (PgJDBC)	jdbc:postgresql://<host>:<port>/<database>

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 be different depending on your environment.

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.