Configure Microsoft 365 Authentication for Dynamics NAV or Business Central On Premises

This article applies to using the Jet 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 Microsoft 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 Microsoft 365 single sign-on, there are only a few small steps required to use the same Microsoft 365 credentials for authentication using the Jet Excel add-in.

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 Microsoft Entra ID. This is explained in the following document for Dynamics NAV or the following document for Business Central.
Enable SOAP Services in the Dynamics NAV Server or Business Central On Premises Server Instance. This is explained in the following document.

Enable SOAP Services in the Dynamics NAV Server

Enable Business Central On Premises Server Instance for Dynamics NAV
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 Microsoft 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 Microsoft Entra ID
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 Microsoft Entra ID 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 URIThis 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.

Starting with the Business Central 2022 release wave 2 (v21), the Business Central Server Administration tool has been discontinued. The Business Central Administration Shell should be used in its place.

Alternatively, open the Business Central Administration Shell with elevated permissions and run the following commands:

Copy

BC Admin Shell Commands

Set-NAVServerConfiguration -ServerInstance $BCInstance -KeyName ValidAudiences -KeyValue "$AADCLIENTID;https://api.businesscentral.dynamics.com"
Set-NAVServerConfiguration -ServerInstance $BCInstance -KeyName ADOpenIdMetadataLocation -KeyValue "https://login.microsoftonline.com/$AADTENANTID/.well-known/openid-configuration"
Set-NAVServerConfiguration -ServerInstance $BCInstance -KeyName AppIdUri -KeyValue "$ApplicationIDURI"
Set-NAVWebServerInstanceConfiguration -WebServerInstance $BCInstance -KeyName AadApplicationId "$AADCLIENTID"
Set-NAVWebServerInstanceConfiguration -WebServerInstance $BCInstance -KeyName AadAuthorityUri -KeyValue "https://login.microsoftonline.com/$AADTENANTID"
Restart-NAVServerInstance -ServerInstance $BCInstance

Provide the Server Application URI noted in the previous section into the Microsoft Entra ID App ID URI setting. This can be found in the Microsoft Entra ID pane of the Administration console.

Provide the required information by replacing the IDs with your own values and click Save.
- Valid Audience: "{AADCLIENTID};https://api.businesscentral.dynamics.com"
- Login endpoint: https://login.microsoftonline.com/{AADTENANTID}
- Metadata Location: https://login.microsoftonline.com/{AADTENANTID}/.well-known/openid-configuration
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 Microsoft Entra ID

Go to http://portal.azure.com and sign in with the Azure credentials.
Click Microsoft Entra ID and select the same Active Directory in which you have registered the NAV or Business Central application.
Click App registrations > + New registration. The +New registration section is displayed.
Enter the name of the application, for example Jet , and click 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), select the permission and Click 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 Authentication. Under Allow public client flows, select Yes.

Note that you do not need to add a Redirect URI. Click Save.
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 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.

In Configure authentication settings for this data source, change the drop-down to Microsoft 365 authentication.

Provide the appropriate settings as described below:
Before clicking Log In, click the Web Service tab and provide the Server, Web Service Port, and Instance Value information.

Go to the Authentication tab and click Log In to get the Azure authentication prompt. Once the credentials are accepted, the window will close automatically.
Click Test Connection in the Data Source Settings window to check if the credentials work.

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

Enable Multi-Factor Authentication

Under the Authentication tab of the client application, select Add a platform > Mobile and desktop application.
In the Mobile and desktop applications section, select the following URLs and select Save.
- https://login.microsoftonline.com/common/oauth2/nativeclient
- https://login.live.com/oauth20_desktop.srf (LifeSDK)