Microsoft Exchange v2 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8858 of the connector.

Get Started

Microsoft Exchange Version Support

The Jitterbit Connector for Microsoft Exchange connects with the REST API.

Establish a Connection

Connect to Microsoft Exchange

There are two schemas available for connecting to Exchange:

• Exchange Web Services (EWS), which is no longer being updated but is still available for both Exchange OnPremise and Exchange Online.
  

• Microsoft Graph

For a look at the data model for each of these schemas, see "Data Model".

To switch between EWS and Microsoft Graph, set Schema to either EWS or MSGraph.

Exchange Online users who still want to use EWS should set Schema to EWS and the Platform to Exchange_Online.

Exchange OnPremise

Microsoft Exchange OnPremise defaults set User, Password, and AuthScheme to support Basic authentication. However, OnPremise also supports authentication via Windows (NTLM), Kerberos (described below) and deletation.

Exchange Online

Exchange Online supports authentication via OAuth, as described below. (Azure AD and Azure Service Principal also use custom OAuth applications for authentication.)

If you are connecting to Exchange Online platform through EWS, set AuthScheme to AzureAD, AzureServicePrincipal, or AzureMSI.

If you connect to Exchange Online through Microsoft Graph, set Schema to MSGraph. When Schema is set to MSGraph, the Platform is ignored.

Authenticate to Microsoft Exchange

Microsoft Exchange supports authentication using OAuth (Azure AD and Azure Service Principal), Managed Service Identity (MSI), or Kerberos.

OAuth

Azure AD and Azure Service Principal both require OAuth-based authentication.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

Microsoft Exchange provides embedded OAuth credentials that simplify connection from a Desktop application . To connect from a Web application, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

To connect via OAuth from all authentication flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Microsoft Exchange from all applicable auth flows. For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that already have embedded OAuth credentials, see Creating a Custom OAuth Application. For a complete list of connection string properties available in Microsoft Exchange, see Connection.

Desktop Applications

provides an embedded OAuth application that simplifies authentication at the desktop.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Microsoft Exchange console. For further information, see Creating a Custom OAuth Application.

Before you connect, set the following variables:

• InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken. provides an embedded OAuth application that simplifies authentication at the desktop; that is, in situations where the user is using a local server not connected to the internet.

  You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Microsoft Exchange console. For further information, see Creating a Custom OAuth Application.

• Custom Azure AD applications only:

  • OAuthClientId: The client ID assigned when you registered your custom OAuth application.
  • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
  • CallbackURL: The redirect URI defined when you registered your custom OAuth application.

When you connect, the connector opens the Microsoft Exchange'sOAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

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 application are executed without a default user context, but based on the assigned roles.

The application access to the resources is controlled through the assigned roles' permissions.

For information about how to set up Azure Service Principal authentication, see Creating a Custom OAuth Application.

Authenticate via Client Secret

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.

Authentication via client credentials requires the use of a custom OAuth application. For further information, see Creating a Custom OAuth Application.

After you set these parameters, you are ready to connect.

• 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, just like any other connection, except that no window opens to prompt the user to log in. Because there is no user context, there is no need for a browser popup. Connections are made and handled internally.

Managed Service Identity (MSI)

If you are running Microsoft Exchange on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Kerberos

Authenticating to Microsoft Exchange via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

To authenticate to Microsoft Exchange using Kerberos, set these properties:

• AuthScheme: NEGOTIATE.
• KerberosKDC: The host name or IP Address of your Kerberos KDC machine.
• KerberosRealm: The realm of the Microsoft Exchange Kerberos principal. Find this value immediately after the '@' symbol of the principal value.
• KerberosSPN: The service and host of the Microsoft Exchange Kerberos Principal. Find this value just before the '@' symbol of the principal value.

For further information on how to use Kerberos in an Microsoft Exchange environment, see Using Kerberos.

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.
• 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.
2. 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 Exchange 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 Jitterbit Connector for Microsoft Exchange in the client OAuth flow. You must create your own OAuth application to use client credentials. See Creating a Custom OAuth Application 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 Exchange 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 Jitterbit Connector for Microsoft Exchange. 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 Exchange 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. If you are connecting to Microsoft Exchange through EWS schema, select Exchange API and add EWS.AccessAsUser.All permission. If you are connecting to Microsoft Exchange through MSGraph schema, select Microsoft Graph API and add the following permissions: Calendars.ReadWrite.Shared, Contacts.ReadWrite, Group.Read.All, Group.ReadWrite.All, User.ReadWrite.All, and Mail.ReadWrite.Shared.
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 Exchange 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.

Use Kerberos

Kerberos

To authenticate to Microsoft Exchange with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to Microsoft Exchange via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log in at their local computer or enter kinit USER at the command prompt.

The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.

3. To obtain a ticket:

   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The connector uses the cache file to obtain the Kerberos ticket to connect to Microsoft Exchange.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with Microsoft Exchange.

Enable Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Exchange Online Administrative Tasks

The Jitterbit Connector for Microsoft Exchange can be used to perform administrative tasks with MSGraph Schema. 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.

Select Exchange Data

Note: The following describes the behavior when Schema is set to EWS. It has no impact on MSGraph.

FindItem vs. GetItem

By default, the connector will perform the Exchange Web Services FindItem API call and only request summary information about items when a SELECT operation is performed. Any request that could return more than one item will only return summary information. For example:

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE Surname='Smith'

If you wish to request the contents of a message or more information about a contact or calendar event, you will need to set IncludeContent to TRUE, specify the ItemIds of the items, or limit your results to a single item. For example:

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE ItemId='AZQ111222...'

OR

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE ItemdId IN ('AZQ111222...', 'AZQ111223...', 'AZQ111224...', 'AZQ111225...')

OR

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE Surname='Smith' LIMIT 1

Public and Custom Folders

If you wish to read from a Public or Custom folder, you'll need to first identify the FolderId of the folder you wish to read from. This can be done by submitting a read query from the ParentFolder (for a custom folder) or from the relevant table for the type of object stored in a Public Folder, using Inbox if the Public Folder

contains messages. For example:

Finding the FolderId of a subfolder of the Inbox:

SELECT DisplayName, FolderId FROM Inbox

Finding the FolderId of a Custom Folder that contains Contacts:

SELECT DisplayName, FolderId FROM Contacts WHERE ParentFolderName='publicfoldersroot'

If your public folder is nested, you may need to do a separate SELECT query on the parent custom folder:

SELECT DisplayName, FolderId FROM Contacts WHERE ParentFolderId='AAEuAAAAAAAa...'

Create Items in Public and Custom Folders

Insert into Public and Custom Folders

If you wish to create an item in a Public or Custom folder, you'll need to first identify the FolderID of the folder you wish to read from (see Selecting Exchange Data). Once you know the FolderID, you can use this value as the ParentFolderId when you create a new object. To create an object in a custom or public folder, use the relevant

object type as the table (or Inbox if the folder contains Messages). For example:

Inserting into a subfolder of the Inbox:

INSERT INTO Inbox (Subject, FromEmailAddress, ToRecipients_EmailAddress, ParentFolderId) VALUES ('New email message', 'user1@email.com', 'user2@email.com', 'AAEuAAAAAAAa...')

Inserting into a Public Folder that contains Contacts:

INSERT INTO Contacts (GivenName, Surname, EmailAddress1, ParentFolderId) VALUES ('Jill', 'Smith', 'user3@email.com', 'AAEuAAAAAAAa...')

Update or Deleting Items in Public and Custom Folders

Update or Deleting an Item in a Public and Custom Folders

Unlike reading from or inserting into a Public or Custom folder, you do not need the FolderId in order to update or delete an item. You simply need to know what type

of object the folder contains and use that type as the Table (or use Inbox for a folder that contains Messages). For example:

Updating a Message item in a custom folder:

UPDATE Inbox SET Subject = 'Updated email message' WHERE ItemID = 'AZQ111222...')

Deleting a Contact item from a Public Folder:

DELETE FROM Contacts WHERE ItemID = 'AZQ111222...')

Fine-Tuning Data Access

Impersonate Users

This authentication method is typically used by administrators to configure access by a service account.
To impersonate all requests, set the following connection properties at connection time.
To impersonate a user for an individual query, use the pseudo columns of the same name.

• ImpersonationUser: Set this to the user to impersonate.
• ImpersonationType: Set this to the format you are using to specify the user. For example, UPN or SID.

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

See Query Processing for more information.

User Defined Views

The Jitterbit Connector for Microsoft Exchange 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 Contacts 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 Jitterbit Connector for Microsoft Exchange models the Microsoft Exchange 2 and Microsoft Exchange APIs as relational tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The available entities, as well as any API limitations and requirements for querying these entities, are documented in EWS Data Model and MSGraph Data Model.

Overview

The Data Models illustrate an example of what your Microsoft Exchange environment might look like. The actual data model will be obtained dynamically based on your Microsoft Exchange account.

Key Features

• Tables and Views are dynamically defined to model calendars, documents, and projects on Microsoft Exchange.
• Stored procedures allow you to execute operations to Microsoft Exchange, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your Microsoft Exchange account are immediately reflected when using the connector.

EWS Data Model

EWS Data Model describes the schemas available to connect to Microsoft Exchange OnPremise and Microsoft Exchange Online using EWS. You can use tables to work with live Microsoft Exchange data. You can use stored procedures provided by Jitterbit Connector for Microsoft Exchange to automate working with Microsoft Exchange data.

MSGraph Data Model

MSGraph Data Model describes the schemas available to connect to Microsoft Exchange Online accounts via the Microsoft Graph. You can use tables to work with live Microsoft Exchange data. You can use stored procedures provided by Jitterbit Connector for Microsoft Exchange to automate working with Microsoft Exchange data.

EWS Data Model

The Jitterbit Connector for Microsoft Exchange models Microsoft Exchange entities as relational Tables and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files.

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

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

Tables

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

Jitterbit Connector for Microsoft Exchange Tables

Calendar

Create, update, delete, and query Calendar items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

SELECT Recurring Events

When performing a SELECT operation on the Calendar table, the connector will not include individual recurring events by default (only the master item will be included). Your query will need to include a WHERE clause similar to the following:

SELECT Subject, IsRecurring, Recurrence_StartDate, Recurrence_EndDate, Recurrence_Interval, Recurrence_Type, Recurrence_NumberOfOccurrences, FirstOccurrence_Start FROM Calendar WHERE ItemId = 'myid'

If you wish to view the individual recurrences of a recurring event, you'll need to add filters START and END with greaterthan and lessthan operators respectively. The interval between START and END should not be more than 2 years. The API will return atmost 1000 entries for recurring events. Please give the interval between START and END filters accordingly. Your query will need to include a WHERE clause similar to the following:

SELECT * FROM Calendar WHERE start >= '2021-05-11 00:50:59.0' AND end <= '2023-02-28 11:20:30.0'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Calendar_OptionalAttendees

The optional attendees for a particular event. An ItemId must be specified when querying this view.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Inserting

When performing a SELECT operation on the Calendar table, the connector will not include individual recurring events by default (only the master item will be included). If you wish to view the individual recurrences of a recurring event, you'll need to filter the search by the IsRecurring column and use Start and End to specify a time period. Your query will need to include a WHERE clause similar to the following:

INSERT INTO Calendar_OptionalAttendees (EmailAddress, ItemId, SendMeetingInvitations) VALUES ('johndoe@example.com', 'itemid', 'SendOnlyToChanged')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Calendar_RequiredAttendees

The required attendees for a particular event. An ItemId must be specified when querying this view.

Table Specific Information

Insert Statements

When performing an INSERT operation, you will need to specify EmailAddress and ItemId. Additionally, there is a property called SendMeetingInvitations you can set to decide who is notified when you insert required attendees.

INSERT INTO Calendar_RequiredAttendees (EmailAddress, ItemId, SendMeetingInvitations) VALUES ('johndoe@example.com', 'itemid', 'SendOnlyToChanged')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Contacts

Create, update, delete, and query Contacts items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

DeletedItems

Create, update, delete, and query Deleted Items.

Table Specific Information

Select Emails from DeletedItems Subfolders

When performing a SELECT operation on the DeletedItems table, the connector will not include the items in the subfolders under DeletedItems, but only the items contained within the DeletedItems folder. If you wish to retrieve the items under the DeletedItems subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every DeletedItems subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the DeletedItems subfolders:

SELECT * FROM DeletedItems WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'DeletedItems' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Drafts

Create, update, delete, and query Drafts items.

Table Specific Information

Select Emails from Drafts Subfolders

When performing a SELECT operation on the Drafts table, the connector will not include the items in the subfolders under Drafts, but only the items contained within the Drafts folder. If you wish to retrieve the items under the Drafts subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Drafts subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Drafts subfolders:

SELECT * FROM Drafts WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Drafts' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Folders

Create, update, delete, and query subfolders for a given folder.

Table Specific Information

Update and Delete

The connector will need the FolderChangeKey to update or delete an item. However, if you are unsure of the FolderChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it take to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Inbox

Create, update, delete, and query Inbox items.

Table Specific Information

Select Emails from Inbox Subfolders

When performing a SELECT operation on the Inbox table, the connector will not include the items in the subfolders under Inbox, but only the items contained within the Inbox folder. If you wish to retrieve the items under the Inbox subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Inbox subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Inbox subfolders:

SELECT * FROM Inbox WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Inbox' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

JunkEmail

Create, update, delete, and query Junk Email items.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Outbox

Create, update, delete, and query Outbox items.

Table Specific Information

Select Emails from Outbox Subfolders

When performing a SELECT operation on the Outbox table, the connector will not include the items in the subfolders under Outbox, but only the items contained within the Outbox folder. If you wish to retrieve the items under the Outbox subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Outbox subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Outbox subfolders:

SELECT * FROM Outbox WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Outbox' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

SentItems

Create, update, delete, and query Sent Items.

Table Specific Information

Select Emails from SentItems Subfolders

When performing a SELECT operation on the SentItems table, the connector will not include the items in the subfolders under SentItems, but only the items contained within the SentItems folder. If you wish to retrieve the items under the SentItems subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every SentItems subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the SentItems subfolders:

SELECT * FROM SentItems WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'SentItems' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Tasks

Create, update, delete, and query Tasks items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Recurrence Fields

In order to INSERT, SELECT, or UPDATE the Recurrence fields in a Task, you'll need to make sure that you only set the fields associated with the Recurrence_Type and Recurrence_Duration fields. Please see the tables below:

Recurrence_Type Values & Associated Fields

Recurrence_Duration & Associated Fields

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Stored Procedures

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

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

Jitterbit Connector for Microsoft Exchange Stored Procedures

CreateAttachments

Create and add a attachment to an existing email.

Stored Procedure Specific Information

Use CreateAttachments procedure to add an attachment to an existing email. To specify file paths of the attachments use Attachments input, whereas for base 64 encoded content specify AttachmentContent and AttachmentName.

EXECUTE CreateAttachments ItemId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD/FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAxEAAABDJHYjDdygQ5pQPUEu3S2cAAVZoayvAAAA', Attachments = 'C:\Users\User\Desktop\logfile.txt,C:\Users\User\Desktop\TestConnectionLog.txt'

Input

Result Set Columns

GetAttachment

Retrieves the indicated attachments.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from Outlook.

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 OAuthAccessToken from this URL.

Input

Result Set Columns

GetUserOofSettings

Provides access to the OOF settings of a user. A user is identified by the email address of the user. If the OOF message is null and OOF is enabled, no OOF message is sent.

Input

Result Set Columns

MoveItem

Move messages from one folder to another.

Input