Use Entra ID authentication with SQL databases

March 21, 2025 ยท View on GitHub

SYNOPSIS

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 to ActiveDirectory.
  • 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 to ActiveDirectory.
  • 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 to ActiveDirectory.
  • 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 to ActiveDirectory.
  • 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. 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.