cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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. 

ErrorAccesDenied.pngimage.png

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.

 

 

 

1 ACCEPTED 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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

19 REPLIES 19
arvindkjangid
New Member

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

Anonymous
Not applicable

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:

  1. Connect to Power BI Service
  2. Settings--> Manage gateways and you will see your configured gateway
  3. Find the Data Source configured under this gateway and having an issue
  4. Double click on Data Source and you will see Data Source Settings
  5. 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"
  6. Click on Apply and It started working for me. 
Anonymous
Not applicable

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:

  1. Connect to Power BI Service
  2. Settings--> Manage gateways and you will see your configured gateway
  3. Find the Data Source configured under this gateway and having an issue
  4. Double click on Data Source and you will see Data Source Settings
  5. 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"
  6. Click on Apply and It started working for me.
Anonymous
Not applicable

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?

 

mappingmapping

Anonymous
Not applicable

No it had to do with the credentials in the gateway. Problem solved already. Thanks for the reply

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

This was not the problem. I deleted the data source under the gateway and added it again. Now it works 

 

v-yulgu-msft
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

 

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Eno, Thanks for the reply. 

Clearly I'm missing something, but to me the gateway credentials seem to be allright. 

 

image.png

 

image.png

 

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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

image.png

 

Is this the correct setting in sql server (2016)

Anonymous
Not applicable

No this is the first one. Can you give me a hint on how to do this? Or Check it?

DirectQuery access via service is handled different than connecting via powerbi desktop? 

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors