Unable to connect to NAV using ODAta

[Anonymous]

I have NAV 2017 environment with queries created and published. Unfortunatelly I'm not able to connect to those with Power BI. I am using basic access with username and web access key.

I can:

• access queries via browser (ie + edge )
• access queries via Excel

But Power BI gives me "User not authorized error".

Any help would be higly appreciated because I can't seem to figure out what the problem is.

Thank you!

[MadsW]

I am a bit pussled here, because I cannot seem to get this to work. My odata feed when copied from NAV:

https://<serverIP or DNS>:<portNO>/<navINSTANCE>/OData/Company('<companyNAME>')/<webserviceNAME>?tenant=<tenantID>

If I would follow the solution suggestet here I should have this url:

https://<tenantID>.<serverIP>:<portNO>/<navINSTANCE>/OData/Company('<companyNAME>')/<webserviceNAME>

But it seems that I would need some DNS set up, to handle that? I get an error when I try to use that URL. What do I need to do more than set alternate tenant ID to same id as tenant?

[AOS]

@MadsWIndeed you need a DNS record to resolve the Alternate ID name.

First, using IP address as a server name is not practical - if IP address changes, you may have to update the IP address in many places such as config files, PowerBI data sources, etc. I recommend to create a DNS name that would resolve to that IP address - if the IP address changes, you will have to update only single DNS record. So, you never use URL as https://<serverIP>:<portNO>/<navINSTANCE>/, but rather https://<DNS>:<portNO>/<navINSTANCE>/. The record to create is a host (type A) record. Probably you already have it if you address the NAV server by name.

Second, for every tenant you have to create an alias (type CNAME) record to resolve Tenant Alternate ID to the host name.

For example, you have Tenant1 and Tenant2 tenants on a NAV instance named NAVINSTANCE running on server with IP address 1.2.3.4. Your domain is DOMAIN.COM.

1. Host (A) record makes possible URL https://navserver.domain.com:7048/navinstance/OData/Company('CompanyName')/WebServiceName?tenant=Ten...

navserver   A   1.2.3.4

2. Alias (CNAME) records make possible URLs like https://tenant1.domain.com:7048/navinstance/OData/Company('CompanyName')/WebServiceName and like https://tenant2.domain.com:7048/navinstance/OData/Company('CompanyName')/WebServiceName respectively.

tenant1   CNAME   navserver.domain.com.
tenant2   CNAME   navserver.domain.com.

[MadsW]

Hi AOS,

Thank you for clearing that out. We use hostnames today not IP, it was just to keep things simple. 🙂

As far as I can remember the issue with Powerpivot or Powerquery has been present in NAV 2016 also? I can't remember if ever getting multitenant data out to PowerBI.

I will ask hoster to make CName records. It is hosted at Azure so it should be possible I think.

We allready use cname to servername https://servername.navdomain.com, so this would be https://tenantname.servername.navdomain.com right?

[Anonymous]

Appologies for not clarifying right away but it was AOS who solved the issue, so the solution is available in his comment.

[v-haibl-msft]

@Anonymous

If possible, you can share your solution here to help others who have the same issue as you.

Best Regards,
Herbert

[Anonymous]

Problem solved and now it works perfectly.

Thank you so much!

[AOS · Accepted solution]

This occurs with a multi-tenant NAV instance if Alternate ID property is not set up for the tenant. The problem is rather on NAV side, I have reported it to Microsoft already.

Shortly, multi-tenant NAV instance is an architecture with one service (instance) hosting many isolated entities (tenants). An OData URI has to include tenant name as a parameter then, e.g.  https://MyNav.contoso.com:7048/NAV/OData/Company('MyCompany')/MyQuery?tenant=MyTenant. Every request must address proper tenant or else request will fail.

When PowerBI connects to such feed, it actually successfully opens the connection and retrieves a "preview" rowset. The "preview" JSON contains a metadata URI, e.g. https://MyNav.contoso.com:7048/NAV/OData/$metadata. One may notice that ?tenant=MyTenant is missing from the URI. (This is easy to test - try https://MyNav.contoso.com:7048/NAV/OData/$metadata and fail, but if you try https://MyNav.contoso.com:7048/NAV/OData/$metadata?tenant=MyTenant you will receive the metadata.) PowerBI sends GET https://MyNav.contoso.com:7048/NAV/OData/$metadata and response is error 401 "Unauthorized". Once PowerBI failed with the metadata request it will return "User not authorized" error to the user. Excel just ignores metadata request failure and delivers data from the feed.

If an NAV Tenant has Alternate ID option set up, tenant parameter can be ignored because Alternate ID uniquely defines a tenant across multi-tenant environment. In fact Alternate ID is a fully qualified host name  which basically changes default tenant URI https://MyNav.contoso.com:7048/NAV/OData/Company('MyCompany')/MyQuery?tenant=MyTenant to https://MyTenant.contoso.com:7048/NAV/OData/Company('MyCompany')/MyQuery. Metadata URI then becomes https://MyTenant.contoso.com:7048/NAV/OData/$metadata and is valid without tenant parameter.

So, the solution is to use Alternate IDs per tenant in your multi-tenant NAV instance. If use of Alternate IDs is not possible - hope that Microsoft confirms this as a bug and issues a fix for this.

[Anonymous]

Hi,

Do you know maybe, if there is any other way to make it work, without using Alternate ID? I can't use Alternate ID in my case, but I need to connect from PBI Desktop to multi-tenant nav using OData.

Thanks in advance

[v-haibl-msft]

@Anonymous

Could you please provide a fiddler trace to us? You can start the trace before make the OData connection, and share the saved .saz file to us through online file service like OneDrive.

Best Regards,
Herbert