Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Error loading data from on prem SQL server to Fabric Lakehouse with Dataflows Gen2

miguel I am trying to load a table with Dataflows Gen 2 from an on premise SQL server to a Lakehouse in Fabric with load to destination. No luck so far. I keep getting this error:  There was a proble...
Dataflows
  • Anonymous's avatar
    Anonymous
    3 years ago

    The solution was in our case that is was not enough to open up port 1433 en whitelist the right addresses *.datawarehouse.pbidedicated.windows.net and the more usecase specific *.northeurope.cloudapp.azure.com in the Fortinet firewall but to open up SQL access in the firewall from our VM (where the gateway is) to the Fortinet maintained internet service 'Azure'.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

miguel's avatar
miguel
Community Admin
3 years ago

could it be that your SQL Instance needs to add some of the IPs used by Power Query and Fabric to the allowlist? This error points to a network related issue.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Ah ok. I'll look into it tomorrow.

     

    • jwelch's avatar
      jwelch
      Microsoft Employee
      3 years ago

      The error message you are seeing isn't about your on-prem SQL Server. Notice that the DataSourceKind is Lakehouse - this means that it's either dataflow staging (which uses Lakehouse) or your destination Lakehouse (probably the destination Lakehouse, since the error is coming up on the Activity writing to the destination Lakehouse).

       

      The SQL Endpoint is used to write to the Lakehouse, so you need make sure that your local network allows connectivity to it. You can get the endpoint info for the Lakehouse from the "More options" menu on the Lakehouse and use the option for the connection string. You can see if it is open by testing from your local machine using SSMS or Azure Data Studio.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        thanks for your guidance jwelch I tried the SQL connection string of the destination lakehouse in SSMS and indeed it gave the same sort error:

        ------------------------------
        ADDITIONAL INFORMATION:

        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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

        For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error

        ------------------------------

        The network path was not found

        ----------------------------------------------------------

        So now I have to ask the VM / network IT team to allow for connection to this lakehouse and from here on every time we want to use or test a new lakehouse repeat that proces or is there a way to use a range of IP's or something like @miguel suggested?

        Our gateway is on a VM, so if that VM should be able to connect to the SQL Endpoint, right?

        I looked a the list of IP's for Azure, but there are a lot of adresses 🙂 I would love some help on which to use here.
        Download Azure IP Ranges and Service Tags – Public Cloud from Official Microsoft Download Center
        Our capacities are in North Europe.
        Maybe PowerQueryOnline, DataFactory, SQL, AzureDataLake?