Error: 40 - Could not open a connection to SQL Server

The error message "Error 40: Could not open a connection to SQL Server" typically indicates a connectivity issue between your application (in this case, Power BI) and the SQL Server. Since you mentioned that the import process starts successfully but fails after some time, this could suggest several potential causes:

1. Network Timeouts: If the import process takes a long time, it may exceed the timeout settings for the connection. You can check and adjust the timeout settings in Power BI or the SQL Server connection string to ensure they are sufficient for your data import needs.
2. Firewall Restrictions: Ensure that there are no firewall rules that might be interrupting the connection after it has been established. Sometimes, firewalls can terminate long-running connections.
3. SQL Server Settings: Verify that SQL Server is configured to allow remote connections and that the SQL Server Browser service is running, especially if you are using named instances. If the SQL Server is set to allow only a limited number of concurrent connections, this could also lead to issues during large data imports.
4. Resource Limitations: Check the SQL Server for any resource constraints (CPU, memory, etc.) that might cause it to drop connections under load.
5. Connection Pooling: If you're using connection pooling, ensure that the pool settings are configured correctly to handle the expected load during data imports.

To prevent this error during large data imports, consider the following best practices:

• Increase the connection timeout settings in Power BI.
• Ensure that the SQL Server can handle the expected load and has sufficient resources available.
• Monitor the network for any interruptions or issues that could affect long-running connections.
• Regularly check the SQL Server logs for any error messages that might provide additional insights into the issue.

By addressing these areas, you should be able to mitigate the occurrence of Error 40 during your data import processes.

Is this deterministic, or do you get the error at random places?

If the error happens in random places, this suggests transient network disruptions. If Power BI in the cloud and SQL Server on-prem this is not unlikely at all. The remedy is to implement retries, so that if you get an error, you try again rather than raising the error.

If the error occurs at one specific stage in the operations, the most likely explanation is that there is a step which has incorrect connection information. A more spectacular explanation is that an operations causes SQL Server to crash, and for some reason Power BI does notice this, but notice that SQL Server is no longer alive.