How to route Azure MSSQL queries over vnet

Question

I've got a vnet in Azure with a private endpoint connection configured for A MSSQL server. There's a private DNS zone configured with an A record for the private endpoint connection. App Services connected to the vnet can connect to the private IP for the MSSQL private endpoint. VMs connected to the vnet resolve and connect to the private IP for the MSSQL private endpoint connection.

Can I make the SQL server resolve the FQDN for an external data source over the vnet using the private DNS zone so that its connection works the same way App Services or VMs do?

I'm trying to create an external data source and an external table in DATABASE_A to query DATABASE_B.TABLE1 between two databases on the same MSSQL server.

-- using DATABASE_A
CREATE EXTERNAL DATA SOURCE AppADataSrc
WITH
(
    TYPE = RDBMS,
    LOCATION = 'my-sqlserver.database.windows.net',
    DATABASE_NAME = 'DATABASE_B',
    CREDENTIAL = MyDBScopedCredential,
);
     
CREATE EXTERNAL TABLE [dbo].[Table1]
    ( [EntityId] [int] NOT NULL,
        [GrpId] [char](36) NOT NULL)
    WITH
    ( DATA_SOURCE = AppADataSrc);

Rather than have it connect to the other database (DATABASE_B) on the same server using the public ip address, I want it to connect to the database over a private connection. Using the private ip address in the LOCATION property CREATE EXTERNAL DATA SOURCE statement results in a timeout when querying the external table. If I turn on the "Allow Azure services and resources to access this server", the query on the public IP address works. However, I'd rather not open the SQL server to all other Azure services.

I'm finding plenty of documentation about connecting to the private endpoint for the MSSQL server over the vnet with other services. I'm not finding much for connecting from an Azure MSSQL server to a private endpoint.

score 0 · Answer 1 · answered Jan 20 '22 at 09:25

Private Endpoints are a one way connection, from the vNet to the resource, they unfortunately don't work the other way round. For some services you can also join the service to the vNet, such as with App Service. App Service has Private Endpoints to have traffic from your vNet use the private IP to get to the App Service, but it also has vNet join which allows traffic from your app service to go over the private network. Unfortuantely the SQL PaaS service doesn't offer vNet join.

You can use Azure SQL Managed instance to join the vNet, which will do what you want, but that is more complex and expensive.

How to route Azure MSSQL queries over vnet

1 Answers1