Use AAD authentication with SQL databases#
Security · SQL Database · Rule · 2020_06
Use Azure Active Directory (AAD) authentication with Azure SQL databases.
Description#
Azure SQL Database offer two authentication models, Azure Active Directory (AAD) and SQL authentication. AAD authentication supports centralized identity management in addition to modern password protections. Some of the benefits of AAD 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 AAD authentication.
Recommendation#
Consider using Azure Active Directory (AAD) 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 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.
Azure AD 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#
- Use modern password protection
- Configure and manage Azure AD authentication with Azure SQL
- Using multi-factor Azure Active Directory authentication
- Conditional Access with Azure SQL Database and Azure Synapse Analytics
- Azure AD-only authentication with Azure SQL
- Azure Policy for Azure Active Directory only authentication with Azure SQL
- Azure deployment reference