SQL
The SQL Query Collector is a template collector that can extract data from foreign databases using configurable SQL statements. The collector uses an OLEDB Provider, which is part of the Omada Identity Cloud infrastructure. To use it in the on-premises deployment, please refer to the section: (https://documentation.omadaidentity.com/connectivity/sql/#prerequisites-on-prem).
In addition to traditional on-premises hosted databases, the SQL Query Collector can also connect to cloud-hosted databases, such as Azure SQL Managed Instance, Amazon RDS for SQL, and other.
Supported objects and operations
Identity data
| System objects | Omada Identity Data Model | Operations |
|---|---|---|
| Identities | - | Read |
| Contexts | - | Read |
| Identity Owner | - | Read |
| Context Owner | - | Read |
| Context Assignment | - | Read |
Access rights
| System objects | Omada Identity Data Model | Operations |
|---|---|---|
| Account | - | Read |
| Resource | - | Read |
| Resource Owner | - | Read |
| Resource Assignment | - | Read |
| Resource Parent/Child | - | Read |
Minimum required permissions
The service account used for running the application pool in Omada Identity must have access to the target SQL database. No additional configuration is required.
Implementation notes
In the advanced mode, you can use a different OLEDB provider (through changes in the connection string). For MySQL, MariaDB and PostgreSQL only default providers are supported.
For Oracle databases, as you cannot introduce the TNSNAMES.ORA file that defines databases addresses for establishing connections, you need to configure a connection string to work without TNSNAMES.ORA. The following example can be used:
Provider=OraOLEDB.Oracle;
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));
User Id=myUsername;Password=myPassword;
Network requirements
N/A
Prerequisites on-prem
Perform the following steps depending on the technology used:
IBM database platforms
The IBM database platform requires both the IBM Data Server Runtime Client software and either an IBM or Microsoft driver. See the IBM software package and Microsoft db2oledb driver for downloads.
Oracle database platforms
The Oracle database platform requires the ODAC Xcopy (with the OLE DB component) package installed. Go to Oracle Downloads and download the package.
MySQL database platforms
The MySQL database platform requires MySQL Connector/NET version 8.4.0. package. See the MySQL Product Downloads.
Delta support
The delta requires the table or view to contain the following columns:
- Change Date (datetime)
- Action (Create, Update, Delete)