Handshake issues when using OAuth2 Active Directory credentials for SQL server database

[Anonymous]

This issue only started for me about 2-3 weeks ago. With the automated refresh on powerbi.com, you will get about a 50/50 change of succeeding a refresh, and the other times it will generate an issue like this:

"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. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=71; handshake=214; [Login] initialization=0; authentication=0; [Post-Login] complete=69; The duration spent while attempting to connect to this server was - [Pre-Login] initialization=30210; handshake=0; Table: AggProgramPeriod."

I have had this happen on all our servers in the EU, USA and Canada. A solution is to switch back to basic authentication for the database. There seems to be something wrong with the OAuth2 Mechanism of refreshing datasets automatically online.

Jaap

[v-qiuyu-msft]

Hi @Anonymous, 

Please try to increase connection timeout. This can be done by editing the report in PBI Desktop and updating the query to use connection timeout and then republish their report to PBI Service. They may have to try out different duration to check the connection timeout. See details here - https://docs.microsoft.com/en-us/powerquery-m/sql-database

Example:
Source = Sql.Database("Servername", "tablename", [option1, option2, ConnectionTimeout=#duration(0, 0, 20, 0)])
or
Source = Sql.Databases("Servername", [option1, option2, ConnectionTimeout=#duration(0, 0, 20, 0)])

Best Regards,
Qiuyun Yu

[Anonymous]

Can you elaborate on why you think this will help? The connection fails within minutes, so the default timeout is never reached. Also, when I connect using powerBI desktop it works and never fails. 

[v-qiuyu-msft]

Hi @Anonymous, 

I provided the method in my previous post based on the Connection Timeout Expired error message. Refresh the dataset in Power BI service is not very same as in Power BI desktop, as many operations may consume Power BI service capacity resource which will affect the dataset refresh, eg: dataset/dataflow refresh, report load, and so on. See: https://docs.microsoft.com/en-us/power-bi/service-premium-capacity-optimize#what-content-is-using-up-my-capacity

I would suggest you increase timeout value as mentioned in my previous post. Also please modify the refresh time to a different time when Power BI service workload is light. 

Best Regards,
Qiuyun Yu

[Anonymous]

I haven't been able to consistently reproduce the issue unfortunately. But for everyone finding this topic, I advise to switch away from organizational authentication, and use a database/password combo instead.