We have a couple of databaes on Azure which query back and forth between themselves using elastic query and also sp_execute_remote. We wanted to move these databases onto a VNET and provide a private endpoint for App Services to use it. That part proved relatively easy and we have our app service able to talk to these databases no problem. However it seems like when we established the connection and turned off public access to the database our external tables stopped being accessible.
Running a select * from externaltable now results in an error like this:
Msg 46823, Level 16, State 1, Line 1
Error retrieving data from xxxxxxxxxxxx.database.windows.net.yyyyyyyyyy. The underlying error message received was: 'Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).'.
I suspect that these requests are still being routed to the public endpoint and that SQL server is not observing the VNET routing to the other servers via the private endpoint. Is there a way to get this to work? For the app service we set WEBSITE_VNET_ROUTE_ALL but I don't imagine there is an exact parallel there for SQL server.
How can I enable vnet and private endpoints while still being able to do elastic query?
