Office 365 integration in a Dynamics NAV hosting environment

We from the Microsoft Dynamics NAV support team (Microsoft CSS) see an increasing number of support cases coming in related to Office 365 integration in a hosting environment. This range from the relatively new Edit in Excel feature or the relatively new Outlook Business Inbox to the more familiar Office 365 Single Sign On functionality. This blog post is intended to help you to first discuss the requirements with your customer, it may also help you to understand the difficulties. It should be relatively easy to implement these capabiltities, but since there are so many external components that require different configuration setup, we may end up in high labor intense support cases where we need to put all pieces together. This blog post will hopefully serve as a guide where we summarize all relevant documentation that is out there. Finally, we will showcase a typical environment with several Azure AD’s among some other typical scenarios.

Before you get started

To visualize what kind of components you do need to think about before starting the implementation, take a look at the following picture:

Edit in Excel

If you do now the basics, you are half way through to finalize the setup successfully. Let’s therefore look at the system requirements and the description of what the Edit in Excel functionality can do for you. All relevant documentation can be found on this blog. E.g. system requirements and the documentation about the functionality and setup.

SSL Cert

Yes, you can use a self-signed certificate. If you search for it on the Internet, there are many ways of creating your own cert. Fortunately, there is a way that avoids using difficult parameters in combination with deprecated tools like makecert.exe.
If you get into trouble with using / configuring a self-signed certificate, it will be your first request for help to either Microsoft CSS or to your Dynamics NAV partner. Trusted certs are recommended. Trusted certs avoid that you must ship the SSL cert to every single device and install them manually in the trusted root store.
Are we done talking about SSL certs? Nope, the 2014 blog post talks about the following:

“CN=<Your site name>”

This can be something like CN=nav.microsoft.com or CN=navvm.westeurope.cloudapp.azure.com. Trusted SSL certs can be ridiculously cheap, but the Dynamics NAV support team suggest that youbuy an expensive wildcard SSL cert. Yes, a wildcard cert is much more expensive, but it does make your life easier as you can use it for any device, computer or SSL proxy that is before the NST server, etc. Then it would be something like:

“CN=*.yourdomain.com”

Credential type

You do also need to decide about credential types. For example, AccessControlService or NavUserPassword to name a few. As you may have heard, Azure team no longer support AccessControlService. As of now, the Dynamics NAV development teams are analyzing the impact of that decision of the Azure team. At present, we do not know what and if there is an impact for us.

Azure Active Directory

For instance, there may be three scenarios to think about:

1. Your company hosts several Azure AD’s
2. Your company consists of several branch offices, locations
3. Your company consists of only one tenant, one database, etc.

The first two scenarios are typical multitenant scenarios. Each single scenario can have a different business requirement. If you do not have an Azure AD, you can logon to the Azure page on the microsoft.com site and you can get a free subscription or a paid subscription. For what we need, which is Single Sign On or SSO, the following does apply:

Just sign on and off you go.

OData endpoint

OData endpoint must be reachable for the Edit in Excel functionality to work. This does mean you need to think about security, firewalls, ports assignment and endpoints if running in a cloud environment. Also, you do need to think or make a decision if the Edit in Excel should be reachable from outside your corporate network, etc.

App Registrations

Though not exceedingly difficult to configure, our documentation lists that you need to have access to Azure AD. We are currently seeing a high incoming support call volume related to the relatively new Edit in Excel functionality that was first shipped in Dynamics NAV 2017. These support request are considered high labor intense support requests where we do require the partner or customer to test a lot themselves to find root cause of the issues they do encounter. We do also have to ask these unfortunate partners or customers to reconfigure their environment to get things to work. Not all Dynamics NAV partners are able or allowed to access customer Azure AD’s which does make partners life not easier but more difficult. It is something to discuss with your customer proactively, that the Dynamics NAV consultant does require access or does need someone to help him creating the Apps on the Azure AD.

Outlook Business Inbox

As with the Excel Add-In, let’s have a look at the system requirements first. E.g. system requirements can be found here and a high level summary of what it can do for you is written below:

Dynamics NAV includes the following add-ins for Outlook:

Contact Insights:
This add-in provides users with Dynamics NAV customer or vendor information in Outlook emails and calendar appointments. It also enables users to create and send Dynamics NAV business documents, such a sales quotes and invoices to a contact. To support these task, the add-in adds actions to the Outlook ribbon, in the Dynamics NAV group.

Document View:
When a business document is sent as an email, this add-in provides a direct link from email to the actual business document in Dynamics NAV. The add-in adds a Document Links action in the email header, which a user can select to display the document.

We’ve learnt from incoming support requests that the PublicWebBaseUrl is not filled in correctly all the time. We’ve also seen some support cases where a highly secured environment could not access all required components that are stored on the Internet needed for the Outlook Business Inbox. A Fiddler trace did show where the environment was too secured and what should be opened so that the Business Inbox could work. In some scenarios we saw that a self-signed certificate limited the full functionality of the Outlook Business Inbox. These are things to consider when deciding what type of SSL cert, you are going to buy.

Office 365

One of the other requirements is that you have a supported release of Excel for the Edit in Excel functionality to work, this can be cloud based or OnPrem (desktop) release. Note that some of these Office 365 subscriptions also have support for Azure AD. The one in the link is an E3 subscription plan (trial) which does also include the Azure AD. With an Office 365 E3 subscription plan, there is no need to also get an Azure AD subscription as well. In the below scenario description, I will be using my MSDN subscription (mmels.onmicrosoft.com) for the Azure AD only and I will be using a separate Office 365 subscription (melsbergmansltd.onmicrosoft.com) for the second Azure AD needed for the authentication of the users. In the hosting scenario, I do need two Azure AD’s to demonstrate. Complicated? No, not really but it should be part of your discussions of requirements you will have to go through with your customer.

Multitenant or single tenant

There may be customers out there that do only require a single tenant environment. This is fine, of course. It has been like this for years. There may also be customers out there that do really require multi-tenant environments. Every single customer still have their own business requirements. These different requirements do require different setup. They do all want this functionality to work with the least administrative work.

Showcasing a typical hosting scenario with multiple Azure AD’s

Let’s run some PowerShell commands that do configure everything from scratch. To summarize the environments again:

  1. Your company hosts several Azure AD’s
  2. Your company consists of several branch offices, locations
  3. Your company consists of only one tenant, one database, etc.

Let’s discuss the first scenario to start with.

Your company hosts several Azure AD’s

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Force

Import-Module 'C:\Program Files\Microsoft Dynamics NAV\110\Service\NavAdminTool.ps1' –
 Import-Module 'C:\NAV\WindowsPowerShellScripts\Multitenancy\NAVMultitenancySamples.psm1'

Force

$UserName = "XXX"
 $sec_password = "XXX" | ConvertTo-SecureString -AsPlainText -Force
 $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $sec_password
 $ThumbPrint = 'XXX'

New-NAVServerInstance -ManagementServicesPort 11045 -ClientServicesPort 11046 -SOAPServicesPort 11047 -ODataServicesPort 11048 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -DatabaseName COMMONMT -ClientServicesCredentialType NavUserPassword -ServiceAccount User -ServiceAccountCredential $Credentials -ServerInstance COMMONMT -ServicesCertificateThumbprint $ThumbPrint

Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName PublicODataBaseUrl -KeyValue 'https://msftzbook15g3.europe.corp.microsoft.com:11048/COMMONMT/OData/'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName PublicSOAPBaseUrl -KeyValue 'https://msftzbook15g3.europe.corp.microsoft.com:11047/COMMONMT/WS/'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName PublicWebBaseUrl -KeyValue 'https://msftzbook15g3.europe.corp.microsoft.com/COMMONMT/'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName PublicWinBaseUrl -KeyValue 'DynamicsNAV://msftzbook15g3.europe.corp.microsoft.com:11046/COMMONMT/'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName ServicesDefaultCompany -KeyValue 'CRONUS International Ltd.'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName ServicesDefaultTimeZone -KeyValue 'UTC'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName SOAPServicesSSLEnabled -KeyValue 'True'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName ODataServicesSSLEnabled -KeyValue 'True'
 Set-NAVServerConfiguration -ServerInstance COMMONMT -KeyName ServicesCertificateValidationEnabled -KeyValue 'False'

NOTE: In in Dynamics NAV 2018, PublicWebBaseUrl ‘\WebClient\’ was deprecated. In Dynamics NAV 2017, it is still in use.

Set-NAVServerInstance -ServerInstance COMMONMT -Force -Start

New-NAVServerUser -Tenant default -ServerInstance COMMONMT -WindowsAccount XXX -FullName 'Marco Mels' -LicenseType Full -Password $sec_password -AuthenticationEmail XXX@mmels.onmicrosoft.com -ContactEmail XXX@mmels.onmicrosoft.com

New-NAVServerUserPermissionSet -ServerInstance COMMONMT -PermissionSetID SUPER -WindowsAccount EUROPE\XXX

#Create branches
 Copy-NavCompany -Tenant default -SourceCompanyName 'CRONUS International Ltd.' -DestinationCompanyName 'Melsbergmans Ltd.' -ServerInstance COMMONMT -Force
 Copy-NavCompany -Tenant default -SourceCompanyName 'CRONUS International Ltd.' -DestinationCompanyName 'Mels Ltd.' -ServerInstance COMMONMT -Force

Export-NAVApplication –DatabaseServer MSFTZBOOK15G3 –DatabaseInstance NAVDEMO –DatabaseName COMMONMT –DestinationDatabaseName Corp -Force
 Remove-NAVApplication –DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO –DatabaseName COMMONMT -Force
 Set-NAVServerInstance –ServerInstance COMMONMT -stop -Force
 Set-NAVServerConfiguration –ServerInstance COMMONMT –element appSettings –KeyName ‘DatabaseName’ –KeyValue ‘’ -Force
 Set-NAVServerInstance –ServerInstance COMMONMT -Start -Force
 Mount-NAVApplication –ServerInstance COMMONMT –DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO –DatabaseName Corp -Force
 Mount-NAVTenant –ServerInstance COMMONMT -Id tenant –DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -DatabaseName COMMONMT -OverwriteTenantIdInDatabase -Force
 Sync-NAVTenant -ServerInstance COMMONMT -Tenant tenant -Mode Sync -Force
 HowTo-MoveCompanyToTenant -ServerInstance COMMONMT –FromDatabase COMMONMT -OldTenantName tenant -NewTenantName branch1 -CompanyName 'CRONUS International Ltd.' -ToDatabase branch1 -DatabaseServer 'MSFTZBOOK15G3\NAVDEMO' -RemoveCompanyWhenMoved -Force
 HowTo-MoveCompanyToTenant -ServerInstance COMMONMT –FromDatabase COMMONMT -OldTenantName tenant -NewTenantName branch2 -CompanyName 'Mels Ltd.' -ToDatabase branch2 -DatabaseServer 'MSFTZBOOK15G3\NAVDEMO' -RemoveCompanyWhenMoved -Force
 HowTo-MoveCompanyToTenant -ServerInstance COMMONMT –FromDatabase COMMONMT -OldTenantName tenant -NewTenantName branch3 -CompanyName 'Melsbergmans Ltd.' -ToDatabase branch3 -DatabaseServer 'MSFTZBOOK15G3\NAVDEMO' -RemoveCompanyWhenMoved -Force
 Dismount-NAVTenant -Tenant Tenant -ServerInstance COMMONMT -Force

New-NAVServerUser -Tenant branch1 -ServerInstance COMMONMT -UserName Admin -FullName 'Administrator' -LicenseType Full -Password $sec_password -AuthenticationEmail XXX@mmels.onmicrosoft.com -ContactEmail XXX@mmels.onmicrosoft.com
 New-NAVServerUser -Tenant branch2 -ServerInstance COMMONMT -UserName Admin -FullName 'Administrator' -LicenseType Full -Password $sec_password -AuthenticationEmail XXX@mmels.onmicrosoft.com -ContactEmail XXX@mmels.onmicrosoft.com
 New-NAVServerUser -Tenant branch3 -ServerInstance COMMONMT -UserName Admin -FullName 'Administrator' -LicenseType Full -Password $sec_password -AuthenticationEmail XXX@melsbergmansltd.onmicrosoft.com -ContactEmail XXX@melsbergmansltd.onmicrosoft.com

New-NAVServerUserPermissionSet -Tenant branch1 -ServerInstance COMMONMT -PermissionSetID SUPER -UserName Admin
 New-NAVServerUserPermissionSet -Tenant branch2 -ServerInstance COMMONMT -PermissionSetID SUPER -UserName Admin
 New-NAVServerUserPermissionSet -Tenant branch3 -ServerInstance COMMONMT -PermissionSetID SUPER -UserName Admin

Result of these PowerShell commands:

Let’s now make one of these tenants available for the other Azure AD:

 Dismount-NAVTenant -Tenant branch1 -ServerInstance COMMONMT -Force
 Dismount-NAVTenant -Tenant branch2 -ServerInstance COMMONMT -Force
 Dismount-NAVTenant -Tenant branch3 -ServerInstance COMMONMT -Force

The following three commands may be necessary but only in Dynamics NAV 2018:

Mount-NAVTenantDatabase -ServerInstance COMMONMT -Id branch1 -DatabaseName branch1 -DatabaseServer MSFTZBOOK15G3\NAVDEMMount-NAVTenantDatabase -ServerInstance COMMONMT -Id branch2 -DatabaseName branch2 -DatabaseServer MSFTZBOOK15G3\NAVDEMO
Mount-NAVTenantDatabase -ServerInstance COMMONMT -Id branch3 -DatabaseName branch3 -DatabaseServer MSFTZBOOK15G3\NAVDEMO

Now we set the Azure AD:

Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch1 -DatabaseName branch1 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -AadTenantId mmels.onmicrosoft.com -DefaultCompany 'CRONUS International Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -AllowAppDatabaseWrite -NasServicesEnabled -RunNasWithAdminRights
Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch2 -DatabaseName branch2 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -AadTenantId mmels.onmicrosoft.com -DefaultCompany 'Mels Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -NasServicesEnabled -RunNasWithAdminRights
Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch3 -DatabaseName branch3 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -AadTenantId melsbergmansltd.onmicrosoft.com -DefaultCompany 'Melsbergmans Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -NasServicesEnabled -RunNasWithAdminRights

Results of these PowerShell commands:

If we want to log on to the Windows Client to any of these tenants, we will have to configure the ClientUserSettings.config file. The one being used by default can be found here:

%USERPROFILE%\AppData\Roaming\Microsoft\Microsoft Dynamics NAV\110


Result of this:

  1. There is a Windows User that can logon to all tenants.
  2. There is a User in Azure AD mmels.onmicrosoft.com that can logon to branch2 only
  3. There is a User in Azure AD melsbergmans.onmicrosoft.com that can logon to branch3 only

Now let’s continue with the WebClient configuration:

New-NavWebServerInstance -ServerInstance COMMONMT -ClientServicesCredentialType AccessControlService -ClientServicesPort 11046 -Server MSFTZBOOK15G3 -WebServerInstance COMMONMT -DnsIdentity '*.europe.corp.microsoft.com' -CertificateThumbprint $ThumbPrint

NOTE: Azure team no longer supports AccessControl service. At the moment of writing this blog, we do not know what this does mean for our own O365 SSO configuration.

We should also set the Management Port.

Set-NAVWebServerInstanceConfiguration -WebServerInstance COMMONMT -KeyName 'ManagementServicesPort' -KeyValue '11045'

If we now want to logon to the WebClient, we will receive an expected error:

It is now time to logon to the Azure portal. In the Azure portal, on the left-hand side, we click on More services and then type in App registration. Click on New Application registration. Enter the following details:

Name: Dynamics NAV 2018 | Azure AD App
Application Type: Web app / API
Sign-on URL: https://msftzbook15g3.europe.corp.microsoft.com/COMMONMT/

Press Create and then select the Dynamics NAV 2018 | Azure AD App to display the details of it. Copy the value: Application ID value and paste it into Notepad. Now click Properties in the right-hand side. Copy the App ID URI value and paste it in notepad. Ensure that Multi-tenanted is set to Yes. Press Save to save any changes you did make. Now click on Reply URL’s.

If you somehow run into troubles, before filing a Support ticket, please generate a copy of the manifest file as a Microsoft support engineer will ask for it. We may also ask you for the customsettings.config file and application event log file. Of course, the relevant web.config file or navsettings.json file should also be sent to the Microsoft support engineer. We can then directly look through the several configuration setup details.
Now open the customsettings.config file. If the NST instance is not the default one, you will find it in the following folder:

%PROGRAMFILES%\Microsoft Dynamics NAV\110\Service\Instances\COMMONMT

In the customsettings.config file, locate the following properties:

<add key="ClientServicesFederationMetadataLocation" value="https://login.windows.net/common/FederationMetaData/2007-06/FederationMetadata.xml" />
<add key="AppIdUri" value="https://mmels.onmicrosoft.com/e2f578a6-dba4-4f7c-8145-0af60117e016" />
<add key="WSFederationLoginEndpoint" value="https://login.windows.net/common/wsfed?wa=wsignin1.0%26wtrealm=https://mmels.onmicrosoft.com/e2f578a6-dba4-4f7c-8145-0af60117e016%26wtreply=https://msftzbook15g3.europe.corp.microsoft.com/COMMONMT/" />
<add key="AzureActiveDirectoryClientId" value="b825d67b-88c4-411a-b89c-b1dae2e41fbe" />

Last but not least, we will have to restart the instance.

Set-NAVServerInstance -ServerInstance COMMONMT -Force -Restart

We should now be able to start the WebClient with O365 SSO after running an IISRESET (some settings may be cached).
URLs being used:

  1. https://msftzbook15g3.europe.corp.microsoft.com/commonmt/?tenant=branch1
  2. https://msftzbook15g3.europe.corp.microsoft.com/commonmt/?tenant=branch2
  3. https://msftzbook15g3.europe.corp.microsoft.com/commonmt/?tenant=branch3

Only if we try to logon with the created User Name (not the Windows User) to the branch3 tenant, something happens:

After accepting, we are able to logon.

Your company consists of several branch offices, locations

The above created example configuration is similar to scenario 2 with some adjustments:

 
Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch1 -DatabaseName branch1 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -DefaultCompany 'CRONUS International Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -AllowAppDatabaseWrite -NasServicesEnabled -RunNasWithAdminRights
 Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch2 -DatabaseName branch2 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -DefaultCompany 'Mels Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -NasServicesEnabled -RunNasWithAdminRights
 Mount-NAVTenant -ServerInstance COMMONMT -Tenant branch3 -DatabaseName branch3 -DatabaseServer MSFTZBOOK15G3 -DatabaseInstance NAVDEMO -DefaultCompany 'Melsbergmans Ltd.' -DefaultTimeZone UTC -EnvironmentType Production -NasServicesEnabled -RunNasWithAdminRights
 New-NAVServerUser -Tenant branch3 -ServerInstance COMMONMT -UserName Admin -FullName 'Administrator' -LicenseType Full -Password $sec_password -AuthenticationEmail XXX@mels.onmicrosoft.com -ContactEmail XXX@mmels.onmicrosoft.com

In the customsettings.config file, locate the following properties:

<add key="ClientServicesFederationMetadataLocation" value="https://login.windows.net/mmels.onmicrosoft.com/FederationMetaData/2007-06/FederationMetadata.xml" />
<add key="AppIdUri" value="https://mmels.onmicrosoft.com/e2f578a6-dba4-4f7c-8145-0af60117e016" />
><add key="WSFederationLoginEndpoint" value="https://login.windows.net/mmels.onmicrosoft.com/wsfed?wa=wsignin1.0%26wtrealm=https://mmels.onmicrosoft.com/e2f578a6-dba4-4f7c-8145-0af60117e016%26wtreply=https://msftzbook15g3.europe.corp.microsoft.com/COMMONMT/" />
<add key="AzureActiveDirectoryClientId" value="b825d67b-88c4-411a-b89c-b1dae2e41fbe" />

The App Registration Dynamics NAV 2018 | Azure AD App can be adjusted so that property Multi-tenanted is set to No.

In the described environment setup, we can now only logon with users that are defined in Dynamics NAV and only belong to tenant mmels.onmicrosoft.com.

Your company consists of only one tenant, one database, etc.

Here scenario 2 does apply with some adjustments. There is no need to create users in branch2 and branch3 (simply because they do not exist as this is a single tenant environment). We also do not have to run the exercise to create a multitenant environment. We can however still use the created application that did apply to scenario 2.

If you like this blog posting that does come out of Microsoft Dynamics CSS, please consider liking this blog posting and / or add some comments how we can improve. In the comments you can also drop any questions you may still have and or suggest topics for other blog postings.

Regards,

Marco Mels

Microsoft CSS EMEA

This posting is provided “AS IS” with no warranties, and confers no rights.