Azure AD-only authentication#
Security · SQL Database · 2023_03
Ensure Azure AD-only authentication is enabled with Azure SQL Database.
Description#
Azure SQL Database supports authentication with SQL logins and Azure AD authentication. By default, authentication with SQL logins is enabled. SQL logins are unable to provide sufficient protection for identities.
Azure AD authentication provides:
- Strong protection controls including conditional access, identity governance, and privileged identity management.
- Centralized identity management with Azure AD.
Additionally you can disable SQL authentication entirely, by enabling Azure AD-only authentication.
Some features may have limitations when using Azure AD-only authentication is enabled, including:
- Elastic jobs
- SQL Data Sync
- Change Data Capture (CDC)
- Transactional replication
- SQL insights
Continue reading Limitations for Azure AD-only authentication in SQL Database.
Recommendation#
Consider using Azure AD-only authentication. Also consider using Azure Policy for Azure AD-only authentication with SQL Database.
Examples#
Azure AD-only authentication can be enabled in two different ways.
Configure with Azure template#
To deploy SQL Logical Servers that pass this rule:
- Set the
properties.administrators.azureADOnlyAuthentication
property totrue
.
For example:
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2022-05-01-preview",
"name": "[parameters('name')]",
"location": "[parameters('location')]",
"identity": {
"type": "SystemAssigned",
"userAssignedIdentities": {}
},
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]",
"administrators": {
"administratorType": "ActiveDirectory",
"azureADOnlyAuthentication": true,
"login": "[parameters('login')]",
"principalType": "[parameters('principalType')]",
"sid": "[parameters('sid')]",
"tenantId": "[parameters('tenantId')]"
}
}
}
Alternatively, you can configure the Microsoft.Sql/servers/azureADOnlyAuthentications
sub-resource.
To deploy Microsoft.Sql/servers/azureADOnlyAuthentications
sub-resources that pass this rule:
- Set the
properties.azureADOnlyAuthentication
property totrue
.
For example:
{
"type": "Microsoft.Sql/servers/azureADOnlyAuthentications",
"apiVersion": "2022-05-01-preview",
"name": "[format('{0}/{1}', parameters('name'), 'Default')]",
"properties": {
"azureADOnlyAuthentication": true
},
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('name'))]"
]
}
Configure with Bicep#
To deploy SQL Logical Servers that pass this rule:
- Set the
properties.administrators.azureADOnlyAuthentication
property totrue
.
For example:
resource logicalServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
name: name
location: location
identity: {
type: 'SystemAssigned'
userAssignedIdentities: {}
}
properties: {
administratorLogin: administratorLogin
administratorLoginPassword: administratorLoginPassword
administrators: {
administratorType: 'ActiveDirectory'
azureADOnlyAuthentication: true
login: login
principalType: principalType
sid: sid
tenantId: tenantId
}
}
}
Alternatively, you can configure the Microsoft.Sql/servers/azureADOnlyAuthentications
sub-resource.
To deploy Microsoft.Sql/servers/azureADOnlyAuthentications
sub-resources that pass this rule:
- Set the
properties.azureADOnlyAuthentication
property totrue
.
For example:
resource aadOnly 'Microsoft.Sql/servers/azureADOnlyAuthentications@2022-05-01-preview' = {
name: 'Default'
parent: logicalServer
properties: {
azureADOnlyAuthentication: true
}
}
Notes#
The Azure AD admin must be set before enabling Azure AD-only authentication. A managed identity is required if an Azure AD service principal (Azure AD application) oversees creating and managing Azure AD users, groups, or applications in the logical server.
Links#
- Use modern password protection
- Azure AD-only authentication with Azure SQL Database
- Configure and manage Azure AD authentication with Azure SQL Database
- Limitations for Azure AD-only authentication in SQL Database
- Azure Policy for Azure AD-only authentication with Azure SQL Database
- Azure deployment reference
- Azure deployment reference