Goal: Give developers with dynamic/public IPs secure, private access to Azure SQL while keeping the public endpoint available for static-IP users and existing web apps.
This is the full, battle-tested guide based on a real implementation (including every error we hit and how we fixed it).
Architecture Overview
New Virtual Network (or existing one)
VPN Gateway (Point-to-Site) with Microsoft Entra ID authentication
Private Endpoint for Azure SQL
Azure DNS Private Resolver (so VPN clients can resolve the private endpoint correctly)
Hybrid access: VPN users → private path, static-IP users → public path
No downtime for existing web apps or the SQL server during setup.
1. Create / Prepare the Virtual Network
Azure Portal → Virtual networks → Create (or use existing).
Choose an address space that doesn’t conflict with anything (example used: whatever was already present, or 10.50.0.0/16).
Create these subnets:
Subnet Name | Example Range | Purpose |
|---|
GatewaySubnet | 10.x.1.0/24 | Required for VPN Gateway |
PrivateEndpointSubnet | 10.x.2.0/24 | SQL Private Endpoint |
snet-dns-inbound | 10.x.10.0/28 | Dedicated for DNS Private Resolver (must be empty) |
Important: The DNS inbound subnet must be empty. You cannot put a Private Endpoint or any other NIC in it.
2. Create the VPN Gateway
Virtual network gateways → Create
Settings:
Deploy (takes 25–45 minutes).
3. Configure Point-to-Site with Microsoft Entra ID
3.1 Create the App Registration (Custom Audience)
Microsoft Entra ID → App registrations → New registration
Name it something clear (e.g. AzureVPN-SQL-Access)
Supported account types: Accounts in this organizational directory only
Register.
Go to Expose an API:
Set Application ID URI if needed (Azure will suggest api://<client-id>)
Add a scope (e.g. name = p2s-vpn, Admins only, Enabled)
+ Add a client application
Go to Enterprise applications → find your app:
3.2 Configure the Gateway
VPN Gateway → Point-to-site configuration → Configure now
Settings:
Save → Download VPN client.
4. Create the Azure SQL Private Endpoint
Go to your Azure SQL Server (logical server) → Networking → Private access
+ Private endpoint
Choose:
Create and approve the connection if required.
Public access can stay Enabled for static-IP developers.
5. Fix DNS Resolution for VPN Clients (Critical Step)
By default, P2S clients resolve *.database.windows.net to the public IP. We need them to resolve to the private IP.
5.1 Deploy Azure DNS Private Resolver
Create a new empty subnet (snet-dns-inbound, /28 minimum).
Search for DNS Private Resolvers → Create
Select your VNet
On the Inbound Endpoints tab → Add endpoint → choose the empty snet-dns-inbound subnet
Create and note the private IP of the inbound endpoint (e.g. 10.x.10.4)
Make sure the Private DNS zone privatelink.database.windows.net is linked to this VNet.
5.2 Update the VPN Client Profile
Download a fresh VPN client package from the gateway.
Extract it and open AzureVPN\azurevpnconfig.xml (or azurevpnconfig_aad.xml).
Replace the <clientconfig> section with:
XML
<clientconfig>
<dnsservers>
<dnsserver>YOUR-INBOUND-ENDPOINT-IP</dnsserver>
</dnsservers>
<dnssuffixes>
<dnssuffix>.database.windows.net</dnssuffix>
<dnssuffix>.privatelink.database.windows.net</dnssuffix>
</dnssuffixes>
</clientconfig>
Save the file.
6. Connect & Test
Install Azure VPN Client from the Microsoft Store (if not already installed).
Import the modified XML.
Connect (sign in with Entra ID).
Test DNS:
PowerShell
Resolve-DnsName yourserver.database.windows.net
You should now see the private IP of the Private Endpoint.
Connect with SSMS / Azure Data Studio using the normal FQDN (yourserver.database.windows.net).
Common Errors & Fixes
Error | Cause | Fix |
|---|
AADSTS650057: Invalid resource | Audience mismatch or missing client application authorization | Ensure you added c632b3df-fb67-4d84-bdcf-b95ad541b5c8 under Expose an API → Add a client application, and re-download the profile |
AADSTS500011 | App not properly exposed / user not assigned | Check Application ID URI, Enterprise Application exists, and user/group is assigned |
nslookup returns public IP | VPN client not using the Private Resolver | Add the inbound endpoint IP to the XML as shown above |
Conflict when creating inbound endpoint | Tried to use a subnet that already has a Private Endpoint NIC | Create a brand-new empty /28 subnet |
Final Notes
Developers always use the public FQDN (server.database.windows.net), never the private IP or privatelink name.
Existing web apps and storage continue working unchanged.
You can later move web apps to private endpoints if desired.
Re-download and re-import the VPN profile any time you change Audience, Issuer, or DNS settings.
This setup gives you a clean, secure, and maintainable way for dynamic-IP developers to reach Azure SQL privately while preserving existing public access.
