Configure Office 365 Authentication for Dynamics NAV or Business Central On Premises – Support Topics

This article applies to using the Jet Reports Excel Add-in (2017 or higher) with Dynamics NAV 2015 - 2018 or Business Central On Premises

Overview

The purpose of this guide is to outline the procedures necessary in order to configure the Jet Excel add-in to use Office 365 credentials for authentication to a Dynamics NAV 2015 - 2018 or Business Central On Premises using OAuth.

Once an instance of Dynamics NAV or Business Central has been configured for Office 365 single sign-on, there are only a few small steps required to use the same Office 365 credentials for authentication using the Jet Excel add-in.

Note - This authentication method does not currently support Multi Factor Authentication.

Prerequisites

The configuration instructions assume that these changes have already been made successfully:

The Dynamics NAV or Business Central On Premises environment has already been configured for users to authenticate with Azure Active Directory. This is explained in the following document for Dynamics NAV https://docs.microsoft.com/en-us/dynamics-nav/authenticating-users-with-azure-active-directory or the following document for Business Central On Premises https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/authenticating-users-with-azure-active-directory
Enable SOAP Services in the Dynamics NAV Server or Business Central On Premises Server Instance. This is explained in the following document for Dynamics NAV https://docs.microsoft.com/en-us/dynamics-nav/configuring-microsoft-dynamics-nav-server#SOAPServices or the following document for Business Central On Premises https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/configure-server-instance#SOAPServices
The registered Dynamics NAV or Business Central application must have a custom scope defined in the Expose an API section of the App Registration in the Azure portal. An example scope name for this is user_impersonation.
A Dynamics NAV 2013 - 2018 (Web Services) or Dynamics 365 Business Central (Web Services) On Premises data source has already been created.

Configuration

There are four steps are required in order to fully complete the Office 365 authentication configuration.

Get the Server Application URI
Configure the Server Application ID in the Microsoft Dynamics NAV or Business Central Instance Settings
Register the Jet Excel add-in as a client application in Azure Active Directory
Configure the Data Source in the Jet Excel Add-In

Get the Server Application URI

Go to https://portal.azure.com and sign in with the credentials for your Azure subscription.
Click Azure Active Directory and select the same Azure AD in which you registered the NAV or Business Central application.
Click App Registrations and then click on the application that represents the Dynamics NAV or Business Central installation (configured during step 1 of the prerequisites above)
Click Expose an API and take note of the Application ID URI. This will be referred to as the Server Application URI. If one is not set, please select 'Set' to get the URI.

Configure the Server Application ID in the Microsoft Dynamics NAV or Business Central Instance Settings

Open the Dynamics NAV Administration or Business Central Administration console and select the instance for configuration in the left pane. Note that the instance should already be configured with AccessControlService or NAVUserPassword as the credential type as configured during step 1 of the prerequisites above. Click Edit.
Insert the Server Application URI noted in the previous section into the Azure Active Directory App ID URI setting. This can be found in the Azure Active Directory (Azure AD) pane of the Administration console. Click Save.
The instance will need to be restarted for the new setting to take effect.

Register the Jet Excel add-in as a client application in Azure Active Directory

Go to http://portal.azure.com and sign in with the credentials for your Azure subscription.
Click Azure Active Directory and select the same Active Directory in which you registered the NAV or Business Central application.
Click App registrations and then click + Add at the top of the page.
Enter the name of the application, for example as Jet Reports, and press Register.
On the Overview page, select Expose an API and choose Set on the following page. Click Save.
Select API permissions. Click Add a permission and select My APIs at the top of the window. Choose the NAV or Business Central application (for which the scope was exposed during Step 3 of the prerequisites), tick the permission and Click on Add permissions.
With the permission added, now select Grant admin consent for the domain and the status will be updated accordingly.
On the Overview page, Click on Authentication. Under Allow public client flows choose Yes. Click Save.

Note that you will need to add a Redirect URI if enabling it for Multi-Factor Authentication.
Take note of the Application (client) ID and the Application ID URI from the Overview page. You will use them in the next section when configuring the Jet Excel add-in.

Configure the Data Source in the Jet Excel Add-In

Open Microsoft Excel and click on the Jet ribbon.
Select Data Source Settings from the Settings are of the Jet ribbon and choose the Dynamics NAV 2013 - 2018 (Web Service) or Dynamics 365 Business Central (Web Services) On Premises data source that has already been configured.
Click the Authentication tab on the left side and change the drop-down to Office 365 authentication.
Provide the appropriate settings as described below:
Enter the valid Office 365 credentials. These credentials must match those that have been configured within Dynamics NAV or Business Central On premises during the prerequisite steps.
Once the credentials are accepted, the window will close automatically.
Click Test Connection in the Data Source Settings window ribbon to verify that the credentials supplied can successfully access the data source.

Enabling Multi-Factor Authentication

Under the Authentication tab of the client application, please select Add a platform
Select Mobile and desktop application
From there, please select https://login.live.com/oauth20_desktop.srf (LiveSDK). Select Save
With that URL selected, select Save