COPY DATA FROM ON-PREM SQL Database to Azure SQL Database

[ivamsikrishna]

Hi We are trying to copy data from on-prem sql database to Azure SQL database in Microsoft Fabric. We have crated Gateway connection to connect to On-Prem sql database and Azure SQL Database with basic schema. We try to create a copy activity and add On-Prem SQL Database as source and Azure SQL Database ad target database. When we truunig the pipelines we are getting below error. Can please help with workaroount if there any  steps if we are missing to copy data from On-Prem sql database to Aacure aql data ase.  Much appreciated, 

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server:

access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.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 - The semaphore timeout period has expired.),Source=.Net SqlClient Data Provider,SqlErrorNumber=121,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=121,State=0,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),},],''Type=System.ComponentModel.Win32Exception,Message=The semaphore timeout period has expired,Source=,'

[Adityakumar]

As per my experience it is a gateway issue, are you able to check the connectivity from In-prem to Fabric. 

[v-priyankata]

Hi @ivamsikrishna 

I hope the information shared by @mabdollahi helpful to you. @mabdollahi thank you for your inputs.
@ivamsikrishna  If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

Thank you for reaching out to the Microsoft Fabric Forum Community.

[v-priyankata]

Hi @ivamsikrishna 

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.

[v-priyankata]

Hi @ivamsikrishna 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the solution would be awesome for others who might run into the same thing.

[Adityakumar]

Please verify below and check the article shared to get more insights. Hope this resolves your issue.

Verify Network Accessibility:

1. Confirm that TCP port 1433 is open on both on-prem SQL Server and Azure SQL Database firewalls.
2. Test connectivity using tools like SSMS or telnet from the integration runtime machine.

Check Integration Runtime/Gateway Configuration:

1. Ensure the self-hosted integration runtime (gateway) is installed on the same network as your on-prem SQL Server.
2. Verify that the gateway is authenticated and configured correctly with Microsoft Fabric.

Validate Connection Strings and Credentials:

1. Double-check the connection strings in your copy activity for accuracy (server name, database, port, credentials).
2. Make sure that the credentials have the necessary permissions on both source and target databases.

Increase Connection Timeout:

1. Adjust the connection timeout settings to at least 30 seconds to handle potential network latency issues.

Review Network Stability:

1. Investigate any intermittent network latency or stability issues which might be causing the pre-login handshake to time out.

For more detailed troubleshooting steps and guidance, see the Microsoft documentation on connecting SQL Server databases through a gateway in Fabric: 
https://learn.microsoft.com/en-us/fabric/data-factory/connector-sql-server-database

[mabdollahi · Accepted solution]

Hi @ivamsikrishna 
It looks like the error is due to a network issue between Microsoft Fabric and your on-prem SQL Server—most likely from the gateway (Self-hosted Integration Runtime). Make sure the gateway is running and can connect to your SQL Server (you can test it using SSMS on the same machine). You can also try using Dataflow Gen2 as a workaround, which sometimes avoids these connection issues.
Regards,
Mehrdad Abdollahi