Forum Discussion
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
- SergeiBaklanDiamond Contributor
Power Query in Power BI Desktop and in Excel are definitely bit different, especially if compare with Excel 2016. However, ConnectionTimeout shall work in Excel. I have no my own experience, please check this post https://blog.crossjoin.co.uk/2014/09/29/advanced-options-for-loading-data-from-sql-server-with-power-query/ at the bottom and comments, it was written at pre-PowerBI Desktop time, most probably with Excel 2016 as sample.
- Dirk VERLIEFDENCopper Contributor
Thank you for your reply.
I only get the timeout when connecting Excel query through Azure VPN, not when I'm connecting through Forticlient VPN.
Is there a setting in Excel for Azure VPN?Regards,
Dirk.
- SergeiBaklanDiamond Contributor
Sorry, I don't know