Here I would like to list down the different ways to connect from Azure to On-Premise SQL Server.
We can create a site-to-site VPN for connecting from Azure to On-Premise. Here the VPN device on Azure takes care of transmitting the request to the On-Premise network.
Azure Data Factory
Azure Data Factory requires running an Integration Runtime service in the On-Premise machine to make the connectivity happen. It also requires Outbound port opening from On-Premise machine if there is any Outbound connection required back to the Azure SQL.
Azure Hybrid Connections
Hybrid Connections are created within the App Service.
Azure Service Bus Relays
Relays create endpoints on the On-Premise application which can be accessed by the Outside World.
Express Route is a highly secured option as it creates a new connectivity other than Public Internet.
On-Premises Data Gateway
If you are using Logic Apps, Power BI then you can rely on On-Premise Data Gateway. This involves installing the On-Premise Data Gateway on Azure & On-Premise too.
Depending on your Network Security guidelines choose the appropriate one.