Excel Power Query SQL timeout

[Dirk VERLIEFDEN]

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.

[#2]

@Dirk VERLIEFDEN 

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. 

[#3]

@Sergei Baklan 

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.

[#4]

@Dirk VERLIEFDEN 

Sorry, I don't know

[#5]

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?

[#6]

@Dirk VERLIEFDEN 

In Excel please check data source parameters for this source - credentials, encryption