When having some on-premises data sources that one wants to access from Azure, is the "best practice" to create a data gateway, or is this becoming an outdated approach? I ask because I notice it relies on the "Gateway Cloud Service" which as I understand it is on its way out.
The actual requirement for me is only to be able to do a query from a functions app to the on-premises sql server. I'm not even sure this is possible through the data gateway.
Any and all feedback appreciated.
Take a look at hybrid connections: https://learn.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections
They are designed for what you want to do:
Basically, a tunnel is created between the SQL endpoint in your on-premise environment and a target endpoint for your Azure Function in the Azure environment. It uses HTTPS traffic, so it is easy on the on-premise firewall. To create the SQL endpoint, you'll need to install an agent called the 'Hybrid Connection Manager', which will act as a proxy between the Azure endpoint and the SQL server.
After the endpoints are established, you can connect to the Azure endpoint with your function, and it will be tunneled to the on-premise SQL (using the HCM).
Bear in mind that this will only work with SQL accounts, not with AD accounts. Also, the Azure Function must be hosted by a dedicated App Service Plan, not the consumption plan (alas).