Microsoft Office 365 Connection

See the Connector Marketplace topic. Please request your administrator to start a trial or subscribe to the Premium Microsoft Office 365 connector.

Microsoft Office 365 is a cloud-based suite of productivity tools including Word, Excel, PowerPoint, and Teams, fostering seamless communication and collaboration.

This topic describes how to authenticate to Microsoft Office 365 and configure any necessary connection properties in the Microsoft Office 365 connection connector.

Prerequisites

Authenticating to Microsoft Office 365

Microsoft Office 365 uses the OAuth authentication standard. To authenticate using OAuth, you will need to create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties.

Azure AD is user-based authentication. It requires you to set AuthScheme to AzureAD.

Azure Service Principal

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user.

All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles’ permissions.

To use Azure Service Principal authentication, you must:

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

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

Do the following:

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

  2. Assign a role to the application:

  • Use the search bar to search for the Subscriptions service.

  • Open the Subscriptions page.

  • Select the subscription to which to assign the application.

  • Open the Access control (IAM).

  • Select Add > Add role assignment. Microsoft Office 365 opens the Add role assignment page.

  • Assign your custom Azure AD application the role of Owner.

  1. Set the appropriate connections property groups.

For all application authentication schemes:

- **AuthScheme:** The *AzureServicePrincipal* in your app settings.

- **InitiateOAuth:** *GETANDREFRESH*. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

- **AzureTenant:** The tenant you wish to connect to.

- **OAuthClientId:** The client Id in your app settings.

For Client Secret authentication:

- **OAuthClientId:** The client Id in your app settings.

- **OAuthClientSecret:** The client secret in your app settings.

You are now ready to connect.

Creating a Custom Azure AD Application

The following steps describe how to create and register a custom Azure AD application, and obtain the OAuth values for your application.

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

  2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.

  3. Click New registration.

  4. Enter a name for the application.

  5. Select the desired tenant setup: single- or multi-tenant, and public or private use.

  • If you select the default option, “Accounts in this organizational directory only”, you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection for Microsoft Office 365. Otherwise, the authentication attempt fails with an error.

  • If your application is for private use only, specify Accounts in this organization directory only.

  • If you want to distribute your application, choose one of the multi-tenant options.

  1. Set the redirect url to http://localhost:33333 (default) OR specify a different port and set CallbackURL to the exact reply URL you defined.

  2. Click Register to register the new application. This opens an application management screen.

Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.

  1. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.

  • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.

  • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft Office 365 displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.

  1. Select API Permissions > Add > Delegated permissions.

  2. Select the Microsoft Graph API and then select the permissions your app will seek.

  3. Save your changes.

  4. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

Connection Configuration

Each connection property available in the Microsoft Office 365 connector is explained below.

Connection Name

The name of the connection to be created should be provided. This is the name that will display on the list of available connections.

Auth Scheme

The type of authentication to use when connecting to Microsoft Office 365.

Possible Values:

  • AzureAD: Set this to perform Azure Active Directory OAuth authentication.

  • AzureServicePrincipal: Set this to authenticate as an Azure Service Principal using a Client Secret.

Client ID

The client Id assigned when you register your application with an OAuth authorization server.

Client Secret

The client secret assigned when you register your application with an OAuth authorization server.

Access Token

The access token for connecting using OAuth.

Refresh Token

The OAuth refresh token for the corresponding OAuth access token.

Grant Type

The grant type for the OAuth flow.

The following options are available: CODE,CLIENT

Azure Tenant

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default).

However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The Azure Tenant is required when setting OAuthGrantType to CLIENT.

When using client credentials, there is no user context. The credentials are taken from the context of the app itself.

While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with.

For this reason, AzureTenant is to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

Azure Environment

The Azure Environment to use when establishing a connection.

Possible Values:

Global, China, USGOVT, USGOVTDOD

In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment.

Directory Retrieval Depth

Specify how many folders deep you want to get results for Files table. The resources directly in the root are located at depth 0. The resources one folder deeper are located at depth 1 and so on. You can specify the value -1 to get all the data in a drive no matter the depth they’re in.

This property must be set or a default of Directory Retrieval Depth = 5 will be used.

Add Configuration: Additional properties can be added using this option as key-value pairs.

After entering all the details, click on the TEST button.

If the connection service identification and authentication details are provided correctly, a success message stating “connection available” is generated.

Click on the CREATE button to save the changes.

If the details are incorrect or the server is down, you will get a message “Connection unavailable”.

If you have any feedback on Gathr documentation, please email us!

← Microsoft Exchange ETL Target
Microsoft Office 365 Ingestion Source →