Microsoft Office 365 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

Microsoft Office 365 Version Support

All hosted versions of Microsoft Office 365 are supported via the microsoft Graph API v1.0. Includes information accessible from 365 editions of Exchange/Outlook, Teams, Tasks, and OneDrive.

Establish a Connection

Connect to Microsoft Office 365

Authenticate using Azure Service Principal

Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Azure Service Principal Authentication for an authentication guide.

Use Azure Service Principal

Azure Service Principal

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

To use Azure Service Principal authentication, you must:

• Set up the ability to assign a role to the authentication application. To do this, create a custom OAuth AD application, as described in

  Creating a Custom OAuth Application

  Microsoft Office 365 supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

  This topic describes how to:

  • create and register custom OAuth application for Azure AD or Azure Service Principal
  • provide Admin Consent to a custom OAuth application
  • create a custom OAuth application for use with client credentials

  Azure AD

  In portal.azure.com:

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
  3. Click New registration.
  4. Enter a name for the application.
  5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
     • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft Office 365 connector. Otherwise, the authentication attempt fails with an error.
     • If your application is for private use only, specify Accounts in this organization directory only.
     • If you want to distribute your application, choose one of the multi-tenant options.
  6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
  7. Click Register to register the new application. An application management screen displays.
     Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
  8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
     • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
     • 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 Office 365 displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
  9. Select API Permissions > Add > Delegated permissions.
  10. Select the Microsoft Graph API and then select the permissions your app will seek.
  11. Save your changes.
  12. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

  Azure Service Principal

  To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

  control to access resources in your subscription.

  In portal.azure.com:

  1. Create a custom OAuth AD application, as described above.
  2. Use the search bar to search for the Subscriptions service.
  3. Open the Subscriptions page.
  4. Select the subscription to which to assign the application.
  5. Open the Access control (IAM).
  6. Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.
  7. Assign your custom Azure AD application the role of Owner.

  Admin Consent

  Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

  To grant admin consent:

  1. Have an admin log in to portal.azure.com.
  2. Navigate to App Registrations and find the custom OAuth application you created.
  3. Under API Permissions, click Grant Consent.

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

  Consent for Client Credentials

  OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

  All tasks accepted by the application are executed outside of the context of a default user.

• Register an application with an Azure AD tenant, to create a new service principal that can be used with the role-based access control, to access resources in your subscription.

Do the following:

1. Create a custom Azure AD application, as described in

   Creating a Custom OAuth Application

   Microsoft Office 365 supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

   This topic describes how to:

   • create and register custom OAuth application for Azure AD or Azure Service Principal
   • provide Admin Consent to a custom OAuth application
   • create a custom OAuth application for use with client credentials

   Azure AD

   In portal.azure.com:

   1. Log in to https://portal.azure.com.
   2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
   3. Click New registration.
   4. Enter a name for the application.
   5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
      • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft Office 365 connector. Otherwise, the authentication attempt fails with an error.
      • If your application is for private use only, specify Accounts in this organization directory only.
      • If you want to distribute your application, choose one of the multi-tenant options.
   6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
   7. Click Register to register the new application. An application management screen displays.
      Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
   8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
      • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
      • 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 Office 365 displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
   9. Select API Permissions > Add > Delegated permissions.
   10. Select the Microsoft Graph API and then select the permissions your app will seek.
   11. Save your changes.
   12. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

   Azure Service Principal

   To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

   control to access resources in your subscription.

   In portal.azure.com:

   1. Create a custom OAuth AD application, as described above.
   2. Use the search bar to search for the Subscriptions service.
   3. Open the Subscriptions page.
   4. Select the subscription to which to assign the application.
   5. Open the Access control (IAM).
   6. Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.
   7. Assign your custom Azure AD application the role of Owner.

   Admin Consent

   Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

   To grant admin consent:

   1. Have an admin log in to portal.azure.com.
   2. Navigate to App Registrations and find the custom OAuth application you created.
   3. Under API Permissions, click Grant Consent.

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

   Consent for Client Credentials

   OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

   All tasks accepted by the application are executed outside of the context of a default user.

1. Assign a role to the application:
   1. Use the search bar to search for the Subscriptions service.
   2. Open the Subscriptions page.
   3. Select the subscription to which to assign the application.
   4. Open the Access control (IAM).
   5. Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.
   6. Assign your custom Azure AD application the role of Owner.

Admin Consent

Admin consent occurs when the Admin for an Azure Active Directory tenant grants permissions to a custom application that explicitly requires an admin to consent to the use case.

When creating a new Azure AD application in the Azure Portal, you must specify which permissions the application requires. Some permissions may be marked as "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your application requires admin consent, there are two ways you can do this.

The easiest way to grant admin consent is to have an admin log into portal.azure.com and navigate to the application you have created in App Registrations. Under API Permissions, click Grant Consent. This gives your application permissions on the tenant under which it was created.

If your organization has multiple tenants or you must grant application permissions for other tenants outside your organization, use the GetAdminConsentURL stored procedure to generate the Admin Authorization URL. Unlike the GetOAuthAuthorizationURL stored procedure, no important information is returned from this endpoint. Rather, after the OAuth application is successfully authorized, it returns a Boolean indicating that permissions have been granted.

After the administrator has approved the OAuth Application, you can continue to authenticate.

Client Credentials

Client credentials refers to a flow in OAuth where there is no direct user authentication taking place. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context. This makes the authentication flow a bit different from the standard flow.

All permissions related to the client OAuth flow require admin consent. This means you cannot use the application embedded with the Microsoft Office 365 connector in the client OAuth flow. You must create your own OAuth application to use client credentials. See

Create a Custom OAuth Application

Microsoft Office 365 supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

This topic describes how to:

• create and register custom OAuth application for Azure AD or Azure Service Principal
• provide Admin Consent to a custom OAuth application
• create a custom OAuth application for use with client credentials

Azure AD

In portal.azure.com:

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
   • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft Office 365 connector. Otherwise, the authentication attempt fails with an error.
   • If your application is for private use only, specify Accounts in this organization directory only.
   • If you want to distribute your application, choose one of the multi-tenant options.
6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
7. Click Register to register the new application. An application management screen displays.
   Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
   • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
   • 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 Office 365 displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
9. Select API Permissions > Add > Delegated permissions.
10. Select the Microsoft Graph API and then select the permissions your app will seek.
11. Save your changes.
12. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

Azure Service Principal

To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

control to access resources in your subscription.

In portal.azure.com:

1. Create a custom OAuth AD application, as described above.
2. Use the search bar to search for the Subscriptions service.
3. Open the Subscriptions page.
4. Select the subscription to which to assign the application.
5. Open the Access control (IAM).
6. Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.
7. Assign your custom Azure AD application the role of Owner.

Admin Consent

Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

To grant admin consent:

1. Have an admin log in to portal.azure.com.
2. Navigate to App Registrations and find the custom OAuth application you created.
3. Under API Permissions, click Grant Consent.

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

Consent for Client Credentials

OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

All tasks accepted by the application are executed outside of the context of a default user.

use them in a client OAuth flow. You must always create a custom OAuth application to use client credentials.

In portal.azure.com:

1. Create a custom OAuth application, as described above.
2. Navigate to App Registrations.
3. Find the application you just created, and open API Permissions.
4. Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
5. Under Application Permissions, select the permissions you require for your integration.

for more information.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under "Application Permissions".

Select the permissions you require for your integration. After you do this, set the following connection properties:

• AuthScheme: AzureServicePrincipal.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you wish to connect to.
• OAuthGrantType: CLIENT.
• OAuthClientId: The client ID in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections will take place and be handled internally.

Create a Custom OAuth Application

Create a Custom OAuth Application

Microsoft Office 365 supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

This topic describes how to:

• create and register custom OAuth application for Azure AD or Azure Service Principal
• provide Admin Consent to a custom OAuth application
• create a custom OAuth application for use with client credentials

Azure AD

In portal.azure.com:

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
   • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft Office 365 connector. Otherwise, the authentication attempt fails with an error.
   • If your application is for private use only, specify Accounts in this organization directory only.
   • If you want to distribute your application, choose one of the multi-tenant options.
6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
7. Click Register to register the new application. An application management screen displays.
   Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
   • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
   • 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 Office 365 displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
9. Select API Permissions > Add > Delegated permissions.
10. Select the Microsoft Graph API and then select the permissions your app will seek.
11. Save your changes.
12. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

Azure Service Principal

To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

control to access resources in your subscription.

In portal.azure.com:

1. Create a custom OAuth AD application, as described above.
2. Use the search bar to search for the Subscriptions service.
3. Open the Subscriptions page.
4. Select the subscription to which to assign the application.
5. Open the Access control (IAM).
6. Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.
7. Assign your custom Azure AD application the role of Owner.

Admin Consent

Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

To grant admin consent:

1. Have an admin log in to portal.azure.com.
2. Navigate to App Registrations and find the custom OAuth application you created.
3. Under API Permissions, click Grant Consent.

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

Consent for Client Credentials

OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

All tasks accepted by the application are executed outside of the context of a default user.

use them in a client OAuth flow. You must always create a custom OAuth application to use client credentials.

In portal.azure.com:

1. Create a custom OAuth application, as described above.
2. Navigate to App Registrations.
3. Find the application you just created, and open API Permissions.
4. Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
5. Under Application Permissions, select the permissions you require for your integration.

Administrative Tasks

The Microsoft Office 365 connector can be used to perform administrative tasks. This can be done by specifying the UserId column to execute CUD operations.

The UserId Column

Many tables expose a special UserId column. This is designed to be used by an administrator to modify records on another user's account. If you are not an administrator or do not desire this behavior, do not specify the UserId when performing an INSERT / UPDATE / DELETE operation. For instance, executing the following will insert a contact for another user:

INSERT INTO Contacts (displayName, CompanyName, UserId) VALUES ('Bill', 'Bob Co', '12345')

The above request will have the overall effect of attempting to add a contact under the resource at /users/12345/contacts. When UserId is not specified, the resources affected will instead be modified under /users/me/contacts. In general if you are not an administrator, you can only affect or view records under /users/me, so it is not recommended to set UserId when you are not an admin.

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 Office 365 connector.

User Defined Views

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

SSL Configuration

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

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 Office 365 and then processes the rest of the query in memory (client-side).

User Defined Views

The Microsoft Office 365 connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used 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 as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Events 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

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting 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 SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The connector models the Microsoft Office 365 API as relational tables. Any changes to the remote data are immediately reflected in your queries; the table definitions are dynamically retrieved. When you connect, the connector connects to Microsoft Office 365 and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables shows definitions from a sample Office 365 site. The actual data model

will be obtained dynamically based on your user credentials and Office 365 site.

Stored Procedures

Stored Procedures are function-like interfaces to Office365. They can be used to search, update, and modify information

in Office365.

Tables

The connector models the data in Microsoft Office 365 as a list of tables in a relational database that can be queried using standard SQL statements.

Microsoft Office 365 Connector Tables

Calendars

This table is dynamic and maps to the corresponding field in the API.

Table Specific Information

Select

You can query Calendars by specifying an ID or selecting all:

SELECT * FROM Calendars WHERE ID = 'your Calendar ID goes here'

Select a certain column from the entity and filter by that column:

SELECT ID FROM Calendars WHERE name LIKE 'Calendar%'

Insert

Specify a Name as a minimum in order to create a new Calendar:

INSERT INTO Calendars (Name) VALUES ('John')

INSERT INTO Calendars (Name, UserId) VALUES ('Test123', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe');

Columns

Contacts

The Office365 table Contacts.

Table Specific Information

Select

You can query Contacts by specifying an ID or selecting all:

SELECT * FROM Contacts WHERE ID = 'your Contact ID goes here'

Select a certain column from the entity and filter by that column:

SELECT GivenName FROM Contacts WHERE GivenName LIKE 'John%'

Insert

Specify a GivenName and a Surname as a minimum in order to create a new Contact:

INSERT INTO Contacts (GivenName, Surname) VALUES ('John', 'Smith')

INSERT INTO Contacts (GivenName, Surname, UserId) VALUES ('John', 'Smith', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe')

Columns

Conversations

The Office365 table Conversations.

Table Specific Information

Select

The GroupId is required to get group Conversations.

SELECT * FROM Conversations WHERE GroupId = 'your GroupId goes here'

You can also get group Conversations by using the GroupId and the Conversation Id.

SELECT * FROM Conversations WHERE ID = 'conversation ID here' AND GroupId = 'your GroupId goes here'

Insert

Specify GroupId, Topic, Content, and NewParticipants to create a new Conversation. NewParticipants is a complex type. Its format is as follows: 'name1, email1; name2, email2'.

INSERT INTO Conversations (GroupId, Topic, Content, NewParticipants) VALUES ('GroupId here', 'This is a test topic.', 'Hi, How Are you?', 'someone, someone@example.com')

Columns

Events

This field is dynamic and maps to the corresponding field in the API.

Table Specific Information

Select

The 'me' property is used by default to return events.

You can use the UserId in the WHERE clause to override this when searching for Events.

SELECT * FROM Events WHERE UserId = 'abc123' AND Subject LIKE '%test%'

The GroupId can be a calendar type ID or a group Id. For example:

SELECT * FROM Events WHERE GroupId = 'enter your group ID here'

Insert

To create a new event, start and end are required, including the timezone.

INSERT INTO Events (Subject, Body_Content, Start_DateTime, Start_TimeZone, End_DateTime, End_TimeZone) VALUES ('New Test Event', 'Event created using Office365Provider', '2016-01-01T10:00:00', 'UTC', '2016-01-01T11:00:00', 'UTC')

Note: By default this statement will create your event under the default calendar.

INSERT INTO Events (Subject, Body_Content, Start_DateTime, Start_TimeZone, End_DateTime, End_TimeZone, UserId) VALUES ('New Test Event', 'Event created using Office365Provider', '2016-01-01T10:00:00', 'UTC', '2016-01-01T11:00:00', 'UTC', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe')

Columns

Files

The Office365 table Files.

Table Specific Information

Select

Retrieve files by using the UserId or File ID (Id) for instance, or simply filter by a certain column:

SELECT * FROM Files WHERE UserId = 'MyUserId'

SELECT Name, LastModifiedDateTime FROM Files WHERE Name LIKE 'test%'

To work for Folder-level files, we need to specify the parentReference_path in the query.

SELECT * FROM files WHERE parentReference_path = '/drives/b!3LIvU2zISEqicGlWkgVknKxKT-q7gM5IqlBJ4w4MZqaX6BQc_vtwQpnqaldXkH9I/root:/Test_Shubham';

INSERT

INSERT operation is not supported for this table.

Note: See UploadFile (or CreateFolder to create a folder) to insert and update content to a file.

Columns

Groups

The Office365 table Groups.

Table Specific Information

Groups require Administrator permissions. To work with them, you must create your own custom OAuth App and set the appropriate OAuthClientId and OAuthClientSecret. In this app, you must configure it to request the Group.Read.All and the Group.ReadWrite.All permissions. This can be done at https://apps.dev.microsoft.com, or in the App Registrations panel at http://portal.azure.com. See Creating a Custom OAuth Application for more details on creating a custom app.

To authorize Groups permissions, an administrator must grant the Groups permissions for your organization at large. This can be done via the administrator authorization endpoint. Simply have the administrator navigate to the following web page and grant permissions. Then run the OAuth authorization as normal afterwards.

https://login.microsoftonline.com/common/adminconsent?client_id=[YourClientId]&redirect_uri=http://localhost:33333

Note that if your organization has multiple tenants, you may replace the /common/ in the URL with the tenant ID to indicate which tenant to grant permissions for.

Select

Retrieve all groups, specify a GroupId (Id), or simply filter by a certain column:

SELECT * FROM Groups WHERE Id = 'Group Id here'
SELECT Id, Description, DisplayName FROM Groups WHERE Name = 'test'

Insert

The following are required to create a new Security Group:

INSERT INTO Groups (DisplayName, MailEnabled, MailNickname, SecurityEnabled) VALUES ('Test group', false, 'test', true)

Columns

Messages

The Office365 table Messages.

Table Specific Information

Select

You can retrieve all from Messages, specify a Message (Id), UserId, or ParentFolderId, or you can filter results by a certain column:

SELECT * FROM Messages WHERE Id = 'MyMessageId'

SELECT * FROM Messages WHERE UserId = 'MyUserId'

SELECT * FROM Messages WHERE ParentFolderId = 'MyParentfolderId'
SELECT * FROM Messages WHERE ParentFolderId = 'Drafts'
SELECT DisplayName, ID FROM Users WHERE DisplayName LIKE 'John%'

Insert

After the insert a new Message will be created in the User's Drafts folder.

INSERT INTO Messages (Subject, Body_Content, UserId) VALUES ('New test Email', 'Test Email created.', 'User ID goes here')

Note: To send the mail, see SendMail.

Known Issues

There's currently an issue with this table. Sometimes it may return an inconsistent number of results. That is, it can return X number of rows for some query and if you try that query again shortly after it will return a different numbers of rows, even though you haven't changed anything. Some rows may be missing.
This is a known API issue that currently has no workaround. As soon as Microsoft fixes it on their Microsoft Graph API then it will automatically work on this connector too.

That being said, there is a configuration that you can apply on the connector to retrieve all messages, but it comes with a downfall: 'events' and 'contacts' data will be returned along with 'messages' data. So we guarantee no purity of information. You will have to rely on your own filtering to distinguish between message and non-message rows.

In order to activate the configuration simply add "ClientSidePaging=true;" (without quotation marks) in the value of Other connection property.

Columns

Plans

The Office365 table Plans.

Table Specific Information

Using Plans requires access to Groups permissions. This requires Admin approval. For this reason, you must use your own OAuth App to add the Groups permissions and from the Microsoft Graph. See Creating a Custom OAuth Application for more details.

Select

All plans in MS Planner exist as a part of a group. In order to retrieve the list of available plans, you must retrieve a list of available plans per group. If no GroupId is specified, then the following WHERE condition will be appended to any query:

GroupId IN (SELECT ID FROM Groups)

Columns

Tasks

The Office365 table Tasks.

Table Specific Information

Tasks requires the Groups and Tasks permissions from the Microsoft Graph. For this reason, you must create your own OAuth App. Please see Creating a Custom OAuth Application for more details.

Select

By default, if no criteria is specified, only Tasks personally assigned to you will show up. For example:

SELECT * FROM Tasks

To bring back tasks across the organization, provide the specific plans ids, or use a subselect for the plan id. For example:

SELECT * FROM Tasks WHERE PlanId IN (SELECT ID FROM Plans)

Insert

To insert a Task, the associated plan must be specified:

INSERT INTO Tasks (Title, PlanId) VALUES ('My Title', '99999999-eeeeeeeee')

Update

To update a Task, both the ID and Etag must be specified:

UPDATE Tasks SET Title = 'New Title' WHERE ID = 'xxxxxx-AAAAAAAAAAA' AND Etag = 'W/\"XXXXXXQEBAQEBAQEBAQEBAQEBARCc=\"'

Delete

To delete a Task, both the ID and Etag must be specified:

DELETE FROM Tasks WHERE ID = 'xxxxxx-AAAAAAAAAAA' AND Etag = 'W/\"XXXXXXQEBAQEBAQEBAQEBAQEBARCc=\"'

Columns

Users

Read, Insert, Update and Delete a User.

Table Specific Information

Select

Query the Users table by retrieving everything from Users, specifying a Id, or filtering by a column:

SELECT * FROM Users WHERE Id = '616391f0-32d8-4127-8f25-aa55771d6617'

SELECT DisplayName, ID FROM Users WHERE DisplayName LIKE 'John%'

Insert

The following are required to create a new organizational User:

INSERT INTO Users (AccountEnabled, DisplayName, MailNickname, UserPrincipalName, PasswordProfile_ForceChangePasswordNextSignIn, PasswordProfile_Password) VALUES (false, 'John Smith', 'JohnS', 'smithjohn@yourcompanydomain.com', true, '123password')

Columns

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 Office 365 Connector Views

CalendarView

Retrieve the ccurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's.

Table Specific Information

Select

Get the occurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's. By default only the event occurrences from the user's default calendar in the range of the last 30 days will be returned. You can filter results by CalendarId, UserId, Start_DateTime, End_DateTime.

For example the following queries will be processed server side:

SELECT * FROM CalendarView WHERE Start_DateTime >= '2019-12-10 15:00' AND End_DateTime <= '2020-01-10 14:30'

SELECT * FROM CalendarView WHERE CalendarId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAAACC_IAAAA='

SELECT * FROM CalendarView WHERE CalendarId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAAACC_IAAAA=' AND UserId = 'a98f25b5-5da1-4937-8729-c0d03026caa0' AND Start_DateTime >= '2019-12-15 08:00' AND End_DateTime <= '2020-01-14 08:00'

Columns

EventOccurrences

Usage information for the operation EventOccurrences.rsd.

Table Specific Information

Select

You can query EventOccurrences by specifying the Event Id, StartDatetime and EndDateTime. EventId is a required field, instead StartDatetime and EndDateTime have a default range of the last 30 days. If you query filtering only by EventId and the specific event does not exist within this time range, you will get empty results.

SELECT * FROM [EventOccurrences] WHERE ID = 'event id' AND StartDateTime = '2018/01/01' AND EndDateTime = '2018/12/31'

By default, if StartDateTime and EndDateTime filters are not specified, only the event occurrences from the user's default calendar in the range of the last 30 days will be returned. Otherwise, the query will get the Occurrences of the Event during the period specified by StartDateTime and EndDateTime.

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft Office 365.

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

Microsoft Office 365 Connector Stored Procedures

AssignLicense

Add or remove subscriptions for the user. You can also enable and disable specific plans associated with a subscription

Input

Result Set Columns

CancelEvent

Cancels an event.

Input

Result Set Columns

CreateFolder

Upload a new file or update content to an existing file.

Input

Result Set Columns

CreateSchema

Creates a schema file for the specified table or view.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Result Set Columns

DownloadAttachments

Download the attachments of an email

Input

Result Set Columns

DownloadEmail

Download the Email

Input

Result Set Columns

DownloadFile

Download the file

Input

Result Set Columns

FetchAdditionalUserFields

Fetch all T1, T2, and T3 fields for a specified user.

Input

Result Set Columns

ForwardEvent

Forward events to recipients.

Input

Result Set Columns

ForwardMail

Retrieve Forwarded Mail.

Input

Result Set Columns

GetAdminConsentURL

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from Office365.

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

MoveMail

Move mail.

Input

Result Set Columns

RefreshOAuthAccessToken

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

Input

Result Set Columns

SendMail

Send mail.

Input

UploadFile

Upload a new file or update content to an existing file.

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 Office 365:

• 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 Events table:

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

Columns