Excel Power Query SQL timeout

%3CLINGO-SUB%20id%3D%22lingo-sub-1554988%22%20slang%3D%22en-US%22%3EExcel%20Power%20Query%20SQL%20timeout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554988%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3ERecently%20my%20company%20started%20using%20Azure%20VPN.%3C%2FP%3E%3CP%3EI%20have%20a%20large%20number%20of%20Power%20Queries%20in%20Excel%20on%20SQL%20databases.%3C%2FP%3E%3CP%3ESince%20the%20move%20to%20this%20Azure%20VPN%20I%20can't%20run%20the%20queries%20in%20Excel%20(v2016-32bit)%20due%20to%20timeout.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EError%20message%3A%3CBR%20%2F%3E%3CEM%3EDataSource.Error%3A%20Microsoft%20SQL%3A%20Connection%20Timeout%20Expired.%20The%20timeout%20period%20elapsed%20while%20attempting%20to%20consume%20the%20pre-login%20handshake%20acknowledgement.%20This%20could%20be%20because%20the%20pre-login%20handshake%20failed%20or%20the%20server%20was%20unable%20to%20respond%20back%20in%20time.%20The%20duration%20spent%20while%20attempting%20to%20connect%20to%20this%20server%20was%20-%20%5BPre-Login%5D%20initialization%3D16012%3B%20handshake%3D9443%3B%20%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDetails%3A%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDataSourceKind%3DSQL%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDataSourcePath%3Dxxxsql01%5C%3C%2FEM%3EABCABC%3CBR%20%2F%3E%3CEM%3EMessage%3DConnection%20Timeout%20Expired.%20The%20timeout%20period%20elapsed%20while%20attempting%20to%20consume%20the%20pre-login%20handshake%20acknowledgement.%20This%20could%20be%20because%20the%20pre-login%20handshake%20failed%20or%20the%20server%20was%20unable%20to%20respond%20back%20in%20time.%20The%20duration%20spent%20while%20attempting%20to%20connect%20to%20this%20server%20was%20-%20%5BPre-Login%5D%20initialization%3D16012%3B%20handshake%3D9443%3B%20%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ENumber%3D-2%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EClass%3D11%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EM-code%3D%3C%2FP%3E%3CP%3ESource%20%3D%20Sql.Databases(%22xxxSQL01%5Cabcabc%3CEM%3E%22%2C%3C%2FEM%3E%20%5BCommandTimeout%3D%23duration(0%2C%200%2C%2010%2C%200)%5D)%2C%3CBR%20%2F%3EInfoBe%20%3D%20Source%7B%5BName%3D%22InfoBe%22%5D%7D%5BData%5D%2C%3CBR%20%2F%3Edbo_tmovemen%20%3D%20InfoBel%7B%5BSchema%3D%22dbo%22%2CItem%3D%22tmovemen%22%5D%7D%5BData%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E-%20When%20I%20copy%20%26amp%3B%20paste%20this%20M-code%20in%20%3CSTRONG%3EPower-Bi%20Desktop%3C%2FSTRONG%3E%2C%20there%20is%20no%20problem%20to%20connect%20without%20delays.%3CBR%20%2F%3E-%20When%20I%20switch%20back%20to%20Forticlient%20VPN%2C%20there%20is%20no%20problem%20to%20run%20the%20queries%20in%20Excel.%3CBR%20%2F%3E-%20CommandTimeout%20%3A%20tests%20done%20with%2020%2C%2030%2C%2040%2C%2050minutes%2C%201h%2C%201h10%20-%26gt%3B%20no%20effect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20the%20SQL%20connector%20in%20Excel%20different%20from%20the%20Power-Bi%20connector%3F%3CBR%20%2F%3EIs%20there%20a%20setting%20on%20the%20SQL%20permissions%20or%20in%20the%20Azure%20VPN%20permissions%20that%20must%20be%20set%3F%3CBR%20%2F%3EIs%20switching%20to%20Excel64bit%20a%20solution%3F%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20more%20information%2C%20please%20do%20not%20hesitate%20to%20contact%20me.%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EDirk.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1554988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555352%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20SQL%20timeout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F242696%22%20target%3D%22_blank%22%3E%40Dirk%20VERLIEFDEN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Query%20in%20Power%20BI%20Desktop%20and%20in%20Excel%20are%20definitely%20bit%20different%2C%20especially%20if%20compare%20with%20Excel%202016.%20However%2C%20ConnectionTimeout%20shall%20work%20in%20Excel.%20I%20have%20no%20my%20own%20experience%2C%20please%20check%20this%20post%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2014%2F09%2F29%2Fadvanced-options-for-loading-data-from-sql-server-with-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblog.crossjoin.co.uk%2F2014%2F09%2F29%2Fadvanced-options-for-loading-data-from-sql-server-with-power-query%2F%3C%2FA%3E%26nbsp%3Bat%20the%20bottom%20and%20comments%2C%20it%20was%20written%20at%20pre-PowerBI%20Desktop%20time%2C%20most%20probably%20with%20Excel%202016%20as%20sample.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555443%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20SQL%20timeout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555443%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3EI%20only%20get%20the%20timeout%20when%20connecting%20Excel%20query%20through%20Azure%20VPN%2C%20not%20when%20I'm%20connecting%20through%20Forticlient%20VPN.%3CBR%20%2F%3EIs%20there%20a%20setting%20in%20Excel%20for%20Azure%20VPN%3F%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EDirk.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555959%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20SQL%20timeout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F242696%22%20target%3D%22_blank%22%3E%40Dirk%20VERLIEFDEN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20don't%20know%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

 

3 Replies
Highlighted

@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. 

Highlighted

@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.

Highlighted

@Dirk VERLIEFDEN 

Sorry, I don't know