SQL Database Entra Authentication

Quick reference for Azure SQL Database Entra authentication in post-deployment scenarios.

Prerequisites

Azure SQL Server must be configured with Entra-only authentication during provisioning. The signed-in user must be set as Entra admin:

@allowed(['User', 'Group', 'Application'])
param principalType string = 'User'

properties: {
  administrators: {
    administratorType: 'ActiveDirectory'
    principalType: principalType  // 'User' for interactive, 'Application' for CI/CD
    login: principalName
    sid: principalId
    tenantId: subscription().tenantId
    azureADOnlyAuthentication: true
  }
}

⚠️ Warning: Hardcoding principalType: 'User' causes UnmatchedPrincipalType errors when deploying from CI/CD with a service principal. Use a parameter instead.

Connection Patterns

Azure CLI (Recommended for Scripts)

⚠️ Warning: az sql db query requires the rdbms-connect extension. Install it first: az extension add --name rdbms-connect --yes

az sql db query \
  --server "$SQL_SERVER" \
  --database "$SQL_DATABASE" \
  --resource-group "$AZURE_RESOURCE_GROUP" \
  --auth-mode ActiveDirectoryDefault \
  --queries "SELECT 1"

Connection Strings

For .NET applications with managed identity:

Server=tcp:{server}.database.windows.net,1433;Database={database};Authentication=Active Directory Default;Encrypt=True;

Required packages:

• Microsoft.Data.SqlClient (v5.1.0+)
• Azure.Identity (for local development)

Database Roles

Grant Managed Identity Access

-- Create user from managed identity
CREATE USER [app-name] FROM EXTERNAL PROVIDER;

-- Grant standard application permissions
ALTER ROLE db_datareader ADD MEMBER [app-name];
ALTER ROLE db_datawriter ADD MEMBER [app-name];
ALTER ROLE db_ddladmin ADD MEMBER [app-name];

💡 Tip: The managed identity name matches the App Service or Container App name.

Verify Current Admin

az sql server ad-admin list \
  --server "$SQL_SERVER" \
  --resource-group "$AZURE_RESOURCE_GROUP"

References

• SQL Managed Identity Access
• EF Core Migrations
• Post-Deployment Guide