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 AzureAD, 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 Azure AD¶

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

Authenticate using Azure Service Principal¶

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

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 Azure Active Directory 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 Azure Active Directory, 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 Azure AD 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 an Azure Active Directory 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 Azure Service Principal Authentication¶

The authentication as an Azure 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 an Azure Service Principal.

Follow the steps below to create a custom OAuth app and obtain the connection properties for the Azure 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 Azure Active Directory then App Registrations and click on New registration button.
3. Enter an app name and set the radio button for "Any Azure AD Directory - Multi Tenant". 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 Harmony 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¶