SQL Managed Instance - Dataflow Refresh Issue

[danjo914]

We recently transitioned our dataflows to connect to a SQL Managed Instance, moving away from our on-premise gateway setup. While setting up Power Query, I was able to successfully view the data from the new SQL Managed Instance. However, an issue arises when I attempt to refresh the dataflow.

Power BI Setting up Dataflow, Power Query shows data but upon refresh receive the following error "Error: Data Source Error : DataSource.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider error: 0 - An attempt was made to access a socket in a way forbidden by its access permissions.) 

I find it interesting that our configuration allows Power Query to establish a successful connection with the data source, yet encounters an issue during the refresh process. I am at a loss for what might be causing this.

I would appreciate any insights or suggestions on potential problem areas we could investigate, or any steps we could take to diagnose and resolve this issue.

Thank you in advance for your assistance.

[v-yueyunzh-msft]

Hi ,  @danjo914 

According to your description, you can connect your sql server in Power Query Online but when you refresh it it return an error code "An attempt was made to access a socket in a way forbidden by its access permissions". This is actually strange.

For your problem, you can try chek:

(1) Is your SQL SERVER a cloud data source, because you said you can move away gateway. If it is a cloud data source, then there is no need to use a local data gateway to connect to the data source, you can use your credentials to try to access the data source, and configure the credentials in "Data Source credential". If it cannot be refreshed, according to your error message, the Power BI Service cannot find the server through your server name and credentials. You can try to check whether the request from Power BI Service is blocked on the SQL SERVER side. And to check that your credentials are correct, try using ip:port as the server name.

(2) If you need to use the local data gateway to access your SQL SERVER, you can try to test whether Desktop can connect on the computer where the local data gateway is installed. Also, you need to check if the firewall is blocking some requests from the cloud:
Adjust communication settings for the on-premises data gateway | Microsoft Learn

And you can also refer to this if it can help :
A network-related or instance-specific error occurred - SQL Server | Microsoft Learn

sql server - An attempt was made to access a socket in a way forbidden by its access permissions - S...

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[danjo914]

Thank you for your response. We believe that the issue was caused by the On-Premises Gateway redirecting to the on-premises database, rather than to the Azure SQL MI as specified in the data source , as the tables and datbase name are the same it was rather misleading. When we removed the Gateway from the Power Query, the connection failed. As we intend to move away from the On-Premises Gateway, we'll need to reassess our networking rules.