Forum Discussion
SQL Mirroring - Managed Private Endpoint
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
15 Replies
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.
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.
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.
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
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.
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,
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.
Hi v-venuppu,
The private DNS zone is correctly linked to the VNET hosting the SQL Private Endpoint and the A record points to the SQL's private IP. This is production system, its all LIVE so im quite sure all is working.
Anything else that i can try?
Thank you
Hi flatproject ,
Your DNS setup looks correct - the nslookup output confirms resolution via the private IP.
Next, ensure your Fabric workspace and SQL Server are in the same region (UK West), as Managed Private Endpoints don’t work cross-region.If regions match, delete and recreate the MPE in Fabric, then wait until its status is Approved and Succeeded.Retry creating the mirrored connection using the same Service Principal with required roles (##MS_ServerStateReader##, SELECT, ALTER ANY EXTERNAL MIRROR).If it still fails, clear the DNS cache in Fabric (recreate the connection after 15 min).These steps usually fix the connection denied / still using public route issue once DNS and permissions are already correct.Thank you.
Hi v-venuppu ,
I tried deleting and recreating the MPE and the mirrored connection a few times but im still getting the same error message
An exception occurred: DataSource.Error: Microsoft SQL: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied because Deny Public Network Access is set to Yes. For more information, see https://go.microsoft.com/fwlink/?linkid=2323206.Anything else I can try?
Thank you for the continuous support
Hi flatproject ,
As the Troubleshooting steps didn't helped in solving the issue, I would suggest to raise support ticket using Microsoft's official support ticket channel using below link:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
Thank you.
Hi v-venuppu ,
Thank you for your continuous support. I will raise a support ticket and update the thread here with hopefully a solution.
Thanks you
Hi v-venuppu ,
I have spoken to support and basically the documentation doesnt mention anywhere that in order for the Managed Private Endpoint and SQL mirroring to work you need to have
1) Public Access enabled
and also
2) "Allow Azure services and resources to access this server" checked.
Unfortunately this makes the product unusable for us.
Thanks again for your help.
Hi flatproject ,
Sorry for the Inconvenience..!!
I would suggest to post the issue under Issues forum using the link below:
Issues - Microsoft Fabric CommunityThank you.
