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[bot]]

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.

[microsoft-github-policy-service[bot]]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.