Microsoft AzureOverview
Use Microsoft Entra authentication for authentication with SQL Database to manage credentials in a single place.
Rationale
Microsoft Entra authentication is a mechanism for connecting to Microsoft Azure SQL Database and SQL Data Warehouse using identities in the Microsoft Entra ID directory. With Entra ID authentication, the identities of database users and other Microsoft services can be managed in one central location. Central ID management provides a single place to manage database users and simplifies permission management.
- It provides an alternative to SQL Server authentication.
- Helps stop the proliferation of user identities across database servers.
- Allows password rotation in a single place.
- Customers can manage database permissions using external (Entra ID) groups.
- It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Microsoft Entra.
- Entra ID authentication uses contained database users to authenticate identities at the database level.
- Entra ID supports token-based authentication for applications connecting to SQL Database.
- Entra ID authentication supports ADFS (domain federation) or native user/password authentication for a local Active Directory without domain synchronization.
- Entra ID supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA). MFA includes strong authentication with a range of easy verification options — phone calls, text messages, smart cards with pins, or mobile app notifications.
Impact
This will create administrative overhead with the user account and permission management. For further security on these administrative accounts, you may want to consider licensing that supports features like Multi-Factor Authentication.
Default Value
Entra ID Authentication for SQL Database/Server is not enabled by default.
Additional Information
Assigning an Administrator in Entra ID is just the first step. When using Entra ID for central authentication, there are many other groups and roles that need to be configured based on the needs of your organization. The How-to Guides should be used to determine what roles should be assigned and what groups should be created to manage permissions and access to resources.
References
- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure
- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication
- https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserveractivedirectoryadministrator?view=azurermps-5.2.0
- https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-identity-management#im-1-use-centralized-identity-and-authentication-system
- https://docs.microsoft.com/en-us/cli/azure/sql/server/ad-admin?view=azure-cli-latest#az_sql_server_ad_admin_list
Remediation guidance
From Azure Console
- Open the SQL server using the
Open in Azurebutton - Under Settings, click
Microsoft Entra ID - Click
Set admin - Select an admin
- Click
Select - Click
Save
Using Azure PowerShell
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DisplayName "<displayName>"
From Azure CLI
az sql server ad-admin create --resource-group <resourceGroupName> --server <serverName> --display-name <displayName> --object-id <userObjectID>
Multiple Remediation Paths
Azure
SERVICE-WIDE (RECOMMENDED when many resources are affected): Assign Azure Policy initiatives at management group/subscription scope and trigger remediation tasks.
az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>
ASSET-LEVEL: Apply the resource-specific remediation steps above to the listed non-compliant resources.
PREVENTIVE: Embed Azure Policy checks into landing zones and IaC workflows to block or auto-remediate drift.
References for Service-Wide Patterns
- Azure Policy overview: https://learn.microsoft.com/en-us/azure/governance/policy/overview
- Azure Policy remediation: https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources
- Azure Policy initiative structure: https://learn.microsoft.com/en-us/azure/governance/policy/concepts/initiative-definition-structure
Operational Rollout Workflow
Use this sequence to reduce risk and avoid repeated drift.
1. Contain at Service-Wide Scope First (Recommended)
- Azure: assign policy initiatives at management group/subscription scope and run remediation tasks.
az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>
2. Remediate Existing Affected Assets
- Execute the control-specific Console/CLI steps documented above for each flagged resource.
- Prioritize internet-exposed and production assets first.
3. Validate and Prevent Recurrence
- Re-scan after each remediation batch.
- Track exceptions with owner and expiry date.
- Add preventive checks in IaC/CI pipelines.
Query logic
These are the stored checks tied to this control.
Azure SQL Servers without Entra admin
Connectors
Covered asset types
Expected check: eq []
{
sqlServers(
where: {
NOT: { entraAdministrator: { administratorType: "ActiveDirectory" } }
}
) {
...AssetFragment
}
}
