Creating an Azure AD Application

Microsoft Excel Online supports user-based authentication using Azure AD. This authentication is OAuth-based.

CData embeds OAuth Application Credentials with CData branding that can be used when connecting to Microsoft Excel Online via a desktop application or a headless machine. To connect to Microsoft Excel Online via the Web, you must always create a custom application, as described here.

However, since custom Azure AD applications seamlessly support all three commonly-used authentication flows, you might want to create a custom application (use your own Azure AD Applications Credentials) for those other authentication flows.

Custom OAuth applications are useful if you want to:

• Control branding of the authentication dialog.
• Control the redirect URI that the application redirects the user to after the user authenticates.
• Customize the permissions that you are requesting from the user.

Authenticating With Azure AD

In https://portal.azure.com:

1. In the left-hand navigation pane, select Azure Active Directory > App registrations.
2. Click New registration.
3. Enter a name for the application.

4. Specify the types of accounts this application should support:

   • For private use applications, select Accounts in this organization directory only.
   • For distributed applications, select one of the multi-tenant options.

   Note: If you select Accounts in this organizational directory only (default), when you establish a connection with CData Power BI Connector for Microsoft Excel Online you must set AzureTenant to the Id of the Azure AD Tenant. Otherwise, the authentication attempt fails.

5. Set the redirect URI to http://localhost:33333 (default) OR, if you want to specify a different port, specify the desired port and set CallbackURL to the exact reply URL you just defined.
6. To register the new application, click Register. An application management screen displays. Record these values for later use. (You will use the Application (client) ID value to set the OAuthClientId parameters, and the Directory (tenant) ID value to set the AzureTenant parameter.)
7. Navigate to Certificates & Secrets. Select New Client Secret for this application and specify the desired duration. After the client secret is saved, the Azure App Registration displays the key value. This value is displayed only once, so record it for future use. (You will use it to set the OAuthClientSecret.)
8. Add the following application permissions: Sites.Read.All, Files.Read, Files.Read.All, Files.Read.Selected, Files.ReadWrite, Files.ReadWrite.All, Files.ReadWrite.AppFolder, Files.ReadWrite.Selected, and offline_access.
9. 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.

Granting Admin Consent

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

To grant admin consent:

1. Have an admin log in to https://portal.azure.com.
2. Navigate to App Registrations and find the custom Azure AD application you created.
3. Under API Permissions, click Grant Consent and follow the wizard.

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