Support for azurerm_mssql_managed_instance with AAD admin + azuread_authentication_only

[YohanValette-DXC]

Is there an existing issue for this?

• I have searched the existing issues

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Description

We need to have the ability to setup the AAD administrator in a block in the azurerm_mssql_managed_instance it-self.

The mssql_managed_instance_active_directory_administrator resource does not fit our needs because of the built-in Azure policy "Azure SQL Database should have Azure Active Directory Only Authentication enabled"

The problem is that when the Azure Policy has the Deny action it prevents creating a azurerm_mssql_managed_instance that do not have administrators.azureADOnlyAuthentication set.

As this parameter is set outside of the azurerm_mssql_managed_instance (that is with the mssql_managed_instance_active_directory_administrator), the MSSQL MI would not comply with the policy at first, thus it cannot be created...

New or Affected Resource(s)/Data Source(s)

azurerm_mssql_managed_instance

Potential Terraform Configuration

Having the block such as in the `azurerm_mssql_managed_instance_active_directory_administrator` resource.

An `azuread_administrator` block:

- login_username - (Required) The login username of the Azure AD Administrator of this SQL Server.
- object_id - (Required) The object id of the Azure AD Administrator of this SQL Server.
- tenant_id - (Optional) The tenant id of the Azure AD Administrator of this SQL Server.
- azuread_authentication_only - (Optional) Specifies whether only AD Users and administrators (like azuread_administrator.0.login_username) can be used to login, or also local database users (like administrator_login). When true, the administrator_login and administrator_login_password properties can be omitted.

References

No response

[sinbai]

@YohanValette-DXC thanks for opening this issue here. Since setting administrators.azureADOnlyAuthentication via Azure API in Terraform requires that azurerm_mssql_managed_instance already exists, adding an azuread_administrator block in azurerm_mssql_managed_instance has actually the same effect as using mssql_managed_instance_active_directory_administrator.

[YohanValette-DXC]

@sinbai thank you for the quick answer 👍

Ok, so I understand that the Azure API it-self does not allow to use the "Azure SQL Database should have Azure Active Directory Only Authentication enabled" policy

[sinbai]

@YohanValette-DXC thanks for your reminder. Terraform could support it via Managed Instances API instead of ManagedInstanceAzureADOnlyAuthentications API.

[mkprizzle]

This is still needed. Managed Instance username/password are still required arguments in TF. This is undesirable if we want AAD Only authentication. Add an authentication mode argument to the MI configuration. If this is not set to "aad_only" then require username and password, otherwise they should be optional arguments. The way it appears right now is if the resource AAD Admin is used to create one, that only supports the "Use both SQL and Azure AD authentication" use case (since a password is still enabled) unless I am misunderstanding how that works.

[mrfreester]

This is a blocker for me, my organization won't allow the creation of the managed instance by policy unless azure AD authentication only is set :

sql.ManagedInstancesClient#CreateOrUpdate: Failure sending request:
│ StatusCode=403 -- Original Error: Code="RequestDisallowedByPolicy"
│ Message="Resource '<my_resource>' was disallowed by policy.
│ Policy identifiers: '[{"policyAssignment":{"name":"P1-Deny-262-Azure
│ SQL Managed Instance must have Azure Active Directory Only Authentication
... and so on

[mike-schenk]

This is possible with Azure SQL Server. It should be made to work with Azure SQL Managed Instance as well.

The following shows how it's done for Azure SQL Server.

resource "azurerm_mssql_server" "server" {
  name                = local.sql_server_name
  resource_group_name = data.azurerm_resource_group.rgrp.name
  location            = data.azurerm_resource_group.rgrp.location
  version             = "12.0"

  # administrator_login              Not required because of azuread_authentication_only
  # administrator_login_password     in the azuread_administrator block below.

  azuread_administrator {
    login_username              = var.sql_ad_admin.name
    object_id                   = var.sql_ad_admin.object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = true
  }

}

[mkprizzle]

This is possible with Azure SQL Server. It should be made to work with Azure SQL Managed Instance as well.

The scope of this should expand to all database services which support AAD auth, such as Postgres, MySQL..etc etc.

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.