Cannot use AD Group for azuread_administrator on azurerm_mssql_server module

We recently upgraded from the azurerm_sql_server module to the azurerm_mssql_server module; however, the way in which we use to apply the SQL Active Directory Administrator appears to have changed. It is now a built-in block which works for setting a single individual user name (e.g. myuser@myco.com) but does not work when applying to an AAD group (e.g. MySQLADAdmins). However, if you defer to using the previous way to applying the SQL AD Admin it works; however, every other time it removes the entry. Meaning if you run the following block it will apply it but if you re-run it, it will remove it (and on and on):

resource “azurerm_sql_active_directory_administrator” “main” {

  • server_name = azurerm_sql_server.main.name*
  • resource_group_name = azurerm_resource_group.main.name*
  • tenant_id = “000000000000000000000000000”*
  • login = “MySQLADAdmins”*
  • object_id = “11111111111111111111111”*
    }

Again the new way, which is shown below, only works with a single username but we need this to be a group.

resource “azurerm_mssql_server” “main” {

  • name = “${local.resource_prefix}-sql”*

  • resource_group_name = azurerm_resource_group.main.name*

  • location = azurerm_resource_group.main.location*

  • version = “12.0”*

  • administrator_login = random_string.sql_username.result*

  • administrator_login_password = random_password.sql_password.result*

  • azuread_administrator {*

  • login_username = “MySQLADAdmins”*

  • object_id = “111111111111111111111111”*

  • tenant_id = “000000000000000000000000”*

  • }*

Any suggestions?

1 Like

Update/Additional details: so this appears to be a “state” issue. Meaning, it will set it once but then if the field gets manually changed (e.g. someone removes or changes the AD Admin group via the Azure UI) and then the pipeline re-runs the Terraform scripts it does not re-populate the field. If you remove the azuread_administrator block from the script it and run a Terraform plan/apply it will remove it from state. Then if you put the block back it will add the group back, but, as aforementioned, if someone changes it in the Azure UI the next time Terraform runs it does not put the group back in there.

Can you elaborate on what you mean by “state” issue? I am seeing the behavior noted in the bug report, but without any modifications the the sql server outside of terraform. In my example, I created and azurerm_mssql_server using terraform. As a follow up I tried to set set a group as the ad administrator using azuread_administrator. The plan indicated that the change would be made, and my state file shows that the mssql_server should have an AD group as its administrator, but when I look in the portal, AD administration is not enabled, and no AD group is associated with the SQL server. Similar behavior can be found in this bug report:
azurerm_sql_active_directory_administrator removed from azurerm_mssql_server on subsequent deployments · Issue #9946 · terraform-providers/terraform-provider-azurerm (github.com)