Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

21 REPLIES 21
navinrangar
Advocate I
Advocate I

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.

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
Employee
Employee

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?

 

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.


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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors