Scheduled Refresh is failing intermitently , ( approx 2-3 times out of every 8 refreshes, but not always concurrently )after i added an Azsure SQL database table to my dataset.
I have an existing Azure SQL connection from another server which works fine and never had these issues until now.
Initially i was going to go with a Postgres SQL from AWS but realised i needed an Gateway, so went with Azure to avoid that extra level of potential issue.
I never needed a gateway with the othe rSQL connection, so don't see that this connection would need one.
I increased the Connection Timeout in the Data Source settings in the pbix file to 30 from the default , which i believe is 10 mins., but only improved things for a couple of days before going back to the current failure rate.
Most times it refreshes ok when i manually refresh,s o it's a transient issue
The below list are an example of the Error Details returned after failing.
Processing error: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=33; handshake=88; [Login] initialization=0; authentication=0; [Post-Login] complete=29954
-----------------------------
Processing error: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.
-----------------------------
Processing 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=349; handshake=91;
-----------------------------
Processing error: An error happened while reading data from the provider: 'Internal .NET Framework Data Provider error 6.'
-----------------------------
If it was always a single reason, it might be easier to resolve, hence i'm reaching out for help.
cheers
Nigel
