Self Hosted IR Connecting to SQL Managed Instance via ADF

Question

We have installed/configured a Self Hosted Integration Runtime to connect/ingest data from a test On-Prem resource. ADF can see the On-Prem source at design time, the destination is a SQL Manged Instance database.

On testing the Managed Instance linked service connection using the SHIR it reports, actual details are masked.Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '<MI name public end point>.database.windows.net,3342', Database: '<db>', User: '<dblogin>'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=22; handshake=14986;

The wait operation timed out

The AutoResolveIntegrationRuntime connects to the linked service without a problem. How is the SHIR granted access (and if an example could be provided of this for reference) through the NSG/firewall? If possible I'd like to trace/identify the traffic concerned, see the traffic being blocked, before making any related changes. Would appreciate any guidance for both points.

Answer 1

Hi S, Richard,

Thanks for posting your query on Microsoft Q&A!

Thank you Amira Bedhiafi for providing answer, in addition to Amira's answer I would like to include add few more details

Check Linked Service Configuration:

• Make sure your linked service configuration in ADF is correct. Confirm the server name, database name, and credentials are accurately inputted.
• Go to the ADF portal, navigate to Manage > Linked services, and review the configuration.

you must grant network access from your SHIR to the SQL MI. This involves two main steps: configuring the Azure SQL MI firewall and your on-premises or VNet firewall/NSG.

1. Configure Azure SQL Managed Instance (MI) Firewall:
   • Navigate to your Azure SQL MI in the Azure portal.
   • Go to the Networking blade under the Security section.
   • Ensure that the public endpoint is enabled.
   • Add a Firewall rule that allows inbound traffic from the public IP address of the machine hosting your SHIR. If the SHIR is on a machine in your corporate network, you need to use the public egress IP of your corporate network. If it's on an Azure VM, use the VM's public IP.
2. Configure Your On-Premises Firewall or Azure VNet NSG:
   • Create an outbound rule on your local or corporate firewall (or the NSG for the SHIR's subnet if it's on an Azure VM).
   • The rule must allow outbound traffic from the SHIR machine's IP address to the public IP address of your SQL MI.
   • The traffic must be allowed on the correct TCP ports. For SQL MI public endpoint, the port is typically 3342. The connection string you provided confirms this port is being used. Make sure this specific port is allowed.
   For more insights, check out these links:
   • Self-Hosted Integration Runtime Troubleshooting
   • Configure Public Endpoint on SQL Managed Instance

https://learn.microsoft.com/en-us/azure/data-factory/self-hosted-integration-runtime-troubleshoot-guide?tabs=data-factory

Please let us know if you are still experiencing the issue after following the above steps, so we can assist you further.

Thanks!

Kalyani

Answer 2

Hello Richard !

Thank you for posting on Microsoft Learn Q&A.

Your SHIR VM just needs to reach your MI public endpoint over TCP 3342 and be allow-listed on the MI firewall. The pre-login handshake timeout almost always means something on the SHIR path (corporate firewall, proxy, TLS inspection, or DNS) is blocking/altering 3342 traffic.

MI public data endpoint is *.database.windows.net,3342 (MI uses 3342 for public; 1433 is for VNet-local) and it needs to be enabled (Portal: MI then Networking then Public endpoint)

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/frequently-asked-questions-faq?view=azuresql

Your org firewall or proxy must allow outbound TCP 3342 from the SHIR machine to *.database.windows.net. The MI public endpoint is fronted by Azure SQL gateways, you connect by FQDN, not a fixed IP.)

https://docs.azure.cn/en-us/azure-sql/managed-instance/connectivity-architecture-overview

If you do SSL or TLS inspection, exclude this traffic since TLS break frequently causes pre-login handshake failures.