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

[v-janeyg-msft]

@shaht 

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

[shaht]

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

[v-janeyg-msft]

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.

[shaht]

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 ? 

[v-janeyg-msft]

@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

[shaht · Accepted solution]

@v-janeyg-msft It is solved now. The encryption was to be enabled on the SQL Server side – As our server is internal-only we hadn’t enabled encryption on the connection.  

[shaht]

Hi, I have a databse in SQL which updates every 15 minutes. I used DirectQuery to get live data for current day and publish it on PBI Service. For Direct Query, the update can be scheduled for 15 minutes. The standard refresh that you mentoned is for Import option I beliive. 

[lbendlin]

Something doesn't quite compute. You say you are connecting via direct query yet you have set a refresh schedule. Please explain what made you choose that.

Also, how can you schedule refreshes every 15 minutes?  The standard refresh request slots are top and bottom of the hour.