Microsoft OneDrive Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Microsoft OneDrive Version Support

Microsoft OneDrive and OneDrive for Business accounts are supported via the Microsoft Graph API v1.0.

Establish a Connection

Connect to Microsoft OneDrive

Authenticate using Microsoft Entra service principal

Microsoft Entra service principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Microsoft Entra service principal authentication for an authentication guide.

Create an Entra ID (Microsoft Entra ID) Application

Create an Entra ID (Microsoft Entra ID) Application

Microsoft OneDrive supports OAuth-based authentication using Microsoft Entra ID. If you will connect via a web application and want to authenticate via Entra ID, you must first register a custom OAuth application in the Entra Admin Center, as described below.

If you will connect via a desktop application or headless machine, you can authenticate using Microsoft OneDrive's built-in embedded application credentials, which use branding. However, custom OAuth applications are also compatible with desktop and headless authentication flows, and may be preferable for production deployments or environments requiring strict policy control.

Register the Application

To register an OAuth application in Microsoft Entra ID, follow these steps:

1. Go to https://portal.azure.com.

2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.

3. Click New registration.

4. Enter a name for the application.

5. Specify the types of accounts this application should support:

   • For private-use applications, select Accounts in this organization directory only.
   • For distributed applications, select one of the multi-tenant options.

   Note

   If you select Accounts in this organizational directory only, when you connect with Microsoft OneDrive connector, you must set AzureTenant to the tenant's ID (either GUID or verified domain). Otherwise, authentication will fail.

6. Set the redirect URI to http://localhost:33333 (default), or use another URI appropriate for your deployment.

   When using a custom redirect URI set a CallbackURL connection property; in those cases, set it to match this URI exactly.

7. Click Register. The application management screen opens. Record these values for later use:

   • Application (client) ID is used for OAuthClientId
   • Directory (tenant) ID is used for AzureTenant

8. Go to Certificates & Secrets. Click New Client Secret, set the desired expiration, and save the generated value. This value will only be shown once — record it to use with OAuthClientSecret.

9. Select the Microsoft Graph API and then add the delegated permissions Files.ReadWrite.All or Files.Read.All. Hit the 'Grant admin consent' button afterwards for the new permissions to take effect.

10. To confirm, click Add permissions.

Create a Service Principal App in Entra ID (Microsoft Entra ID)

Create a Service Principal App in Entra ID (Microsoft Entra ID)

Microsoft OneDrive supports Service Principal-based authentication, which is role-based. This means that the Service Principal's permissions are determined by the roles assigned to it. The roles specify what resources the Service Principal can access and which operations it can perform.

If you want to use a Service Principal to authenticate to Microsoft OneDrive, you must create a custom application in Microsoft Entra ID.

To enable Service Principal authentication:

• Confirm that you have permission to register applications and assign roles in your tenant.
• Register a new application and configure credentials and permissions in the Entra Admin Center.

Register the Application

1. Go to https://portal.azure.com.
2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup. Since this custom application is for Service Principal use, choose Any Microsoft Entra ID tenant – Multitenant.
6. Click Register. The application management screen opens. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant
7. Navigate to Certificates & Secrets and define the application authentication type. Two types of authentication are available: certificate (recommended) or client secret
   • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft OneDrive displays the key value. This value is displayed only once, so be sure to record it for future use. Use this value for the OAuthClientSecret
8. Navigate to Authentication and select the Access tokens option.
9. Select the Microsoft Graph API and then add the delegated permissions Files.ReadWrite.All or Files.Read.All. Hit the 'Grant admin consent' button afterwards for the new permissions to take effect.
10. Save your changes.
11. If you specified permissions that require admin consent, you can grant them from the current tenant on the API Permissions page.

Grant Admin Consent

Some custom applications require administrative permissions to operate within a Microsoft Entra ID tenant. This is especially true for applications that use Application permissions, which allow the app to run without a signed-in user. Admin consent can be granted when creating a new application, by adding relevant permissions marked as "Admin Consent Required".

Admin consent is also required to use Client Credentials in the authentication flow.

These permissions must be granted by an admin. To grant admin consent:

1. Log in to https://portal.azure.com with an administrator account.
2. Navigate to Microsoft Entra ID > App registrations and select your registered application.
3. Navigate to API permissions.
4. Review the permissions listed under Application permissions. Ensure the necessary API scopes are included for your use case.
5. Click Grant admin consent to approve the requested permissions.

This gives your application permissions on the tenant under which it was created.

Fine-Tuning Data Access

Fine-tune Data Access

• DirectoryRetrievalDepth: How many folders deep you want to get results for Folders, Files, SharedResources, and Permissions tables.
• DriveId: The ID of the Drive that you want to use when getting data for tables Files, Folders, SharedResources, and Permissions.
• ListGroupDrives: Indicates if you would like to list your group drives or not when querying the Drives table. Useful if you're using a OneDrive for Business account.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Microsoft OneDrive connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft OneDrive and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Microsoft OneDrive connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Files WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

The Microsoft OneDrive connector models Microsoft OneDrive objects as an easy-to-use SQL database, using views and stored procedures. These are defined in schema files, which are simple, easy-to-read text files that define the structure and organization of data. Because the table definitions are dynamically retrieved, any changes to the remote data are immediately reflected in your queries.

Views

The Views section, which lists read-only SQL tables, contains samples from an example OneDrive site. Your data model is obtained dynamically based on your user credentials and OneDrive site.

The sample site includes the following views:

Stored Procedures

Stored Procedures are SQL scripts that extend beyond standard CRUD operations. They can be used to search, update, and modify information in OneDrive.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Microsoft OneDrive Connector Views

Drives

Displays all OneDrive drives accessible to the current user, including personal and shared drives.

Table Specific Information

Due to API limitations, all operators on this view are executed client-side.

Columns

Files

Provides detailed information about individual files stored in OneDrive drives, including name, size, and metadata.

Table Specific Information

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, audio_album, audio_title FROM Files WHERE ID IN ('B1E6BJ084A9133KB1!101', 'APF9D2K6WFA524Y')

Include ParentReference_DriveId and ParentReference_Id in the where clause to get the result for a particular folder, e.g.:

SELECT * FROM Files WHERE  ParentReference_DriveId = 'b!3Yqza-5FJ0usfhyH-2QafkQpf0OC0jlNh8LAETWPK6dNROPDScevTKbV-drnCFHV' And ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'

File listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the files in the 'Drive' no matter the depth they're located in.

While files may be listed in this view, the contents of the files may be worked with by using stored procedures such as:

• CopyResource - Copy a file/folder from one directory in your drive to another.
• DeleteResource - Delete a file/folder from your drive.
• DownloadFile - Download an existing file from your drive.
• MoveResource - Move a file/folder from one directory in your drive to another.
• UploadFile - Upload a new file or update content to an existing file.

Columns

FileVersions

Tracks the version history of each file in OneDrive, allowing review or rollback of previous file states.

Table Specific Information

SELECT

The connector will use the Microsoft OneDrive API to process WHERE clause conditions built with the following column and operator. The filter labeled 'createdBy_user_id' is required to execute the query successfully.

• FileId supports the '='.
• DriveId supports the '='.
• UserId supports the '='.
• createdBy_user_id supports the '='.

For example:

SELECT * FROM FileVersions WHERE FileId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY' AND DriveId = 'b!GrEGWax2VE-ssnJNr1E8llqoLptvK-BGjFRpceFzFg5Nwp9Bw2xbR62J0NAouP6z' AND createdBy_user_id = '472df319-2978-49d4-9445-9e7019def16f'

SELECT * FROM FileVersions WHERE FileId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY' AND createdBy_user_id = '472df319-2978-49d4-9445-9e7019def16f'

SELECT * FROM FileVersions WHERE createdBy_user_id = '472df319-2978-49d4-9445-9e7019def16f'

Columns

Folders

Lists all folders within a OneDrive drive, useful for navigation and hierarchical organization analysis.

Table Specific Information

Note: See CreateFolder to create a folder.

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, Size, Description FROM Folders WHERE ID IN ('B1E6BJ084A9133KB1!122', 'APF9D2K6WFAAPX9')

Folder listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the folders in the 'Drive' no matter the depth they're located in.

While folders may be listed in this view, the contents of the folders may be worked with by using stored procedures such as:

• CopyResource - Copy a file/folder from one directory in your drive to another.
• DeleteResource - Delete a file/folder from your drive.
• MoveResource - Move a file/folder from one directory in your drive to another.

Columns

GroupMembers

Displays a list of users assigned to each group, helping manage team or access group memberships.

Columns

Groups

Shows all security and distribution groups within the connected Microsoft 365 tenant, enabling administrative oversight.

Columns

Permissions

Details sharing and access permissions for files and folders within a OneDrive drive, identifying owners and collaborators.

Table Specific Information

Due to API limitations, all operators on this view are executed client-side.

Columns

SharedResources

Identifies all files and folders that have been shared from OneDrive, along with recipients and access levels.

Table Specific Information

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, Size, Shared_Scope FROM SharedResources WHERE ID IN ('B1E6BJ084A9133KB1!122', 'APF9D2K6WFAAPX9')

SharedResources listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the shared resources in the 'Drive' no matter the depth they're located in.

Columns

SharedWithMeResources

Lists files and folders that have been shared with the signed-in user by others, including metadata such as the owner, sharing permissions, and access details.

Table Specific Information

Due to API limitations, all operators on this view are executed client-side.

SharedWithMeResources listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the shared resources in the 'Drive' no matter the depth they're located in.

Columns

Users

Lists users with OneDrive accounts in the organization, including user identifiers and profile details.

Users Information

Projection is done server-side. Basic select query is supported e.g.:

SELECT * FROM Users

Using this view an admin user can list all other users details.

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with Microsoft OneDrive.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft OneDrive, along with an indication of whether the procedure succeeded or failed.

Microsoft OneDrive Connector Stored Procedures

CopyResource

Copies a specified file or folder from one location in OneDrive to another, preserving content and structure.

Input

Result Set Columns

CreateFolder

Creates a new folder at a specified path within OneDrive to organize content.

Input

Result Set Columns

CreateSubscription

Registers a webhook subscription to receive notifications for changes to OneDrive content (for example, file updates).

Input

Result Set Columns

DeleteResource

Permanently deletes a specified file or folder from OneDrive, removing it from all views and history.

Input

Result Set Columns

DeleteSubscription

Removes an existing webhook subscription to stop receiving change notifications from OneDrive.

Input

Result Set Columns

DownloadFile

Retrieves and downloads a specified file from OneDrive storage to the local or application context.

Stored Procedure Specific Information

Microsoft OneDrive allows only a small subset of columns to be used in the Exec query. Either ResourcePath or ResourceId is required to download the file. Typically, these columns can only be used with the = comparison.

If the file is present in the root folder of a OneDrive account, mention the file name directly for the value of ResourcePath. For example:

EXECUTE DownloadFile ResourcePath = 'Book.xlsx', LocalFile = 'D:\Desktop\Book.xlsx'

If the file is present inside a child folder of a OneDrive account, you need to specify the path of the file, starting from the child folder name, for the value of ResourcePath. For example:

EXECUTE DownloadFile ResourcePath = 'Notebooks/test.png', LocalFile = 'D:\Desktop\test.png'

You can also download the file by providing the file's ResourceId. For example:

EXECUTE DownloadFile ResourceId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY', LocalFile = 'D:\Desktop\test.png'

Input

Result Set Columns

FetchAdditionalUserFields

Retrieves tiered metadata fields (T1, T2, T3) for a specific user, typically for profiling or auditing.

Input

Result Set Columns

GetAdminConsentURL

Generates a URL that an admin must visit to grant tenant-wide OAuth permissions to your app; used when setting up enterprise integrations.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from OneDrive.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the auth token from this URL.

Input

Result Set Columns

ModifySubscription

Creates or updates a webhook subscription to listen for changes in OneDrive files, folders, or other resources.

Input

Result Set Columns

MoveResource

Relocates a file or folder within the OneDrive directory structure, maintaining metadata and permissions.

Stored Procedure Specific Information

Microsoft OneDrive allows only a small subset of columns to be used in the Exec query. DestinationID and either of ResourcePath or ResourceId is required to move the resource. These columns can typically be used with only = comparison.

If file is present in the root folder of a OneDrive account, then mention the file name directly for the value of ResourcePath. For example:

EXECUTE MoveResource ResourcePath = 'Book.xlsx', DestinationID = '01JSGDQ3MFCITWE4PQKFAY6KH5LHJKPYU5'

If file is present inside a child folder of a OneDrive account, then you need to specify the path of the file starting from child folder name for the value of ResourcePath. For example:

EXECUTE MoveResource ResourcePath = 'Notebooks/test.png', DestinationID = '01JSGDQ3MFCITWE4PQKFAY6KH5LHJKPYU5'

You can also move the resource by providing the ResourceId and DestinationID as well. For example:

EXECUTE MoveResource ResourceId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY', DestinationID = '01JSGDQ3MFCITWE4PQKFAY6KH5LHJKPYU5'

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Office 365 services.

Input

Result Set Columns

RenameResource

Changes the name of a specified file or folder in OneDrive without affecting its contents or location.

Input

Result Set Columns

UploadFile

Uploads a new file or overwrites an existing one in OneDrive, supporting binary content updates.

Input

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft OneDrive:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Files table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Files'

Columns

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the DownloadResource stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'DownloadResource' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'DownloadResource' AND IncludeResultColumns='True'

Columns

Pseudo-Columns

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Files table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Files'

Columns

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns