PowerBi DirectQuery + SQL Data Connection Issue

[shaht]

I am  working on a Live Dashboard using DirectQuery in PowerBI.

I am using datasource from SQL Server which updates every 15 minutes.

The dashboard is working fine on my PowerBi Desktop, but on PowerBI server, the Report is not being able to connect with the data source.

I have used On-Premise gateway and the data source is connected successfully and being refreshed every 15 minutes but the visual is not able to load the data.

I have attached photos of the error and the data source settings that I have on Desktop and on Gateway. Could you please have a look and advise where could be the connection issue occurring ?

 successful data connection with refreshPBI desktop connectiongateway detailserror-1error-2

[shaht]

To Accompanish this automated 15 minute update, if you have anyother option then please let me know. 

[v-janeyg-msft]

Hi, @shaht 

Has the refresh history failed? Have you ruled out the three possibilities suggested by the error message? Can other people in the organization see the report? Has it been displayed normally in the service before? Did the problem suddenly occur? Does the report have only sql data source?

Check if there will be RLS on the SQL side, Is the gateway up-to-date and functional?

If everything is ok, try to re-publish the pbix file to the service.

Janey

[shaht]

The refresh history is fine as I showd in one of the image. No other people also can not see tis reports. This is the first time we are using Power BI in our organisation. Gateway is working fine on personal mode with local excel files but with SQL is shwoing this issue. Yes report has only 1 datasource from SQL. Gateway is latest version. 

[v-janeyg-msft]

Hi, @shaht 

If everything is normal in the service, Check for permission restrictions in SQL and RLS.

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Janey

Janey

[shaht]

Hi Janey, We tried this morning using different SQL credential vis Basic Method with no restrictions but it did not help. Is it compulsory to use Windows credentials which would match across desktop login, desktop data source setting, oiwer bi service login,  and gateway data source settings ? 

[v-janeyg-msft]

Hi, @shaht 

The account you log in to powerbi has nothing to do with the credentials of your data source. You need to consider whether there are permission restrictions on the data source, and whether you can view the data source by entering the credentials of the data source in powerbi. 

Also, are you sure you are in DQ mode not LC(ssas)? If it is LC mode, the map data source is required.

Janey

[shaht]

Ok I understood regarding the cedentials logic. 

I have used DQ only. You have any other possible solution or work around for this ? 

[shaht]

I can see the datasource in my Desktop version but not on Power Bi service  

[v-janeyg-msft]

Hi, @shaht 

Based on the information you have provided so far, I can't find any possible reason for the error. Can the report be displayed normally in the desktop? Whether other sql data sources can be used normally in the service? 

Are you sure you don't have any RLS?

[shaht]

@v-janeyg-msft  There is no RLS. The report runs perfectly in desktop version. 

I only have access to this SQL databse right now. I will check with IT if we can check with other databases. 

For DQ, as connection shows successful, I used Analise in Excel option to get the excel file. I can see the fields in pivot table but once I drag any of them into raw/columns,  it shows below error. 

I also created an Import version of this file and published. i can see the report for the import version but the schedule refresh fails. 

[v-janeyg-msft]

Hi, @shaht 

Try to change the privacy level to 'none'.

Is the gateway online and signed in?

So what's the error message displayed by the import mode refresh?

Janey

[shaht]

Hi @v-janeyg-msft , I tried for "None" privacy setting too, but got same error. Yes Gateway is running. And below is the error I get when i try to demand/schedule refresh the import version. 

[v-janeyg-msft]

Hi, @shaht 

Can you find more specific error messages like this:

I found some thread with similar situation:

Solved: Unable to connect to the data source undefined - Microsoft Power BI Community

Solved: Unable to Connect to Data Source Undefined - Microsoft Power BI Community

Solved: data gateway cluster and failed refresh: "Unable t... - Microsoft Power BI Community

Hope it helps.

Did you map to the correct data source? The gateway is also not the same as the previous one. I feel like I didn't get your specific info...

Janey

[shaht]

@v-janeyg-msft  Yes Gateway is now running 24 hours on an admin account. Previously it was running locally on my PC. So datasource is correct. 

i also tried a dirrenet datasource, but it showed the same error. So the issue is with SQL server connection. Is there any permission to be given in SQL server setting to make this work ? 

[v-janeyg-msft]

@shaht 

Can you show your error message like this?

[shaht]

this is the message i get when i try to refresh import file 

[v-janeyg-msft]

Hi, @shaht 

Why are there two gateways? What is the connection information of LiveData in the gateway?

[shaht]

The upper one is running on my lcoal PC but i am not using it anymore. 

The information is as below

[v-janeyg-msft]

@shaht 

I see that the account don't seem to match.

Can you refresh other reports such as local excel files using this gateway below?

[shaht]

Yes the personal gateway is running on my pc. the data set get refreshed successfully. On premise gateway is on Admin PC.