topic Re: Loading Data On Prem to Fabric - SQL Endpoint Error in Data Engineering

Loading Data On Prem to Fabric - SQL Endpoint Error

tim_crowther1 — Wed, 19 Mar 2025 09:32:11 GMT

Hi,

We are trying to load data from an On-Prem SQL VM (source) to Fabric Warehouse or SQL DB (target). We have followed how to do this from the Microsoft documentation and Tales from the Field. We already use a gateway to load data into our Power BI models. We've been able to test using a fabric pipeline and gen2 data flow writing data from source to source, from target to target. However, when we try to go from source to target it throws an error when trying to write to the target:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'server_guid.database.fabric.microsoft.com,1433', Database: 'DB_GUID', User: ''. Check the connection configuration is correct, and make sure the SQL Database firewall allows the Data Factory runtime to access.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.),Source=Framework Microsoft SqlClient Data Provider,''Type=System.ComponentModel.Win32Exception,Message=An existing connection was forcibly closed by the remote host,Source=,'

This is inside a corporate environment and we are pretty confident that we have opened up the firewalls to the required ports and ip addresses and when we run the test network on the gateway it comes back with all passed.

We have also tested trying to connected to the SQL endpoints from Azure datastudio and SSMS and get the same error...

Can anyone help or has seen this before and can point us in the direction of the setting that will help us over this.

Thanks

Tim

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

Anonymous — Wed, 19 Mar 2025 11:56:23 GMT

Hi @tim_crowther1 ,

Thank you for reaching out to the Microsoft Fabric Community.

The error is likely caused by TLS/SSL issues, network blocks, or SQL server settings. First, make sure your SQL VM supports TLS 1.2 or higher — check the registry under SCHANNEL\Protocols.

Next, update your connection string to include TrustServerCertificate=True to skip SSL checks.

Make sure TCP/IP is enabled in SQL Server Configuration Manager, SQL Browser is running, and remote connections are allowed. If firewalls are open but Azure Data Studio or SSMS still fail, check for proxies, security tools, or antivirus that might block SQL traffic — try turning them off briefly to test.

Finally, update Azure Data Studio, SSMS, and SQL drivers to the latest versions. If nothing works, run a network trace (e.g., Wireshark) to find where the connection breaks.

For additional assistance, please refer to Microsoft’s TLS Troubleshooting Guide.

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

Anonymous — Sat, 22 Mar 2025 09:51:37 GMT

Hi @tim_crowther1 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

tim_crowther1 — Sat, 22 Mar 2025 15:29:37 GMT

Hi @Anonymous,

We have gone through everything in the Micorosoft documentation but still haven't been able to connect to the SQL endpoints.

One of the tests that we did run was to load data into the lakehouse through a pipeline. We managed to get this to work, but due to character limitations in strings, we can't use this approach across our entire dataset.

Being inside of a corporate environment infosec aren't too happy with: "proxies, security tools, or antivirus that might block SQL traffic — try turning them off briefly to test."

We will keep working to find the issue...

Thank you for your suggestions, definitely something good for us to check.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

Anonymous — Mon, 24 Mar 2025 05:01:22 GMT

Hi @tim_crowther1 ,

Thank you for your clarification details. Could you please consider the following approaches for ensuring the sql endpoint connection issue.

Ensure that your network security groups, firewalls, and any intermediate security appliances are configured to allow traffic on the necessary ports (e.g., TCP port 1433) between your on-premises environment and Microsoft Fabric endpoints. Additionally, verify that the necessary permissions are set for users accessing the SQL endpoints.
Review your connection strings to ensure they are correctly formatted. Avoid using unsupported parameters such as Integrated Security=True or Trusted_Connection=True, as these are not supported by Fabric.

For more details on how to create a sql endpoint and use power bi to get data from workspace via sql endpoint you can refer to the official documentation.

Connectivity to data warehousing - Microsoft Fabric | Microsoft Learn
CREATE ENDPOINT (Transact-SQL) - SQL Server | Microsoft Learn

Also, the character limitation you're experiencing is a known issue in Microsoft Fabric's data warehouse, where varchar fields are limited to approximately 8,000 characters. To work around this limitation: please have a look into the similar thread which help you understand better.

https://community.fabric.microsoft.com/t5/Fabric-platform/Ingesting-Varchar-MAX-to-datawarehouse-failure/m-p/3867594

Split large text data into smaller chunks within the 8,000-character limit and adjust your data processing logic to reconstruct the original text. Consider using external storage like Azure Blob Storage for large data, and reference it in your Fabric workflows. These strategies help navigate current limitations and security constraints for better integration with Microsoft Fabric.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

Anonymous — Thu, 27 Mar 2025 01:25:28 GMT

Thank you.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

Anonymous — Mon, 31 Mar 2025 16:52:00 GMT

Hi @tim_crowther1 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Loading Data On Prem to Fabric - SQL Endpoint Error

tim_crowther1 — Wed, 02 Apr 2025 19:16:16 GMT

Hi @Anonymous,

Unfortunately as we are in a corporate environment, we are still waiting for infrastructure to install and run a network trace using Wireshark as updating and looking into everything else hasn't help as of yet.

Will keep you posted.

Thanks