Public Endpoint and Azure SQL (Entra-only) Considerations

When using an Azure SQL server as part of a customer Azure Managed Application, the connection model differs from a storage account. Azure SQL can be configured for Entra-only authentication, which means that no SQL logins, keys, or connection strings are involved. Access is always based on Microsoft Entra ID tokens from the customer tenant.

This section outlines the trade-offs between using a public endpoint with Entra-only authentication versus configuring a private endpoint via VNet integration.

Public Endpoint + Entra-only Authentication:

Simplicity

Using a public endpoint with Entra-only authentication is straightforward:

• Azure SQL supports direct Microsoft Entra ID connections from Power BI and other Azure services.

• No secrets or keys need to be managed; authentication happens with access tokens from Entra ID.

Automation and Cost

• Low complexity and low cost: no VNet, DNS, or data gateways required.

• Automation friendly: identities and permissions can be provisioned through IaC templates.

• Managed identities from the Managed Application can be granted least-privilege contained users in the SQL database.

Security & Governance

• No storage keys or passwords: all access goes through Microsoft Entra ID.

• Governance can be strictly enforced by:

• Restricting access to customer-tenant identities only.

• Assigning roles to Managed Identities and service principals with fine-grained RBAC.

• Firewall rules on the SQL server can limit exposure to required IP ranges or Azure services.

Private Endpoint via VNet (Customer-managed)

Setup Complexity

Configuring a private endpoint for Azure SQL requires:

1. Creating a VNet and subnet for the private endpoint.

2. Integrating DNS so that the SQL server’s hostname resolves to the private IP.

3. Managing firewall, routing, and network security group (NSG) rules.

4. Deploying a VNet Data Gateway for Power BI connectivity, since Power BI cannot connect directly to private endpoints.

5. Integration complexity with the other components in the Managed Application.

Cost & Maintenance

• Higher cost due to additional networking infrastructure, DNS zones, and requirement for Power BI Premium/Fabric capacity to run the VNet Data Gateway.

• Ongoing maintenance overhead: ensuring gateway uptime, managing DNS resolution, and handling VNet peering or firewall updates.

Security & Governance

• Provides maximum isolation: SQL server is not exposed to the internet.

• Full alignment with strict compliance requirements.

• Governance at both the network layer (VNet, NSG, firewall) and identity layer (Entra-only authentication).

Argument Summary

1. Simplicity & Automation

• Public endpoint + Entra-only authentication is the simplest and most automation-friendly path.

• No secrets to manage, and no reliance on connection strings.

1. Security Trade-offs

• Public endpoints are internet-facing, but with Entra-only authentication and firewall rules, risks are minimized.

• Private endpoints remove internet exposure but at the cost of complexity, capacity requirements, and higher ongoing operations.

1. Governance Controls

• Public endpoint + Entra-only auth centralizes governance in Microsoft Entra ID.

• Private endpoint adds network-level governance for customers who require full isolation.

Bsure Insights Setup

• Azure SQL per customer: Each customer has a dedicated Azure SQL instance deployed in their subscription via the Managed Application.

• Entra-only authentication:

• Configure a Microsoft Entra admin on the SQL server.

• Create contained principals for:

• The Managed Application’s Managed Identity.

• The Power BI refresh identity (service principal).

• Assign only the necessary database roles (e.g., read-only for reporting).

• No keys, no passwords: all authentication is token-based.

Connectivity options:

• Public endpoint: Power BI can connect directly using Entra ID authentication, with no gateway required.

• Private endpoint: Requires VNet Data Gateway bound to Power BI Premium/Fabric capacity, adding complexity and cost but delivering maximum isolation.