Azure SQL Database With Both Public Access and Private Endpoint from Azure Data Factory

Question

I have an Azure SQL Server setup with Public access from "Selected networks". There are a handful of subnets allowed to access the database along with another set of public IPs for our company VPN, and all of the connections are working fine.

The problem occurs when trying to access the database through a private endpoint created in Azure Data Factory. In the Data Factory we have an Azure Integration Runtime with a Managed Virtual Network, and we are creating a Managed Private Endpoint to connect to the Azure SQL Server. Once we approve the Private Endpoint for the SQL Server, our other applications in our subnets can no longer communicate with the database. The Data Factory can communicate fine. We belive this may have to do with DNS resolution and changes that occured when creating the Managed Private Endpoint, but I have not found a solution yet as to what changes to make to fix the issue and allow both the Public access and Private Endpoint working at the same time.

Answer 1

Hi @J Mod Have you created the Private DNS Zone for Azure SQL and linked it to the VNet that all other serrvices exist? When a private endpoint for Azure SQL is enabled then the server is accessible from the rest of private resources through FQDN only and not IP address or anything else.

Let me know if this can solve your problem otherwise inform me accordingly.

Looking forward to your reply,

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards,

Answer 2

Hi @J Mod

Thanks for the question and using MS Q&A platform.

As I understand you are facing a issue with Azure SQL Database and Private Endpoint could be related to DNS resolution. When you create a Private Endpoint, it creates a DNS zone for the Private Endpoint in your virtual network. This DNS zone overrides the public DNS zone for the Azure SQL Server. As a result, the public endpoint of the Azure SQL Server is no longer accessible from the subnets that are not allowed to access the Private Endpoint.

To resolve this issue, you can create a DNS zone for the Azure SQL Server in your virtual network and add a CNAME record that points to the public endpoint of the Azure SQL Server. This will allow the subnets that are not allowed to access the Private Endpoint to access the Azure SQL Server through the public endpoint.

Here are the steps to create a DNS zone and add a CNAME record:

1. In the Azure portal, navigate to your virtual network and select "DNS zones" from the left-hand menu.
2. Click on "Add DNS zone" and enter the name of your Azure SQL Server.
3. Once the DNS zone is created, click on it and select "Add record set".
4. Enter the name of the CNAME record and select "CNAME" as the record type.
5. In the "Canonical name" field, enter the public endpoint of your Azure SQL Server.

After adding the CNAME record, you should be able to access the Azure SQL Server through the public endpoint from the subnets that are not allowed to access the Private Endpoint.

I hope this helps! Let me know if you have any further questions or concerns.

Answer 3

Hello @J Mod

Based on the information you provided, it is possible that the issue is related to DNS resolution. When you create a private endpoint for your Azure SQL Server, it creates a private DNS zone that resolves the server's Fully Qualified Domain Name (FQDN) to the private IP address of the server.

This private DNS zone is linked to the virtual network where the private endpoint is created.

To allow both public access and private endpoint working at the same time, you need to configure DNS resolution for both scenarios.

If you are connecting to the private endpoint from a VNet, you can use the same private zone that was created with the Azure SQL Database. If you are connecting to the private endpoint from a branch (Site-to-site VPN, Point-to-site VPN, or ExpressRoute), you need to use on-premises DNS resolution.

In your case, it is possible that the DNS resolution for your subnets is not configured correctly after creating the private endpoint.

You can try the following steps to troubleshoot the issue:

1. Check if the private DNS zone is linked to the virtual network where the private endpoint is created. You can do this by going to the virtual network's DNS settings and checking if the private DNS zone is listed under the DNS servers.
2. Check if the private DNS zone is resolving the server's FQDN to the private IP address. You can do this by running an nslookup command from a virtual machine in the virtual network. For example, you can run the following command: nslookup yourserver.privatelink.database.windows.net. If the private DNS zone is configured correctly, the command should return the private IP address of the server.
3. Check if the DNS resolution for your subnets is configured correctly. You can do this by checking the DNS settings for your subnets and making sure that they are using the same DNS servers as the virtual network where the private endpoint is created. If you are still facing issues after trying these steps, you can try contacting Microsoft support for further assistance.

I hope this helps you further:

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.