question

TommyNewman-3774 avatar image
0 Votes"
TommyNewman-3774 asked TommyNewman-3774 commented

Azure SQL PaaS Server Private Endpoint Certificate

We have set up a PaaS SQL Server with no public access. It's connected via a Private Endpoint.
The network card has a private IP which we use to connect, and we have internal DNS on Windows Server to provide a suitable "A Record" for the IP.

Problem is, to connect to the database, I have to say TrustServerCertificate=yes in ODBC connection strings and in SSMS.
Would I just need to create an internal certificate for the "A record" I created so I can trust the certificate?

I looked in the Azure portal but was not able to see anything relating to certificates.
Look forward to hearing from you.

azure-sql-databaseazure-private-link
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GeethaThatipatri-MSFT avatar image
0 Votes"
GeethaThatipatri-MSFT answered TommyNewman-3774 commented

Hi, @TommyNewman-3774 Welcome to Microsoft Q&A Platform, and thanks for your query.
You can follow the tutorial below:
Tutorial: Connect to an Azure SQL server using an Azure Private Endpoint - Azure portal | Microsoft Docs
No need to deal with certificates and DNS records, you can just use the public server name and it will be resolved in the private IP address automatically.


Regards
Geetha


· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So I have a SQL PaaS Server in Azure, typically you connect to it using the server name which is a domain name that resolves to a public IP address. Now we have disabled public access, so we connect to it via a Private Endpoint.


Private Endpoints link the Azure Virtual Network (vnet), and our network config is done so the routing tables send the traffic to the correct location via SD-WAN etc. Connecting to the SQL Database is done via the server name, but that resolves to a public IP, which I can’t use, due to public access being disabled.


Enabling Private Endpoints creates this private DNS alias, if public access was still enabled then this alias would not exist.


So in Azure you can have Private DNS Zones which use Azure’s own DNS servers and services.
We don’t use Private DNS for the virtual network in production, we use our own DNS servers in AD.


If I do an nslookup on the server name you will see it has an alias with the zone I need to add.
When you do the nslookup for the main server name sql-server.datbase.windows.net, it resolves to the alias sql-server.privatelink.database.windows.net.


So by creating the zone in our DNS servers in AD, Azure will look at this zone return the private IP I need of 10.X.X.X for sql-server.privatelink.database.windows.net. Now because its an alias looking up the original server name will then eventually return my private IP.


The reason Microsoft do it this way is so if you wanted public access back again, Azure would add / remove this alias address providing you the correct IP you need.


Hope that helps.

1 Vote 1 ·

@TommyNewman-3774 Thanks for sharing the information here,
Thank you for visiting the Microsoft QA forums! Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Regards
Geetha

0 Votes 0 ·
TommyNewman-3774 avatar image TommyNewman-3774 GeethaThatipatri-MSFT ·

How do I mark my own answer as the correct answer?

0 Votes 0 ·

Morning,

When I do an nslookup I get the public IP information, not my private IP I need.

How do I get Azure DNS to resolve to my private IP?

Regards
Tommy

0 Votes 0 ·

@TommyNewman-3774 Please confirm if you tried as mentioned below
217351-image.png

Regards
Geetha


0 Votes 0 ·
image.png (122.9 KiB)

Hi yes, I have tried this but I get the same issue. We use custom DNS servers for the virtual network in Azure. Would that have anything to do with it?

0 Votes 0 ·

Looking online it seems I may have to create a new zone on my Windows internal DNS server with the SQL server hostname and IP.

The following video shows how to do it at 4 minutes 22 seconds. I will give this a try.

https://www.youtube.com/watch?v=rXbamGNz-xQ&ab_channel=JohnSavill%27sTechnicalTraining

0 Votes 0 ·

@TommyNewman-3774 It does it automatically when you disable public access. please give a try.

Regards
Geetha

0 Votes 0 ·
TommyNewman-3774 avatar image TommyNewman-3774 GeethaThatipatri-MSFT ·

I have had public access disabled all along, but due to the Azure vnet using custom DNS servers in Active Directory I'm pretty sure I need to create a new DNS zone as per the above. I will test and get back to you all.

0 Votes 0 ·