Connection Timeout Settings with SQL Server Database

[JaneLHunt]

Hello

Would anybody in Microsoft please be able to clarify the following concerning the Connection Timeout Setting and its use with the Power Query M code function SQL.Database?

In the Power BI Service with automated refreshes from SQL Server, via a Gateway Server, we have connection timeouts on a regular basis.  
There is no firewall or other impediment preventing the connection and can tell from SQL logs it is a connection timeout issue.
The error returned in the Power BI Service is:
An error happened while reading data from the provider: 'Internal .NET Framework Data Provider error 6.

In Power Query, the SQL.Database function is used to connect to SQL Server.

In the documentation,
https://learn.microsoft.com/en-us/powerquery-m/sql-database
It states for the Connection Timeout
ConnectionTimeout: A duration that controls how long to wait before abandoning an attempt to make a connection to the server.
The default value is driver-dependent.

1.  A data source for SQL Server has been created to on the Gateway Server.
    What driver is the Gateway using to connect?
    The SQL Server Native Client is installed on that Gateway Server. Is it using that?

2. If so, please clarify what is the correct syntax to use in Power Query if I want to increase the Connection Timeout.
Is it the same as the Command Timeout syntax, with the time split out into Days, Hours, Minutes, Seconds?

NB.
The SQL Database is on an Azure SQL Serverless server which has Auto Pause enabled, default setting of one hour, so if server is idle for over an hour, it will pause.
We are using a Gateway server to connect from the Power BI Service because:
1.  The Allow Azure services and resources to access this server box is not ticked.
2. We do not yet have VPN/Private Endpoints set up

3. Because of the above 2, access is controlled via the server's firewall, which includes the Gateway Server's address.

The connection timeouts are probably caused because the Azure Serverless SQL database is paused when the Power BI Service automated refresh schedule kicks in.
As a work-around, we would like to increase the connection timeout, but have been unsuccessful in trying to find any clear documentation about this.

Any clarification on Connection Timeout gratefully received!

[JaneLHunt]

Hello @DanielMartin 

At the time, in the Power BI Service (not desktop), for automated data refresh of semantic models, I was unable to set the connection timeout in Power Query.
Connection timeout = A duration that controls how long to wait before abandoning an attempt to make a connection to the server.
Only the command timeout could be set. Command timeout = A duration that controls how long the server-side query is allowed to run before it is canceled.

The problem was caused by the Power BI Service connecting to Azure SQL Serverless databases with AutoPause enabled.
When the automated refresh schedule in the Power BI Service kicks off, the Azure SQL database is already paused. Hence when the Power BI Service tried to connect, it was unable to, causing connection timeout error.

The way we resolved this problem was:

1. On Azure SQL Database side - lengthen the time before it goes into Pause mode to allow time for the Power BI Refreshes to start before being paused.
2. Where possible for our Power BI Datasets which load data from our Data Warehouse also based on an Azure SQL Serverless database, build into our Synapse Pipelines Extract, Load and Transform process the ability to trigger Power BI dataset refreshes once data loaded into the Data Warehouse.
In this way, the Power BI Service data refreshes are triggered before the Azure SQL database goes into Pause mode.

Your situation may be different from the above, but those were our resolutions re Azure SQL Serverless databases in AutoPause mode causing connection timeout errors in the Power BI Service.

[cassidy]

I'm guessing you are looking for the following

let
    Source = Sql.Databases("Servername", [CommandTimeout=#duration(0, 0, 35, 0)]),  

 Here is a solved post for more context
https://community.fabric.microsoft.com/t5/Desktop/How-to-set-command-timeout-option/m-p/487941

[JaneLHunt]

Hi @cassidy 
Thanks, but I am specifically asking about connection timeout, not command timeout.  And this is concerning how connection timeout is handled in the Power BI Service, not desktop.
Command timeout = A duration that controls how long the server-side query is allowed to run before it is canceled.
Connection timeout = A duration that controls how long to wait before abandoning an attempt to make a connection to the server.

[DanielMartin]

Hey Jane, did you figure this out? Having the same problem. 

Did you have to set ConnectionTimeout manually on every table you imported to the PowerBI semantic model, or did you figure out something else?

Appreciate answers! 🙂