Use Entra ID authentication with SQL databases#
Security · SQL Database · Rule · 2020_06 · Critical
Use Entra ID authentication with Azure SQL databases.
Description#
Azure SQL Database offer two authentication models, Entra ID (previously known as Azure AD) and SQL authentication. Entra ID authentication supports centralized identity management in addition to modern password protections. Some of the benefits of Entra ID authentication over SQL authentication including:
- Support for Azure Multi-Factor Authentication (MFA).
- Conditional-based access with Conditional Access.
It is also possible to disable SQL authentication entirely and only use Entra ID authentication.
Recommendation#
Consider using Entra ID authentication with SQL databases. Additionally, consider disabling SQL authentication.
Examples#
Configure with Azure template#
To deploy logical SQL Servers that pass this rule:
- Set the
properties.administrators.administratorType
toActiveDirectory
. - Set the
properties.administrators.login
to the administrator login object name. - Set the
properties.administrators.sid
to the object ID GUID of the administrator user, group, or application.
For example:
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2022-11-01-preview",
"name": "[parameters('name')]",
"location": "[parameters('location')]",
"properties": {
"publicNetworkAccess": "Disabled",
"minimalTlsVersion": "1.2",
"administrators": {
"azureADOnlyAuthentication": true,
"administratorType": "ActiveDirectory",
"login": "[parameters('adminLogin')]",
"principalType": "Group",
"sid": "[parameters('adminPrincipalId')]",
"tenantId": "[tenant().tenantId]"
}
}
}
Alternatively, you can configure the Microsoft.Sql/servers/administrators
sub-resource.
To deploy Microsoft.Sql/servers/administrators
sub-resources that pass this rule:
- Set the
properties.administratorType
toActiveDirectory
. - Set the
properties.login
to the administrator login object name. - Set the
properties.sid
to the object ID GUID of the administrator user, group, or application.
For example:
{
"type": "Microsoft.Sql/servers/administrators",
"apiVersion": "2022-02-01-preview",
"name": "[format('{0}/{1}', parameters('name'), 'ActiveDirectory')]",
"properties": {
"administratorType": "ActiveDirectory",
"login": "[parameters('adminLogin')]",
"sid": "[parameters('adminPrincipalId')]"
},
"dependsOn": [
"server"
]
}
Configure with Bicep#
To deploy logical SQL Servers that pass this rule:
- Set the
properties.administrators.administratorType
toActiveDirectory
. - Set the
properties.administrators.login
to the administrator login object name. - Set the
properties.administrators.sid
to the object ID GUID of the administrator user, group, or application.
For example:
resource server 'Microsoft.Sql/servers@2022-11-01-preview' = {
name: name
location: location
identity: {
type: 'SystemAssigned'
}
properties: {
publicNetworkAccess: 'Disabled'
minimalTlsVersion: '1.2'
administrators: {
azureADOnlyAuthentication: true
administratorType: 'ActiveDirectory'
login: adminLogin
principalType: 'Group'
sid: adminPrincipalId
tenantId: tenant().tenantId
}
}
}
Alternatively, you can configure the Microsoft.Sql/servers/administrators
sub-resource.
To deploy Microsoft.Sql/servers/administrators
sub-resources that pass this rule:
- Set the
properties.administratorType
toActiveDirectory
. - Set the
properties.login
to the administrator login object name. - Set the
properties.sid
to the object ID GUID of the administrator user, group, or application.
For example:
resource sqlAdministrator 'Microsoft.Sql/servers/administrators@2022-02-01-preview' = {
parent: server
name: 'ActiveDirectory'
properties: {
administratorType: 'ActiveDirectory'
login: adminLogin
sid: adminPrincipalId
}
}
Configure with Azure Verified Modules
A pre-validated module supported by Microsoft is available from the Azure Bicep public registry. To reference the module, please use the following syntax:
To use the latest version:
Configure with Azure CLI#
az sql server ad-admin create -s '<server_name>' -g '<resource_group>' -u '<user_name>' -i '<object_id>'
Configure with Azure PowerShell#
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName '<resource_group>' -ServerName '<server_name>' -DisplayName '<user_name>'
Notes#
In newer API versions the properties.administrators
property can be configured.
Entra ID authentication can also be configured using the Microsoft.Sql/servers/administrators
sub-resource.
If both the properties.administrators
property and Microsoft.Sql/servers/administrators
are set,
the sub-resource will override the property.
Links#
- SE:05 Identity and access management
- IM-1: Use centralized identity and authentication system
- Configure and manage Microsoft Entra authentication with Azure SQL
- Using Microsoft Entra multi-factor authentication
- Conditional Access with Azure SQL Database and Azure Synapse Analytics
- Microsoft Entra-only authentication with Azure SQL
- Azure Policy for Microsoft Entra-only authentication with Azure SQL
- Azure deployment reference