Forum Discussion
Report couldn't access data source in Direct Query mode
I've created a simple report with PBI desktop. In desktop all works fine.
When I open the report in the service i get the following error:
Report couldn't access the data source because it doesn't have persmissions.
The gateway is online and when I go to the data sets it refreshes every 15 minutes.
Where do i set the persmissions?
I connect to a sql server 2016 , with basic authentiction.
Anonymous I would assume that the credentials can access the SQL Server, otherwise you wouldn't be able to use the datasource. My thought is that while it can reach the SQL Server, it can't access all the tables/views that it needs to in order to produce the report results... what level of permission does it have on the SQL Server? Is it a different account, or your credentials?
Alternatively, did you use a basic auth connection in the Desktop?
21 Replies
just go and uncheck 'SSO sign in via direct query with kubernetes' option in 'data sources' in 'manage gateways and connections' . if it doesn't work also uncheck 'skip test connection' and save.
This is due to you have checked "Use SSO via Kerberos for DirectQuery queries" & "Use SSO via Kerberos for DirectQuery And Import queries".
Go to the bottom of "Data Source Setting" in GATEWAY CLUSTERS.
In Advanced Settings unchecked "Use SSO via Kerberos for DirectQuery queries" & "Use SSO via Kerberos for DirectQuery And Import queries".
Apply changes.
Now refresh your report.
Cheers
Hi All,
Recently I installed On-premises data gateway (Standard Mode) on one of the Server Machine and configured it.
When I try to open a report has a direct query on Power BI Web, I was getting error message
"This repoer couldn't access the data source because it doesn't have permission. Choose another data gateway, or contact the gateway administrator"
To Solve this, I followed following steps:
- Connect to Power BI Service
- Settings--> Manage gateways and you will see your configured gateway
- Find the Data Source configured under this gateway and having an issue
- Double click on Data Source and you will see Data Source Settings
- At the end, there is an option "Advance settings". Expand this and uncheck option--> "Use SSO via kerberos for direct queries". Default It is checked. It says "This will only be applied for DirectQuery. Imports will use the Username and password specified in the source details Learn more: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem#credentials"
- Click on Apply and It started working for me.
Hi All,
Recently I installed On-premises data gateway (Standard Mode) on one of the Server Machine and configured it.
When I try to open a report has a direct query on Power BI Web, I was getting error message
"This repoer couldn't access the data source because it doesn't have permission. Choose another data gateway, or contact the gateway administrator"
To Solve this, I followed following steps:
- Connect to Power BI Service
- Settings--> Manage gateways and you will see your configured gateway
- Find the Data Source configured under this gateway and having an issue
- Double click on Data Source and you will see Data Source Settings
- At the end, there is an option "Advance settings". Expand this and uncheck option--> "Use SSO via kerberos for direct queries". Default It is checked. It says "This will only be applied for DirectQuery. Imports will use the Username and password specified in the source details Learn more: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem#credentials"
- Click on Apply and It started working for me.
Anonymous I would assume that the credentials can access the SQL Server, otherwise you wouldn't be able to use the datasource. My thought is that while it can reach the SQL Server, it can't access all the tables/views that it needs to in order to produce the report results... what level of permission does it have on the SQL Server? Is it a different account, or your credentials?
Alternatively, did you use a basic auth connection in the Desktop?
Hi Anonymous,
Are you sure you use the same credential in gateway and in desktop file to connect to data source? Could you log in SQl Server in SSMS with this credential and view all wanted tables and columns?
Regards,
Yuliana Gu
Hi, I got the same issue. I have resolved it by click on Edit Query of the table you got from direct query. Then chose Recent Sources from the tab >> choose your database again on the left >> click ok on the table >> Choose import in Connection settings (this will create a copy into power bi from your direct query). A new Query will be created as a copy of your direct query table. It's will be linked and updated when data in your database changed. You can create visualizations from that query. Hope this help!
I've created the same test on my own pc with a local sql server and published it. And this works fine.
Your suggestions about the credentials are probably right. Now its up to me to figure this one out.
At first I published from Desktop using the import sql feature, later I used (with a new file) the direct query option. Both using the same gateway. Can this somehow be the root of the problem?
Thanks for the help. I´ll keep this thread open for a little longer until I got it working on premise
Anonymous,
Did you map user from PBI service to Windows account user here?
mapping
