Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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?
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:
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:
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?
No it had to do with the credentials in the gateway. Problem solved already. Thanks for the reply
Hi,
Im facing the same issue.
Could you pls provide me solution in detail.
Power bi desktop - basic auth or windows?
power bi service - ?
gateway connection for datasource is successful.
kindly advice!
Thanks
I don't have a button for this. I can only add or remove users
@Anonymous,
If you configured Gateway but yourself then you should have user under USERS tab (see screenshot). Provide your screenshot.
This was not the problem. I deleted the data source under the gateway and added it again. Now it works
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
Yes i can login with a local user account (windows authentication) and the SA account. I can view the tables.
I tried both in the gateway. No luck there
Can this be due to any restrictions with DirectQuery?
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!
@Anonymous This looks like the credentials you are using in the gateway do not have sufficient permissions to access the database. The local connection (with your creds) will work if you have permissions, but once you deploy, you are using the credentials tied to the datasource in the gateway.
Eno, Thanks for the reply.
Clearly I'm missing something, but to me the gateway credentials seem to be allright.
I'm pretty new to Power Bi and Direct Query. With CSV/Excel files i got (an other) gateway working, but this one is a pain in the ass.
@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?
I tried both, with the same result. any other suggestions?
@Anonymous Are you using the datasource on the gateway for other reports, or is this the first one you are trying to hook up? Has the SQL Server been configured to allow external access?
Is this the correct setting in sql server (2016)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.