PowerBi DirectQuery + SQL Data Connection Issue

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?

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. 

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

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. 

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

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 ? 

shaht 

Can you show your error message like this?

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

Hi, shaht 

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

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

The information is as below

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?

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

shaht 

' the data set get refreshed successfully ' So you're fine now, are you?

v-janeyg-msft  It was successful only for personal gateway. For on-premises I am having the issue. My IT team just advised me that data source Encryption should be ON for gateway connection. They are testing it right now. I hope it will solve it. 

Hi, shaht 

Have you tried to change the authentication method to 'window'?  I found that my machine will fail to connect if I use 'basic'. Since your personal gataway can connect successfully, The problem must be in the configuration of the service.

Yes Windows was the first option. Then I got special SQL credentials from IT with no restrictions and I used basic method, but still same issue. The error has been same in all cases. 

What are your thoughts on the 'Encryption' option ? 

shaht 

Actually the 'Encryption' option you mentioned, I never noticed it, this step on the desktop is not important. Of course, it would be great if just ticking this option would solve the issue.

I still think it is because the service is not configured properly, you should always unify the credentials in the desktop and service. 

Manage your data source - SQL - Power BI | Microsoft Docs

Janey