SQL Server Active Directory Admin is not configured Affecting Database service in Azure

Snyk CCSS

IAM/ Usage

Is your environment affected by this misconfiguration?

In a few clicks we can analyze your entire application and see what components are vulnerable in your application, and suggest you quick fixes.

Test your applications

Frameworks

CIS-Azure

Snyk IDSNYK-CC-00702
creditSnyk Research Team

Report a new misconfiguration Found a mistake?

Description

Azure Active Directory authentication for SQL Database uses Azure AD identities, providing an alternative to SQL Server authentication. This helps centralize permission management, reduces proliferation of identities, and eliminates the need to store more passwords.

How to fix?

SQL Server Active Directory Admin should be configured.

Example Configuration

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "resources": [
      {
        "type": "Microsoft.Sql/servers",
        "apiVersion": "2020-02-02-preview",
        "name": "allowed702",
        "location": "West Europe",
        "properties": {
          "administratorLogin": "4dm1n157r470r",
          "administratorLoginPassword": "4-v3ry-53cr37-p455w0rd",
          "version": "12.0"
        }
      },
      {
        "type": "Microsoft.Sql/servers/administrators",
        "apiVersion": "2020-02-02-preview",
        "name": "allowed702/activeDirectory",
        "location": "West Europe",
        "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'allowed702')]"
        ],
        "properties": {
          "administratorType": "ActiveDirectory",
          "login": "sqladmin",
          "sid": "19f30f3a-1b04-4a63-99d3-fc482217efba",
          "tenantId": "[subscription().tenantId]"
        }
      },
        ]
  }

Configure an azurerm_sql_active_directory_administrator with the server_name attribute set to the name of the SQL server.

Example Configuration

resource "azurerm_sql_server" "allowed" {
  name                         = "allowed702"
  resource_group_name          = azurerm_resource_group.example702.name
  location                     = azurerm_resource_group.example702.location
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
}

resource "azurerm_sql_active_directory_administrator" "allowedadmin" {
  server_name         = azurerm_sql_server.allowed.name
  resource_group_name = azurerm_resource_group.example702.name
  login               = "sqladmin"
  tenant_id           = data.azurerm_client_config.current.tenant_id
  object_id           = data.azurerm_client_config.current.object_id
}