We have an issue with scheduled refreshes not working occasionally. This is ocurring with two different on-premise data gateways that connect to two different SQL Servers and databases which are part of an Availability Group. The error is basically the same each time. The Table value changes to one of the queries we have in the dataset file. The Pre-Login and Handshake values change as well sometimes.
Part of Error message:
Underlying error code: -2147467259 Table: "TableName".
Underlying error message: 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=133; handshake=14961;
We have added connection timeout of 10 minutes to each of the queries in the dataset file. but the issue persists.
This article below seems close to what we are encountering but is not in context of PowerBI.
I have scanned the web and this forum, but I have not found a fix. Has anyone encountered the same issue?
Is the gateway is latest version. (please try to open the Gateway and sign-in with the right credentials)
If it is the latest Gateway version, please check the dataset settings with Gateway Connection and Data Source Credentials.
If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and give it some kudos?
Hi, can you please try adding a timeout like 120 minutes for each query in edit queries. That is the power bi refresh limit from service. If that much doesn't work then you have a problem because 10 minutes is not so much for big tables. The engine could be busy at that time and work upload the data under 10 minutes.
I don't think is a power bi problem it looks like an engine problem getting the query results very slow.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If the error is in 20-30 seconds is a connection problem. The gateway is having trouble to connect to the data source. Please be sure you have the gateway output ports opened and to test a connection from the gateway administration and from the gateway computer to garantee you can actually connect the data.
Thanks, but have run the gateway port test. All came back fine. I have additional logging turned on as well. Unfortunately it the gateway logs show the same information as the Power BI "Refresh History" modal. There is no rhyme or reason to the failures and am thinking at this point it is something at the network layer or an AV monitor/scanner on the server blocking communication out.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!