Applies toAzure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VMs; hybrid Active Directory with Entra Connect
Part 1: Identity
Map on-premises principals
| On-premises | Azure SQL target |
|---|---|
| AD user logins | Entra ID users (synced via Entra Connect) |
| AD group logins | Entra ID groups (recommended: grant to groups) |
| Service accounts (apps) | Managed identities (App Service, VMs, AKS workload identity) |
| SQL logins | Keep only where needed (legacy apps); rotate passwords; store in Key Vault |
sa / sysadmin use | Entra admin group on the server; PIM for elevation |
-- Azure SQL Database / MI: create users from Entra principals
CREATE USER [sql-sales-readers] FROM EXTERNAL PROVIDER; -- Entra group
ALTER ROLE db_datareader ADD MEMBER [sql-sales-readers];
CREATE USER [app-sales-api] FROM EXTERNAL PROVIDER; -- managed identity name
ALTER ROLE db_datawriter ADD MEMBER [app-sales-api];
On MI you can also create server-level logins: CREATE LOGIN [sql-dba-team] FROM EXTERNAL PROVIDER;
Apps that only speak Windows authentication
- Azure SQL MI supports Windows authentication for Entra principals using Kerberos (incoming trust-based flow for hybrid-joined clients, or modern flow for Entra-joined clients). Legacy apps can keep integrated security with configuration changes.
- SQL Server on Azure VMs: join the VMs to AD (extend AD DS to Azure or use Entra Domain Services) — nothing changes for the app.
Connection strings
Server=tcp:sqlsrv-prod.database.windows.net,1433;Database=Sales;Authentication=Active Directory Default;Encrypt=True;
Active Directory Default uses managed identity in Azure and the developer's sign-in locally — no passwords.
Part 2: Networking
| Target | Network model |
|---|---|
| Azure SQL Database | Public endpoint (disable) + private endpoint in a spoke |
| Azure SQL MI | VNet-injected in a dedicated subnet (delegated); optional private endpoint for other VNets |
| SQL Server on VM | Normal VM NIC in a subnet |
DNS
| Service | Private DNS zone |
|---|---|
| Azure SQL Database | privatelink.database.windows.net |
| Azure SQL MI | MI has its own DNS zone (<mi>.<dnszone>.database.windows.net resolves to the MI subnet IP); private endpoints use privatelink.<dnszone>.database.windows.net |
On-premises clients must resolve these names to private IPs: conditional forwarders → Azure DNS Private Resolver inbound endpoint.
Firewall and ports
- Azure SQL DB via private endpoint: 1433.
- MI: 1433 (and redirect connection type 11000–11999 within VNets for better performance; proxy mode uses 1433 only).
- Allow app subnets only; NSGs on the MI subnet are managed with the required service rules.
Test before cutover
Resolve-DnsName sqlsrv-prod.database.windows.net # must return a 10.x private IP
Test-NetConnection sqlsrv-prod.database.windows.net -Port 1433
Warning
Don't leave the public endpoint open "temporarily" during migration with a broad IP rule. Set up the private endpoint and DNS first; a short delay is better than an exposed production database.