This article applies to using the Jet Reports Excel Add-in (2017 or higher) with Dynamics NAV 2015 and higher
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 installation using OAuth. Once an instance of Dynamics NAV 2015 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.
The following instructions assumes these changes have already been made successfully:
- The Azure Active Directory tenant governing the Office 365 subscription has been integrated with an existing Windows Azure subscription, as explained in the answer to the following TechNet question: http://social.technet.microsoft.com/Forums/windowsazure/en-US/fbf0d2f9-cb40-429e-bc5e-3116399e5250/how-to-login-to-windows-azure-subscription-using-office-365-account?forum=windowsazureaditpro
- The Dynamics NAV 2015 installation has already been configured for Office 365 single sign-on, as explained in this video: http://msdn.microsoft.com/en-us/library/dn474204.aspx. To verify that the correct steps have been taken to configure Dynamics NAV with single sign-on, please refer to the following post written by the Dynamics NAV Team: http://blogs.msdn.com/b/nav/archive/2013/12/19/validating-single-sign-on-with-office-365-and-nav-2013-r2.aspx
- Add a new Data Source
Several steps are required in order to fully complete the Office 365 authentication configuration.
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 then click the Active Directory that governs the organization’s Office 365 credentials.
- Click App Registrations and then click on the application that represents the Dynamics NAV installation (configured during step 2 of the prerequisites above).
- Click Properties and take note of the App ID URI. This will be referred to as the Server Application URI.
Configure the Server Application ID in the Microsoft Dynamics NAV Instance Settings
- Open the Dynamics NAV Administration console and select the instance for configuration in the left pane.
- Click Edit.
- Set the Credential Type setting to NavUserPassword.
- Insert the Server Application URI noted in the previous section into the Azure Active Directory App ID URI setting.
- Click Save.
Register the Jet Excel add-in as a client application in Azure Active Directory
- Go to http://portal.windowsazure.com and sign in with the credentials for your Azure subscription.
- Click Azure Active Directory and click the Active Directory that governs the organization’s Office 365 credentials.
- Click App registrations and then click + Add at the top of the page.
- Enter the name of the application as Jet Reports, choose Native client application, and enter http://JetReports2015 as the Redirect URI and click the Create button at the bottom of the page.
- Select the Jet Reports application you just created and go to Required Permissions menu option.
- Click + Add at the top of the page and search for NAV. Select your Dynamics NAV Web Client application from the list of applications and click Select at the bottom of the page. Click 2 Select permissions, check the box for your Dynamics NAV Web Client, and click Select at the bottom of the page.
- Click Done.
- Take note of the Client ID from the Properties section and the Redirect URI” from the Redirect URIs section of the app. You will use them in the next section when configuring the Jet Excel add-in.
Configuring the Dynamics NAV 2013 and later (Web Service) Data Source in the Jet Excel add-in
- Open Microsoft Excel and click on the Jet ribbon.
- Click on Data Source Settings and choose the Dynamics NAV 2013 and later (Web Service) 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:
- Azure Active Directory Tenant
This value is the identifier of the Azure Active Directory tenant that exists as a result of step one of the prerequisites above. This can either be referenced by identifier (GUID) or domain. All Active Directory tenants are given a default domain of “DOMAIN.ONMICROSOFT.COM”. This value can be used here.
- Client application ID
The client application ID is a value assigned to the registered client application when it is configured in Azure. This should be in a GUID format.
- Client application URI
This URI is a “logical” URI, meaning it is used for identification purposes only, though it should be in proper URI format (“http://UriHere”). When registering the Jet Excel add-in as a client application within Azure Active Directory, this value is created. See the previous section for details. This is labeled as the Redirect URI in Azure Active Directory.
- Server application URI
This value represents the Dynamics NAV installation that was registered within Azure Active Directory. Usually, this URI is the address of the Dynamics NAV Web Client. Within Azure, this value is called the “Sign-On URL” of the server application.