Jul 29 2020 11:45 PM - edited Jul 30 2020 02:07 AM
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.
Jul 30 2020 02:36 AM
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... at the bottom and comments, it was written at pre-PowerBI Desktop time, most probably with Excel 2016 as sample.
Jul 30 2020 03:30 AM
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.
Dec 22 2020 01:26 AM
Timeout problem has disappeared.
However, now I can't connect due to another error:
DataSource.Error: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
When I copy-paste the M-code from the Excel (2016) power query into Power Bi power query, there is no error!?
Any advice?
Dec 22 2020 12:14 PM
In Excel please check data source parameters for this source - credentials, encryption