SQL Mirroring - Managed Private Endpoint

[flatproject]

I have a UKWest Azure SQL database in a vnet/subnet using a private link with public network access disabled.

I have created a trial capacity on Fabric so I can test the SQL mirroring and start using it with PowerBI.

On Fabric I have created a Workspace, assigned the trial capacity and created a Managed Private Endpoint successfully.

On Entra i have registered my App, and on SQL I have created the Login/User as such:

MASTER

-- CREATE LOGIN [fabric-mirror-sp] FROM EXTERNAL PROVIDER WITH OBJECT_ID = 'Object_Id';

-- ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [fabric-mirror-sp];

DB

CREATE USER [fabric-mirror-sp] FOR LOGIN [fabric-mirror-sp];

GRANT SELECT, ALTER ANY EXTERNAL MIRROR TO [fabric-mirror-sp];

Now, when i go to my Fabric workspace->New Item->Mirrored Azure SQL database, set up all the details for connecting using Service Principal, I get this error: 

Cannot connect to SQL Database: 'xxxx-sql-server.database.windows.net', Database: 'xxxx-db-live', Reason: Connection was denied since Deny Public Network Access is set to Yes. To connect to this server, 1. If you persist public network access disabled, please use Managed Virtual Network IR and create private endpoint. https://docs.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint; https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal-private; 2. Otherwise you can enable public network access, set "Public network access" option to "Selected networks" on Azure SQL Networking setting.

From the error message as far I understand, Fabric is still trying to connect using a public route which gets denied correctly, instead of using the Managed Private Endpoint.

Any ideas/help on how to proceed? 

Thanks

[v-venuppu]

Hi @flatproject ,

You don’t need to manually link Fabric’s Managed VNet to your Private DNS Zone - it’s Microsoft-managed and doesn’t appear in Azure’s VNet list.Just ensure your Private DNS Zone is correctly linked to the VNet hosting your SQL Private Endpoint, and that the A record points to the SQL’s private IP.If nslookup <your-sql-server>.database.windows.net from that VNet resolves to the private IP, Fabric will connect automatically. In short, correct DNS linkage inside Azure ensures Fabric’s MPE connects - no Fabric-side DNS link required.

Thank you.

[v-venuppu]

Hi @flatproject ,

You can’t directly see the Managed Private Endpoint (MPE) IP - it’s managed internally by Fabric, so the 401 error is expected.
Instead, confirm that your Private DNS Zone has an A record for your SQL server pointing to its private endpoint IP.Then, link this DNS zone to the Fabric managed VNet (shown under Virtual network links).You don’t need to add the MPE IP manually - Fabric resolves it automatically once the DNS zone is linked.Finally, validate with nslookup <your-sql-server>.database.windows.net from a VM in the same VNet; if it returns a private IP, retry the Fabric mirror setup - it should now connect privately.

Thank you.

[flatproject]

Hi @v-venuppu ,

I have confirmed that my Private DNS Zone has an A record for the SQL server pointing to its private endpoint IP. Under Virtual Network Links (in the Private DNS ), there is a link created but i'm not sure if its related to fabric.

If i click edit this is how it looks:

If i try to add a new Virtual Network Link, i dont get an option under Virtual Network to select the Fabric managed vnet.

Should i be able to see the Fabric VNET in the dropdown?

Thanks,

[v-venuppu]

Hi @flatproject ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991 for the prompt response.

Since your Managed Private Endpoint (MPE) shows Approved/Succeeded, the likely issue is DNS resolution. Ensure your SQL server has an A record in the Private DNS Zone( "privatelink.database.windows.net") pointing to the private IP of the MPE. Also, link the VNet hosting Fabric to this DNS zone. Test resolution from a VM in the same VNet using nslookup <your-sql-server>.database.windows.net-it should return the private IP. Confirm SQL Public Network Access = Disabled and the private endpoint shows Approved. Once DNS resolves correctly, retry the mirrored database connection via the Service Principal.

[flatproject]

Hi @v-venuppu and thanks for getting back to me.

My SQL server has an A record in the Private DNS Zone pointing to a private endpoint for keeping the traffic inside our vnet but not pointing also at the MPE. How can i find the private IP of the MPE to add it to the Recordset under "privatelink.database.windows.net"? If i go to the SQL Server, Networking, Private Access and click on the MPE created by Fabric i get a 401 error which i believe is expected because the MPE is managed by Azure. Also, if i go to the NEtwork Foundation and check all Private Endpoints, Pending Connections, Private Link Services I cant find the name of the MPE.

Any help much appreciated.

Thanks

[flatproject]

Hi @rohit1991 and thanks for getting back to me.

The managed private endpoint is approved and shows as approved and succeeded.

Fabric:

Azure Portal under SQL server networking private access:

It all still fails.

How can i check the DNS resolution?

I have checked the "Private DNS Zones" and i can see the "privatelink.database.windows.net" is there. Should i be checking something else as well? Maybe the Recordsets? Should something specific be in there?

Thanks again for your help.

[rohit1991]

Hi @flatproject 

The error occurs because Fabric is still trying to connect to your SQL Database through the public endpoint, which is blocked since public network access is disabled. Even though you’ve created a Managed Private Endpoint, it must be properly approved and linked on both Fabric and Azure SQL sides. Go to your Fabric workspace >>Manage connections and gateways >> Managed private endpoints, and ensure its status shows Approved. Then open your Azure Portal >> SQL Server >> Networking tab, and under Private endpoint connections, make sure the same endpoint appears and is approved there too. If it’s still pending, manually approve it. After approval, try setting up the mirrored SQL connection again using your Service Principal credentials that were granted access. If it still fails, verify DNS resolution to ensure Fabric is routing via the private IP instead of the public one. Once both sides are approved and connected, Fabric will securely communicate with SQL without needing public network access.