How can I send data from an Azure Function App using an SQL Trigger to an on-prem SQL Server through a hybrid connection?

Question

I'm trying to set up an Azure Function App with an SQL trigger that sends data to an on-prem SQL Server through a hybrid connection. I've gone through quite a lot online but I can't seem to get it to connect to my on-prem SQL Server.

First of all, the SQL trigger only available on Linux with Python version 3.10. When I try using a Windows OS (since most of the examples I see use SqlClient to connect to an on-prem SQL Server), I don't see the SQL Trigger option listed, only SQL Inbound and SQL Outbound.

Second of all, with the Linux OS, I am able to set up the trigger and receive the changes from the table, but no matter which connection string I try I cannot successfully connect to the on-prem SQL Server. Yes it allows remote connections and the tcp/ip configuration is set correctly as far as I can tell.

I am also concerned about long-term support since this trigger is only supported in an older version of Python with Linux. Is this something that is going to continue to be supported or is there a better, more updated way of doing this?

Thank you!

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

You are running into a few key challenges that stem from both azure's platform limitations and the complexity of hybrid connectivity.

1. SQL Trigger and Platform Limitations

• You're correct that SQL Trigger is only available on Linux with Python 3.10.
• Windows OS only supports SQL Input (Inbound) and SQL Output (Outbound), not SQL Triggers.
• This is a limitation of the Azure Functions Extension for SQL, which uses Change Tracking to listen for updates.
• Future Support Concerns: The fact that this is tied to an older version of Python (3.10) is concerning for long-term support. Microsoft hasn't officially stated plans to expand SQL Triggers to Windows or other runtimes yet.

2. Connecting to On-Prem SQL Server via Hybrid Connection

• Hybrid Connections in Azure App Service do NOT support TCP connections directly (only HTTP-based connections). This is likely why your SQL connection fails.
• SQL Server requires a direct TCP connection (port 1433), which isn't supported by Hybrid Connections.
• Even though remote connections are enabled and TCP/IP is configured correctly on your SQL Server, Hybrid Connections won't allow a direct SQL connection because it doesn't proxy TCP.

---

3. How to Successfully Connect Your Azure Function to OnPrem SQL

Since Hybrid Connections won't work, here are your best alternatives:

Option 1: Use an VNet with a VPN or ExpressRoute

• Why? A VNet Integration with a Site-to-Site VPN or ExpressRoute allows your Azure Function to communicate with your on-prem SQL Server as if it were on the same network. Steps:
  1. Deploy an Azure Virtual Network Gateway.
  2. Set up a Site-to-Site VPN (or ExpressRoute if you need better performance).
  3. Integrate your Azure Function with the VNet (using Regional VNet Integration, not the older Gateway-required one).
  4. Modify your Azure Function’s connection string to use the internal private IP of your on-prem SQL Server.

Option 2: Use an OnPrem Data Gateway

• Why? The On-Premises Data Gateway acts as a bridge between Azure and your on-prem SQL Server.
• How?
  1. Install the OnPrem Data Gateway on a machine inside your network.
  2. Register the gateway with Azure Data Factory (or Power Automate).
  3. Configure the gateway in Azure Logic Apps or an Azure API App, and then call it from your Function App.

Option 3: Use a Self-Hosted Function App

• If you must use the SQL Trigger with direct access to on-prem SQL, host the Azure Function on a self-managed VM or Kubernetes (AKS with VNet).
• This allows the function to be inside the same network as your SQL Server, bypassing Azure’s networking restrictions.

---

4. Recommended Approach

Given your concerns about long-term support and connectivity, I strongly recommend Option 1 (VNet Integration with VPN/ExpressRoute). This is:

1. Fully supported
2. Secure and scalable

More future-proof than the Hybrid Connection approach

If VPN/ExpressRoute isn’t an option, then Option 2 (OnPremises Data Gateway) is your next best choice.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.