SQL data connection occasionally timing out for scheduled refreshes

[Lexicron]

Our PowerBI dataset pulls in from an SQL database. I've set the PowerBI dataset to refresh every 12 hours. Sometimes refresh is successfully, but sometimes it times out with this 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.)

I don't know much about SQL, I just work in PowerBI. If I need to step outside of PowerBI to change some SQL settings, please talk to me like I'm 5!

The SQL database is setup in our Azure portal. I have access to the firewall settings. Is there anything here that helps resolve the time-out issue?

I do also have access to SQL Server Management Studio if I do need to use it, but I don't know how to use it, so a walkthrough would be super appreciated please. I've done a query on the table sys.database_firewall_rules but there is nothing in there.

I am using the user login highlighted in yellow, which only appears in 2 places out of a possible 3:

I don't have access to nor knowledge of PowerShell.

[Lexicron · Accepted solution]

Thank you @v-jingzhang !

The issue appears to be fixed now and they are refreshing without issues.

The change I did was to set the following setting to YES in Azure (in the first screenshot):

[v-jingzhang]

Thank you for sharing your solution!

Cheers

Jing

[v-jingzhang]

Hi @Lexicron 

I did some more research. I think this issue might be caused by resource starvation on the DB side. Does this error occur on a specific scheduled time? During the refresh failure time, is the database at high utilization? If so, you may consider scaling up your SQL Azure instance to add more resources to it. See Scale resources - Azure SQL Database | Microsoft Docs

Another operation you could try is to restart your SQL Azure instance. You can simply scale the database to another tier and then scale it back. When scaling, it would restart database engine process. Sometimes restarting will solve some DB issues. See SQL SERVER - How to Restart SQL Azure Database? - SQL Authority with Pinal Dave

Hope this helps.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[v-jingzhang]

Hi @Lexicron 

According to the information you provided, the issue seems to happen in Azure SQL database rather than Power BI. The connection to Azure SQL DB is established successfully while The wait operation timed out. 

I suggest that you seek help at Azure SQL Database forum.

Regards,
Community Support Team _ Jing