Enabling Entra ID authentication in SQL Server on Azure VM modifies existing extension configurations

[Masahigo]

Describe the bug

We are managing our SQL Server on Azure VM setup through Terraform. Also the SQL Server IaaS extension configurations are part of the IaC.

resource "azurerm_mssql_virtual_machine" "sql_vm" {
  virtual_machine_id               = azurerm_windows_virtual_machine.sql_vm.id
  sql_connectivity_port            = 1433
  sql_connectivity_type            = "PRIVATE"
  sql_connectivity_update_username = var.sql_admin_username
  sql_connectivity_update_password = random_password.sql_connect_pw.result
  r_services_enabled               = false
  tags                             = var.tags

  storage_configuration {
    disk_type             = "NEW"
    storage_workload_type = "GENERAL"

   ...
  }

  sql_instance {
    collation                            = "Latin1_General_CI_AS"
    adhoc_workloads_optimization_enabled = false
    max_dop                              = 0
    max_server_memory_mb                 = 2147483647
    min_server_memory_mb                 = 0
  }

 ...
}

Now we have been taking Entra ID authentication into use in our environments based on this setup. It still seems to be the case that there is no Azure ARM provider API to enable this feature so we cannot use Terraform to enable it. The feature is part of the VM extension and managed through either Azure CLI and/or PowerShell.

We have noticed that after the feature is enabled through Azure CLI in an environment the SQL IaaS extension's existing configurations are affected. This causes configuration drift which has to be manually resolved afterwards.

Related command

# Validate Microsoft Entra authentication with a system-assigned managed identity
az sql vm validate-azure-ad-auth -n sqlvmname -g myresourcegroupname
# Should return: "Sql virtual machine sqlvmname is valid for Azure AD authentication."

# Enable Microsoft Entra authentication with a system-assigned managed identity
az sql vm enable-azure-ad-auth -n sqlvmname -g myresourcegroupname

# Verify that Entra ID is enabled
az sql vm show -n sqlvmname -g myresourcegroupname --expand '*' --query serverConfigurationsManagementSettings.azureAdAuthenticationSettings 

# should return:
# { 
#   "clientId": "" 
# }

Errors

Terraform detects configuration drift and needs to re-create the VM extension

# xxx.azurerm_mssql_virtual_machine.sql_vm must be replaced
-/+ resource "azurerm_mssql_virtual_machine" "sql_vm" {
      ~ id                               = "/subscriptions/../resourceGroups/../providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/<name-of-the-vm>" -> (known after apply)

      + sql_instance {
          + adhoc_workloads_optimization_enabled = false
          + collation                            = "Latin1_General_CI_AS" # forces replacement
          + instant_file_initialization_enabled  = false # forces replacement
          + lock_pages_in_memory_enabled         = false # forces replacement
          + max_dop                              = 0
          + max_server_memory_mb                 = 2147483647
          + min_server_memory_mb                 = 0
        }

        # (1 unchanged block hidden)
    }

As you can see there are several SQL Server instance specific configurations that have changed, for instance the SQL Server instance's collation.

Issue script & Debug output

not available

Expected behavior

Executing the Azure CLI commands should not lead to altering the other configurations of the VM extension. This causes configuration drift that has to be resolved separately.

Environment Summary

azure-cli 2.65.0 *

core 2.65.0 *
telemetry 1.1.0

Extensions:
bastion 0.2.4
containerapp 0.3.13
managementpartner 0.1.3
resource-graph 2.1.0
storage-preview 0.8.3

Dependencies:
msal 1.31.0
azure-mgmt-resource 23.1.1

Python location '/opt/homebrew/Cellar/azure-cli/2.65.0/libexec/bin/python'
Extensions directory '/Users/masimalmi/.azure/cliextensions'

Python (Darwin) 3.11.10 (main, Sep 7 2024, 01:03:31) [Clang 15.0.0 (clang-1500.3.9.4)]

Additional context

No response

[azure-client-tools-bot-prd]

Hi @Masahigo,

2.65.0 is not the latest Azure CLI(2.67.0).

If you haven't already attempted to do so, please upgrade to the latest Azure CLI version by following https://learn.microsoft.com/en-us/cli/azure/update-azure-cli.

[yonzhan]

Thank you for opening this issue, we will look into it.

[ebruersan]

Hello, this command should not cause any configuration changes like what you are seeing. Please open a case with Microsoft support so that our engineers can investigate.
I also want to point out that you can use the API to enable Entra authentication. Please see the documentation . You will need to set AADAuthenticationSettings.

[Masahigo]

@ebruersan Thanks for the tip!

Indeed, this works 🎉

resource "azapi_update_resource" "enable_sql_entra_id_auth" {
  type        = "Microsoft.SqlVirtualMachine/sqlVirtualMachines@2023-10-01"
  resource_id = <resource-id-of-the-azure-vm>

  body = jsonencode({
    properties = {
      serverConfigurationsManagementSettings = {
        azureAdAuthenticationSettings = { "clientId" : "" }
      }
    }
  })

  depends_on = [
    ...
  ]
}

But the exact configuration drift happens also with this approach. After executing Terraform the next time it shows that the same values have changed in the VM extension. And since this azapi_update_resource depends on the VM extension Terraform wants to re-create that as well

# azapi_update_resource.enable_sql_entra_id_auth must be replaced
-/+ resource "azapi_update_resource" "enable_sql_entra_id_auth" {
      ~ id                      = "/subscriptions/xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/xxx/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/<name-of-azure-vm>" -> (known after apply)
      ~ name                    = "<name-of-azure-vm>" -> (known after apply)
      ~ output                  = jsonencode({}) -> (known after apply)
      ~ parent_id               = "/subscriptions/xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/xxx" -> (known after apply)
      ~ resource_id             = "/subscriptions/xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/xxx/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/<name-of-azure-vm>" -> (known after apply) # forces replacement
        # (4 unchanged attributes hidden)
}

After letting Terraform sort out the configuration drift (by first deleting the VM extension, then creating it again and then applying the azprovider update) it enables the feature again and causes configuration drift to re-occur so there is no end to this rabbit hole 😄

I think we will stick to the Azure CLI approach for now.

[ebruersan]

@Masahigo we were not able to repro this configuration drift. In order to resolve this issue we would need to have a support case open. It's up to you, of course, to pursue it or not.

[Masahigo]

@ebruersan Thanks for the help!

[hzargaryan]

I was able to reproduce the issue. After successfully applying the code block below, running terraform plan shows that sql_connectivity_port was reset to 0 by azapi_update_resource (see the screenshot).

resource "azapi_update_resource" "enable_sql_entra_id_auth" {
  type        = "Microsoft.SqlVirtualMachine/sqlVirtualMachines@2023-10-01"
  resource_id = azurerm_mssql_virtual_machine.sql_vm.id

  body = {
    properties = {
      serverConfigurationsManagementSettings = {
        azureAdAuthenticationSettings = { 
          clientId : "" # set clientId empty to use system-assigned managed identity
        } 
      }
    }
  }

  depends_on = [
    azurerm_mssql_virtual_machine.sql_vm
  ]
}