Forum Discussion

Dirk VERLIEFDEN's avatar
Dirk VERLIEFDEN
Copper Contributor
Jul 30, 2020

Excel Power Query SQL timeout

Hello,

Recently my company started using Azure VPN.

I have a large number of Power Queries in Excel on SQL databases.

Since the move to this Azure VPN I can't run the queries in Excel (v2016-32bit) due to timeout.

Error message:
DataSource.Error: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16012; handshake=9443;
Details:
DataSourceKind=SQL
DataSourcePath=xxxsql01\ABCABC
Message=Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16012; handshake=9443;
Number=-2
Class=11

 

M-code=

Source = Sql.Databases("xxxSQL01\abcabc", [CommandTimeout=#duration(0, 0, 10, 0)]),
InfoBe = Source{[Name="InfoBe"]}[Data],
dbo_tmovemen = InfoBel{[Schema="dbo",Item="tmovemen"]}[Data]

- When I copy & paste this M-code in Power-Bi Desktop, there is no problem to connect without delays.
- When I switch back to Forticlient VPN, there is no problem to run the queries in Excel.
- CommandTimeout : tests done with 20, 30, 40, 50minutes, 1h, 1h10 -> no effect.

 

Is the SQL connector in Excel different from the Power-Bi connector?
Is there a setting on the SQL permissions or in the Azure VPN permissions that must be set?
Is switching to Excel64bit a solution?

For more information, please do not hesitate to contact me.

Best regards,

Dirk.

 

5 Replies

Resources