Oracle SQL Microsoft Graph connector
The Oracle SQL Graph connector allows your organization to discover and index data from an on-premises Oracle database. The connector indexes specified content into Microsoft Search. To keep the index up to date with source data, it supports periodic full and incremental crawls. With the Oracle SQL connector, you can also restrict access to search results for certain users.
Note
Read the Set up Microsoft Graph connectors in the Microsoft 365 admin center article to understand the general Microsoft Graph connectors setup instructions.
This article is for anyone who configures, runs, and monitors an Oracle SQL Graph connector. It supplements the general setup process, and shows instructions that apply only for the Oracle SQL Graph connector. This article also includes information about Troubleshooting and Limitations.
Before you get started
Install the connector agent
In order to access your on-premises third-party data, you must install and configure the Graph connector agent. See Install the Graph connector agent to learn more.
Step 1: Add a connector in the Microsoft 365 admin center
Follow the general setup instructions.
Step 2: Name the connection
Follow the general setup instructions.
Step 3: Configure the connection settings
To connect your Oracle SQL connector to a data source, you must configure the database server you want crawled and the on-premises Graph connector agent. You can then connect to the database with the required authentication method.
For Oracle SQL connector, you need to specify the Hostname, Port and Service (database) name along with the preferred authentication method, username, and password.
If Service Name is not available and you connect using SID, the Service Name can be derived using one of the following commands (to be executed as sys admin) -
- select SERVICE_NAME from gv$session where sid in (select sid from v$MYSTAT);
- select sys_context('userenv','service_name') from dual;
Note
Your database must run Oracle database version 11g or later for the connector to be able to connect. The connector supports Oracle database hosted on Windows, Linux and Azure VM platforms.
To search your database content, you must specify SQL queries when you configure the connector. These SQL queries need to name all the database columns that you want to index (that is, source properties), including any SQL joins that need to be performed to get all the columns. To restrict access to search results, you must specify Access Control Lists (ACLs) within SQL queries when you configure the connector.
Step 3a: Full crawl (Required)
In this step, you configure the SQL query that runs a full crawl of the database. The full crawl selects all the columns or properties where you want to select the options Query, Search, or Retrieve. You can also specify ACL columns to restrict access of search results to specific users or groups.
Tip
To get all the columns that you need, you can join multiple tables.
Select data columns (Required) and ACL columns (Optional)
The example demonstrates selection of five data columns that hold the data for the search: OrderId, OrderTitle, OrderDesc, CreatedDateTime, and IsDeleted. To set view permissions for each row of data, you can optionally select these ACL columns: AllowedUsers, AllowedGroups, DeniedUsers, and DeniedGroups. For all these data columns you can select the options to Query, Search, or Retrieve.
Select data columns as shown in this example query:
SELECT OrderId, OrderTitle, OrderDesc, AllowedUsers, AllowedGroups, DeniedUsers, DeniedGroups, CreatedDateTime, IsDeleted
To manage access to the search results, you can specify one or more ACL columns in the query. The SQL connector allows you to control access at per record level. You can choose to have the same access control for all records in a table. If the ACL information is stored in a separate table, you might have to do a join with those tables in your query.
The use of each of the ACL columns in the above query is described below. The following list explains the four access control mechanisms.
- AllowedUsers: This option specifies the list of user IDs who will be able to access the search results. In the following example, list of users: john@contoso.com, keith@contoso.com, and lisa@contoso.com would only have access to a record with OrderId = 12.
- AllowedGroups: This option specifies the group of users who will be able to access the search results. In the following example, group sales-team@contoso.com would only have access to record with OrderId = 12.
- DeniedUsers: This option specifies the list of users who do not have access to the search results. In the following example, users john@contoso.com and keith@contoso.com do not have access to record with OrderId = 13, whereas everyone else has access to this record.
- DeniedGroups: This option specifies the group of users who do not have access to the search results. In the following example, groups engg-team@contoso.com and pm-team@contoso.com do not have access to record with OrderId = 15, whereas everyone else has access to this record.
Supported data types
The below table summarizes the data types that are supported by the Oracle SQL connector. The table also summarizes the indexing data type for the supported SQL data type. To learn more about Microsoft Graph connectors supported data types for indexing, refer documentation on property resource types.
Category | Source data type | Indexing data type |
---|---|---|
Number datatype | NUMBER(p,0) | int64 (for p <= 18) double (for p > 18) |
Floating-point number datatype | NUMBER(p,s) FLOAT(p) |
double |
Date datatype | DATE TIMESTAMP TIMESTAMP(n) |
datetime |
Character datatype | CHAR(n) VARCHAR VARCHAR2 LONG CLOB NCLOB |
string |
Unicode character datatype | NCHAR NVARCHAR |
string |
RowID datatype | ROWID UROWID |
string |
For any other data type currently not directly supported, the column needs to be explicitly cast to a supported data type.
Watermark (Required)
To prevent overloading the database, the connector batches and resumes full-crawl queries with a full-crawl watermark column. By using the value of the watermark column, each subsequent batch is fetched, and querying is resumed from the last checkpoint. Essentially this is a mechanism to control data refresh for full crawls.
Create query snippets for watermarks as shown in these examples:
WHERE (CreatedDateTime > @watermark)
. Cite the watermark column name with the reserved keyword@watermark
. You can only sort the watermark column in ascending order.ORDER BY CreatedDateTime ASC
. Sort on the watermark column in ascending order.
In the configuration shown in the following image, CreatedDateTime
is the selected watermark column. To fetch the first batch of rows, specify the data type of the watermark column. In this case, the data type is DateTime
.
The first query fetches the first N number of rows by using: "CreatedDateTime > January 1, 1753 00:00:00" (min value of DateTime data type). After the first batch is fetched, the highest value of CreatedDateTime
returned in the batch is saved as the checkpoint if the rows are sorted in ascending order. An example is March 1, 2019 03:00:00. Then the next batch of N rows is fetched by using "CreatedDateTime > March 1, 2019 03:00:00" in the query.
Skipping soft-deleted rows (Optional)
To exclude soft-deleted rows in your database from being indexed, specify the soft-delete column name and value that indicates the row is deleted.
Full crawl: Manage search permissions
Select Manage permissions to choose the various access control (ACL) columns that specify the access control mechanism. Select the column name you specified in the full crawl SQL query.
Each of the ACL columns is expected to be a multi-valued column. These multiple ID values can be separated by separators such as semicolon (;), comma (,), and so on. You need to specify this separator in the value separator field.
The following ID types are supported for using as ACLs:
- User Principal Name (UPN): A User Principal Name (UPN) is the name of a system user in an email address format. A UPN (for example: john.doe@domain.com) consists of the username (logon name), separator (the @ symbol), and domain name (UPN suffix).
- Microsoft Entra ID: In Microsoft Entra ID, every user or group has an object ID that looks something like 'e0d3ad3d-0000-1111-2222-3c5f5c52ab9b'
- Active Directory (AD) Security ID: In an on-premises AD setup, every user and group have an immutable, unique security identifier that looks something like 'S-1-5-21-3878594291-2115959936-132693609-65242.'
Step 3b: Incremental crawl (Optional)
In this optional step, provide a SQL query to run an incremental crawl of the database. With this query, the SQL connector determines any changes to the data since the last incremental crawl. As in the full crawl, select between the options Query, Search, or Retrieve. Specify the same set of ACL columns that you specified in the full crawl query.
The components in the following image resemble the full crawl components with one exception. In this case, "ModifiedDateTime" is the selected watermark column. Review the full crawl steps to learn how to write your incremental crawl query and see the following image as an example.
Step 4: Assign property labels
Follow the general setup instructions.
Step 5: Manage schema
Follow the general setup instructions.
Step 6: Manage search permissions
You can choose to use the ACLs specified in the full crawl screen or you can override them to make your content visible to everyone.
Step 7: Choose refresh settings
The Oracle SQL connector supports refresh schedules for both full and incremental crawls. We recommend that you set both.
A full crawl schedule finds deleted rows that were previously synced to the Microsoft Search index and any rows that moved out of the sync filter. When you first connect to the database, a full crawl runs to sync all the rows retrieved from the full crawl query. To sync new rows and make updates, you need to schedule incremental crawls.
Step 8: Review connection
Follow the general setup instructions.
Limitations
The Oracle SQL connector has these limitations in the preview release:
- The on-premises database must run Oracle Database version 11g or later.
- ACLs are only supported by using a User Principal Name (UPN), Microsoft Entra ID, or Active Directory Security.
- Indexing rich content inside database columns is not supported. Examples of such content are HTML, JSON, XML, blobs, and document parsings that exist as links inside the database columns.
Troubleshooting
After publishing your connection, you can review the status under the Data Sources tab in the admin center. To learn how to make updates and deletions, see Manage your connector. You can find troubleshooting steps for commonly seen issues here.
If you have any other issues or want to provide feedback, write to us aka.ms/TalkToGraphConnectors.