Skip to content

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 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:

Azure Template snippet
{
  "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:

Azure Template snippet
{
  "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:

Azure Bicep snippet
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:

Azure Bicep snippet
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:

br/public:avm/res/sql/server:<version>

To use the latest version:

br/public:avm/res/sql/server:0.9.1

Configure with Azure CLI#

Azure CLI snippet
az sql server ad-admin create -s '<server_name>' -g '<resource_group>' -u '<user_name>' -i '<object_id>'

Configure with Azure PowerShell#

Azure PowerShell snippet
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.

Comments