Power BI Service Data Refresh issues

[raji_chandra]

Hello,

I recently started facing data refresh failure in Power BI service when I am not logged into my computer. The data source is SQL Server. I am able to connect with the SQL server in ADF and SSMS, the refresh works if I logged into my computer. But during the weekend or holiday, the following error is thrown:

"Processing error: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)
Cluster URI: WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net"

Is there any known issue going on that stops data refresh?

Regards,

Raji Chandrasekaran

[raji_chandra]

Hello,

I have connected data with database credentials and remove on premise gateway. The timeout is now changed to 35 minutes for the command and query. Still the following error is thrown: 

An error happened while reading data from the provider: 'Internal .NET Framework Data Provider error 6.'

Why is this error shown?

Regards,

Raji

[v-kkf-msft]

Hi @raji_chandra ,

Please see if these documents are helpful.

https://www.reddit.com/r/PowerShell/comments/itt0at/getting_timeout_errors_from_azure_serverless_sql... 

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi Raji! can you tell us a bit more? is the SQL Server an onpremise DB engine? do you have a gateway installed? Which one (personal/standard)?

That might help us to help you

Regards,

[raji_chandra]

Hello,

Thanks for the reply! The SQL DB is in the cloud, Azure SQL Server. The gateway is installed in the computer and it is standard.

Regards,

[ibarrau]

Oh. You shouldn't be using gateway if it is a Azure SQL Database. You can directly connect the Power Bi Service with Azure SQL Database just editing the credentials. Unless you have another on premise source you can avoid gateway. 
The gateway need the PC or Machine working turned on. That should be all because once you logged in it will run a service in windows.

Even though it looks like there is a timeout. That can be because of a bad login or the response of the server is slow and the timeout runs. Try expanding the timeout for command and connection in Power Query code. This doc shows the parameters you can use:

https://docs.microsoft.com/en-us/powerquery-m/sql-database

I would add more time there and if the DB is the only source, try connecting that source directly without gateway and just editing credentials.

I hope that helps,