How to Connect to Dynamics Navision Server From Power BI Desktop

[monaraya]

A few of our customers reached out to us for a Dynamics Navision connection from Power BI Desktop, and we successfully implemented a secure connection to NAV, following the process below, which I thought would be good to share with other users as well, who have a requirement to connect to the NAV server.

Generally, when we set up Microsoft Dynamics NAV, the authentication method by default is ‘Windows’.

Windows Authentication: This method allows you to connect to NAV as the current Windows user.

But this method would add a few restrictions, like only machines in same network as the server and joined to the domain can access the NAV server, otherwise users will see an error on the connection from PBI Desktop.

So, we suggested a connection using a “NavUserPassword” auth and OData connector from PBI Desktop.
NavUserPassword Authentication in Navision: Users are authenticated using their username and password instead of Windows authentication.

Prepare your Nav environment: 

1. Install a trusted SSL certificate in the NAV server
2. Open the mmc.exe from the Run window
3. Go to the File menu, and then choose Add/Remove Snap-in
4. Select the Certificate and clicked on Add
5. Select “Computer account”
6. Clicked on Finish and then OK
7. Expand the certificate folder-> Personal
8. Click on certificate folder then select imported certificate
9. Right Click on Certificate
10. From All Tasks Select Manage Private Keys
11. Add NETWORK SERVICE or Service account permission to the certificate

12. Copy the Certificate from Personal / Certificate folder

13. Paste the certificate into the Trusted Root Certification Authorities/Certificates folder, Enterprise trust, Trusted publishers, and Trusted people.

14. Copy the certificate Thumbprint and remove all the spaces and keep it safe, need to enter this while configuring a Nav instance.

Configure Navision server:

1. Open Dynamics NAV RTC client

2. Navigate to your company, for example: CRONUS International Ltd./Departments/Administration/IT Administration/General/Users  and create a new user.

e.g. Username: CHRIS_USERPASS and click on Password and set an appropriate password with a combination of one uppercase alphabet, one number and special character.

3. Select Permission to SUPER under User Permission Set tab

4. Create Web Service Access Key (not mandatory)

5. Open the Microsoft Dynamics NAV 2016 Administration

6. In the DynamicsNAV instance, insert the Certificate Thumbprint, and don’t forget to change the Credential Type to NAVUserPassword.

7. Enabled SSL, OData service and copy the OData Base URL and keep it safe for connecting later on.

Check connection from PBI Desktop:

1. Open the Power BI Desktop app
2. Select the OData Feed connector
   
3. Use your OData service URL for the PowerBI connectivity, in the following format:
   
4. In the next step, choose basic authentication and provide the Username and Web Service access Key as a Password that we created in the above step:
   
5. And connect!

Author: Pallavi Pandey

Reviewer: Mounika Narayana Reddy