Forum Discussion
Unable to connect to NAV using ODAta
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.
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?
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=Tenant1
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.
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?