Microsoft Exchange Connection Details

Introduction

Connector Version

This documentation is based on version 21.0.8662 of the connector.

Get Started

Exchange Version Support

The Jitterbit Connector for Exchange connects with the REST API.

Establish a Connection

Available Schemas

There are two services available for connecting to Exchange. These are EWS (Exchange Web Services) and the Microsoft Graph. Exchange Web Services is available for both Exchange OnPremise and Online, but is no longer receiving updates. Microsoft recommends switching to using the Microsoft Graph for Exchange Online users. Both are available with our tool.

To switch between the two, use the Schema connection property to set either EWS or MSGraph. If you wish to use EWS with Exchange Online, set Schema to EWS and the Platform to Exchange_Online.

Connect to Exchange using Exchange OnPremise

When using an OnPremise edition of Exchange, OnPremise Set User, Password, and AuthScheme; by default, the connector performs Basic authentication, but Windows (NTLM), Kerberos, and delegated authentication are also supported.

Authenticate with Kerberos

Please see Using Kerberos for details on how to authenticate with Kerberos

In addition to the authentication values, set the Server property to the address of the Exchange server you are connecting to and set Platform to the Exchange version. Finally, set Schema to EWS.

Connect to Exchange using Exchange Online

When connecting to Exchange Online, authentication will be done via OAuth. If you are connecting to Exchange Online platform through EWS, set the AuthScheme property to either Microsoft Entra ID, AzureServicePrincipal or AzureMSI. Otherwise if you will be using Microsoft Graph to connect to Exchange Online, resources will be pulled from a different service so the Schema should be set to MSGraph. When Schema is set to MSGraph, the Platform value will be ignored.

Authenticate using Microsoft Entra ID

Entra ID is a connection type that goes through OAuth. Set your AuthScheme to AzureAD and see Using OAuth Authentication for an authentication guide.

Authenticate using Microsoft Entra service principal

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

Authenticate using MSI Authentication

If you are running Exchange on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:

• AuthScheme: Set this to AzureMSI.

The MSI credentials will then be automatically obtained for authentication.

Use OAuth Authentication

OAuth requires the authenticating user to interact with Exchange using the browser. The connector facilitates this in various ways as described below.

Embedded Credentials

See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.

Custom Credentials

Instead of connecting with the connector's embedded credentials, you can register an app with Custom Credentials to obtain the OAuthClientId and OAuthClientSecret.

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Exchange and you can connect with its embedded credentials. You might want to create a custom OAuth app to change the information displayed when users log into the Exchange OAuth endpoint to grant permissions to the connector.

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure.

Embedded Credentials

Authenticate using the Embedded OAuth Credentials

Desktop Authentication with the Embedded OAuth App

You can connect without setting any connection properties for your user credentials.

When you connect, the connector opens the OAuth 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 to be persisted across connections.

Custom Credentials

There are two types of app authentication available: using a client secret and using a certificate. You can use any of them depending on the configured app authentication.

Desktop Authentication with Your OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Get an OAuth Access Token

You are ready to connect after setting one of the below connection properties groups depending on the authentication type.

1. Authenticating using a Client Secret
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthClientSecret: Set this to the Client Secret in your app settings.
   • CallbackURL: Set this to the Redirect URL in your app settings.
   • AuthScheme: Set this to the "AzureAD" in your app settings.
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken. .
2. Authenticating using a Certificate
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.
   • CallbackURL: Set this to the Redirect URL in your app settings.
   • AuthScheme: Set this to the "AzureAD" in your app settings.
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken. .

When you connect the connector opens the OAuth 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 to be persisted across connections.

Headless Machines

Use OAuth on a Headless Machine

To create Exchange data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the connector to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the connector's embedded OAuth credentials or using the OAuth credentials for your custom OAuth app.

Use the Credentials for a Custom OAuth App

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Obtain a Verifier Code

On the headless machine, set one the following properties groups depending on the authentication type:

1. Authenticating using a Client Secret
   • InitiateOAuth: Set this to OFF.
   • OAuthClientId: Set this to the App ID in your app settings.
   • OAuthClientSecret: Set this to the App Secret in your app settings.
2. Authenticating using a Certificate
   • InitiateOAuth: Set this to OFF.
   • OAuthClientId: Set this to the App ID in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

1. Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL. The webpage will state that the site could not be reached.
3. Inspect the URL of the this site page and find the code value. It will be present in the URL in the form code=XXXXXX (terminated with &, which denotes the next URL parameter) The value after the "code=" is the verifier code.
4. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the one of the following connection properties groups depending on the authentication type to obtain the OAuth authentication values:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthVerifier: Set this to the verifier code.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set this to REFRESH.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the one of the following properties groups depending on the authentication type:

1. Authenticating using a Client Secret
   • OAuthSettingsLocation: Set this to a writable text file.
   • InitiateOAuth: Set this to GETANDREFRESH.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthClientSecret: Set this to the Client Secret in your app settings.
   • CallbackURL: Set this to the Callback URL in your app settings.
2. Authenticating using a Certificate
   • OAuthSettingsLocation: Set this to a writable text file.
   • InitiateOAuth: Set this to GETANDREFRESH.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.
   • CallbackURL: Set this to the Callback URL in your app settings.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Create a Custom OAuth App

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Exchange and you can connect with its embedded credentials.

You might want to create a custom OAuth app to change the information displayed when users log into the Exchange OAuth endpoint to grant permissions to the connector.

Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.

Steps to Create a Custom OAuth App

Follow the steps below to obtain the OAuth values for your app, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.

2. In the left-hand navigation pane, select Microsoft Entra ID then App Registrations and click the New registration button.

3. Enter an app name and set the radio button for the desired tenant setup.

   When creating a custom OAuth application in Microsoft Entra ID, you can define if the application is single- or multi-tenant. If you select the default option of "Accounts in this organizational directory only", you will need to set the AzureTenant connection property to the ID of the Microsoft Entra ID Tenant when establishing a connection with the Jitterbit Connector for Exchange. Otherwise, the authentication attempt will fail with an error. If your app is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your app, choose one of the multi-tenant options.

4. Then set the redirect URL to something such as http://localhost:33333, the connector's default. Or, set a different port of your choice and set CallbackURL to the exact reply URL you defined.

5. Define the app authentication type by going to the Certificates & Secrets section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate, but you can also create an application secret.

   • Option 1 - Upload a certificate: In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as the OAuthClientSecret.

6. Select API Permissions and then click Add. If you plan for your app to connect without a user context, select the Application Permissions (OAuthGrantType = CLIENT). Otherwise, when selecting permissions, use the Delegated permissions.

7. If you are connecting to Exchange through EWS schema, select Exchange API and add EWS.AccessAsUser.All permission. If you are connecting to 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.

8. Save your changes.

9. If you have selected to use permissions that require admin consent (such as the Application Permissions), you may grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under Admin Consent.

Admin Consent

Admin consent refers to when the Admin for a Microsoft Entra ID tenant grants permissions to an application which requires an admin to consent to the use case. The embedded app within the Jitterbit Connector for Exchange, contains no permissions that require admin consent. Therefore, this information applies only to custom applications.

Admin Consent Permissions

When creating a new OAuth app in the Azure Portal, you must specify which permissions the app will require. Some permissions may be marked stating "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your app requires admin consent, there are a couple of ways this can be done.

The easiest way to grant admin consent is to just have an admin log into portal.azure.com and navigate to the app you have created in App Registrations. Under API Permissions, there will be a button for Grant Consent. You can consent here for your app to have permissions on the tenant it was created under.

If your organization has multiple tenants or the app needs to be granted permissions for other tenants outside your organization, the GetAdminConsentURL may be used to generate the Admin Authorization URL. Unlike the GetOAuthAuthorizationURL, there will be no important information returned from this endpoint. If the admin grants access, it will simply return a boolean indicating that permissions were granted.

Once an admin grants consent, authentication may be performed as normal.

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 app itself. All tasks taken by the app are done without a default user context. This makes the authentication flow a bit different from standard.

Client OAuth Flow

All permissions related to the client oauth flow require admin consent. This means the app embedded with the Jitterbit Connector for Exchange cannot be used in the client oauth flow. You must create your own OAuth app in order to use client credentials. See Creating a Custom OAuth App for more details.

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 and Application permissions. The permissions used during client credential authentication are under Application Permissions. Select the applicable permissions you require for your integration.

You are ready to connect after setting one of the below connection properties groups depending on the authentication type.

1. Authenticating using a Client Secret
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthGrantType: Set this to CLIENT.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthClientSecret: Set this to the Client Secret in your app settings.
2. Authenticating using a Certificate
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthGrantType: Set this to CLIENT.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Authentication with client credentials will take place automatically like any other connection, except there will be 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.

Use Microsoft Entra service principal Authentication

The authentication as a Microsoft Entra service principal is handled via the OAuth Client Credentials flow, and it does not involve direct user authentication. Instead, credentials are created for just the app itself. 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.

Custom Credentials

You will need to register an OAuth app to obtain the OAuth property values before connection to the Exchange data source. You can check the Custom Credentials guide on how to set the OAuth properties.

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure.

Create a Custom OAuth App

Creating a custom OAuth app and a service principal that can access the necessary resources is required when authenticating using a Microsoft Entra service principal.

Follow the steps below to create a custom OAuth app and obtain the connection properties for the Microsoft Entra service principal authentication.

Steps to Create a Custom OAuth App

Follow the steps below to obtain the OAuth values for your app.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Microsoft Entra ID then App Registrations and click on New registration button.
3. Enter an app name and set the radio button for Accounts in any organizational directory (Any Microsoft Entra ID tenant - Multitenant). Then set the redirect URL to something such as http://localhost:33333, the connector's default.
4. Copy the Application (client) ID value displayed on the Overview section after creating the app, since this value is used as the OAuthClientId
5. Define the app authentication type by going to the Certificates & Secrets section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate, but you can also create an application secret.
   • Option 1 - Upload a certificate: In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as the OAuthClientSecret.
6. In the Authentication tab, make sure to check the option: Access tokens (used for implicit flows).
7. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
8. Select the particular subscription to assign the application to, then open the Access control (IAM) section, and click on the Add role assignment button.
9. Select Owner as the role to assign to your created OAuth app.

Custom Credentials

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Authentication with Your OAuth App

There are two types of app authentication available: using a client secret and using a certificate. You can use any of them depending on the configured app authentication.

Get an OAuth Access Token

You are ready to connect after setting one of the below connection properties groups depending on the authentication type.

1. Authenticating using a Client Secret
   • AuthScheme: Set this to the "AzureServicePrincipal" in your app settings.
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthClientSecret: Set this to the Client Secret in your app settings.
2. Authenticating using a Certificate
   • AuthScheme: Set this to the "AzureServicePrincipal" in your app settings.
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthClientId: Set this to the Client ID in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Use Kerberos

This section shows how to use the connector to authenticate to Exchange using Kerberos.

Authenticate with Kerberos

To authenticate to Exchange using Kerberos, set the following properties:

• AuthScheme: Set this to NEGOTIATE.
• KerberosKDC: Set this to the host name or IP Address of your Kerberos KDC machine.
• KerberosRealm: Set this to the realm of the Exchange Kerberos principal. This will be the value after the '@' symbol (for instance, EXAMPLE.COM) of the principal value (for instance, ServiceName/MyHost@EXAMPLE.COM).
• KerberosSPN: Set this to the service and host of the Exchange Kerberos Principal. This will be the value prior to the '@' symbol (for instance, ServiceName/MyHost) of the principal value (for instance, ServiceName/MyHost@EXAMPLE.COM).

Retrieve the Kerberos Ticket

You can use one of the following options to retrieve the required Kerberos ticket.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you won't need to set the User or Password connection properties with this option.

1. Ensure that you have an environment variable created called KRB5CCNAME.
2. Set the KRB5CCNAME environment variable to a path pointing to your credential cache file (for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file will be created when generating your ticket with MIT Kerberos Ticket Manager.
3. To obtain a ticket, open the MIT Kerberos Ticket Manager application, click Get Ticket, enter your principal name and password, then click OK. If successful, ticket information will appear in Kerberos Ticket Manager and will now be stored in the credential cache file.
4. Now that the credential cache file has been created, the connector will use the cache file to obtain the kerberos ticket to connect to Exchange.

As an alternative to setting the KRB5CCNAME environment variable, you can directly set the file path using the KerberosTicketCache property. When set, the connector will use the specified cache file to obtain the kerberos ticket to connect to Exchange.

Keytab File

If the KRB5CCNAME environment variable has not been set, you can retrieve a Kerberos ticket using a Keytab File. To do this, 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 both the KRB5CCNAME environment variable and the _KerberosKeytabFile_ property have not been set, you can retrieve a ticket using a User and Password combination. To do this, set the User and Password properties to the user/password combo that you use to authenticate with Exchange.

Cross-Realm Authentication

More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g. where one realm/KDC is used for user authentication and another realm/KDC used for obtaining the service ticket).

In such an environment, the KerberosRealm and KerberosKDC properties can be set to the values required for user authentication. The KerberosServiceRealm and KerberosServiceKDC properties can be set to the values required to obtain the service ticket.

Exchange Online Administrative Tasks

The Jitterbit Connector for 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 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.

User Defined Views

The Jitterbit Connector for 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"

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 Exchange models the Exchange 2 and 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. You can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Overview

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

Key Features

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

EWS Data Model

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

MSGraph Data Model

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

EWS Data Model

The Jitterbit Connector for Exchange models 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 Exchange. They can be used to search, update, and modify information in Exchange.

Tables

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

Generally, querying Exchange tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for 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). If you wish to view the individual recurrences of a recurring event, you'll need to specify the ItemId . 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'

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 available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for 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