Forum Discussion
Invalid connection credentials for Gateway when querying cross database views
I'm going to guess this isn't so much an error but a limitation of the Gateway or DirectQuery itself.
I have an on-prem gateway installed and configured. There are no connection issues and I'm able to view reports via the DirectQuery in BI Service. These reports are generated from a number of tables in DatabaseOne. This is the same database the gateway has been configured for.
Inside DatabaseOne I have a SQL View that queries two tables from DatabaseTwo. There are no issues when building and viewing reports in the Power BI Desktop tool using this SQL View. However, when I publish the reports to the BI Service:
- All tiles display the "Can't display visual" error/placeholder
- A message is displayed at the top of the report page that says "Invalid Connection Credentials". The details do not provide any insight into the root issue.
What I have done:
- I have verified that the user account associated with the gateway service has access to both DatabaseOne and DatabaseTwo
- If I create a SQL View that queries on a tables only in DatabaseOne there are no isssues
- Reviewed documentation and message board posts for insight on this behavior
Any thoughts?
3 Replies
- GilbertQ
Super User
Hi there
Currently there is a limitation for DirectQuery where it can only get data from One Source, if you scroll down you will see the limitation.
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-aboutThank you for the response and linked article.
I think the issue I'm having, and I'll send this as feedback to the team, is in the handling of this behavior/limitation in the UI.
- The Power BI Desktop tool has no issue with a DirectQuery connection against the View that pulls data from multiple sources. I would have expected to see a warning or error message at this step.
- The issue only occurs when the report is published to BI Service. Is it a limitation of DirectQuery, the Gateway or both? If it works inside the BI Desktop tool and doesn't when published to BI Service then the only difference is the Gateway connection back to my on-prem database.
- The error displayed by Power BI Service is completely misleading. The error describes an issue with credentials and nothing about data source conflicts/limitations.
- GilbertQThat is true that the message can be misleading and I would imagine that it possibly might be better written. With that being said I am fairly certain it would be due to going across to a different database?
