Running into refresh issues with Dataflow Gen 2

[hammadhassan90]

I am running into refresh failures when using SQL On Prem data as source and Lakehouse as destination. Previously, i was getting the following error:

Mashup Exception Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.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.) Details: DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = 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.)

After following the guidance underlined in the following article

I am still running into refresh issues but this time I am getting the following error.: Gateway proxy unable to connect to SQL. Learn how to troubleshoot this connectivity issue here: https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy.

the gateway works fine for gen1 dataflows as well as power bi datasets with on-prem data. Based on the instructions provided in the article, I went ahead and added *.datawarehouse.pbidedicated.windows.net url to be allowed by firewall. Am I missing a step?.

[BC852]

Hi I have the same issue, does anyone can help 😭

[AnonymousPerson]

@hammadhassan90 

Did you find a resolution?  I am facing the exact same error: "Gateway proxy unable to connect to SQL"

The gateway is working fine for non-Fabric, and is up to date. 

I'm desparate to use Fabric but in 2 months of preview still haven't been able to load one cell of VARCHAR data from an on-prem SQL Server. Fabric has more deal-breakers than game-changers at the moment.

[parkergeis]

@AnonymousPerson Were you able to find a solution?

[rubayatyasmin]

Hi, @hammadhassan90 

It looks like you are facing two different issues here:

1. A network-related error or instance-specific error that occurred while establishing a connection to SQL Server.
2. The gateway proxy is unable to connect to SQL.

The first issue usually occurs due to incorrect SQL Server instance name or if the SQL Server does not allow remote connections. If you've already checked these, and your SQL Server instance is running and allows remote connections, the issue could be a firewall blocking the connection or not having the correct client protocols enabled in SQL Server configuration manager.

The second issue suggests a problem with the gateway setup. While you've allowed a URL through your firewall, it's also important to check whether your gateway is properly installed, configured, and up to date.

Here are a few things you can try to resolve these issues:

1. Check your SQL Server Configuration:

   • In the SQL Server Configuration Manager, check if TCP/IP is enabled under SQL Server Network Configuration > Protocols for [YourInstanceName].
   • Under SQL Server Native Client Configuration > Client Protocols, make sure TCP/IP is enabled.
   • Check if the SQL Server Browser service is running, as this service is used to provide the SQL Server connection to the client.

2. Check Firewall settings:

   • Ensure that your firewall allows incoming connections for the SQL Server port (default is 1433).
   • Add an exception in the firewall for sqlbrowser.exe.

3. Check your Data Gateway:

   • Make sure that your gateway is running and is updated to the latest version.
   • Ensure the correct credentials are entered for the data source in the gateway configuration.
   • Try to restart the gateway service and test the connection again.
   • If you're using a standard mode (single-tenant) gateway, check if adding *.servicebus.windows.net to your allowlist helps, as it's the endpoint used by Azure Service Bus, which is the technology the on-premises data gateway uses to facilitate communication between the cloud and on-premises.