How to create and assign the managed identity in azurerm_sql_server

I am trying to create and assign managed identity for the azure SQL server. I didn’t find where azure_sql_server will have have the following properties like mssql_server.

azuread_administrator {
    login_username = "AzureAD Admin"
    object_id      = "00000000-0000-0000-0000-000000000000"
  }


identity {
 type= UserAssigned
primary_user_assigned_identity_id = "00000000-0000-0000-0000-000000000000"

Could someone help me with the above.

Hi @animesh.srivastava!

I’d recommend to use azurerm_mssql_server instead of azurerm_sql_server. azurerm_mssql_server receives new features often earlier, if they arrive to azurerm_sql_server at all.

Do you have any reason to prefer azurerm_sql_server? In that case you can use azurerm_sql_active_directory_administrator to define the Azure AD administrator, but UserAssigned identity is not supported yet.

Thanks, aristosvo

There are SQL server that is created using azurerm_sql_server, if it is changed to azurerm_mssql_server, will there be any impact on existing data if recreated using mssql module?

Yes, therefore I would not recreate it, but import it. I wouldn’t do this in PRD first, but in general this should work. Make sure you’ve valid configuration etc, be careful.

terraform state rm <azurerm_sql_server.resource_name>
terraform import <-var=x> <-var-file=x> <azurerm_mssql_server.resource_name> <azure ID>

Hi, all I am using azurerm 2.71

I get the following error:
An argument named “primary_user_assigned_identity_id” is not expected here.

I added the identity block as below in the azurerm_mssql_server resource:

identity {
type = “UserAssigned”
primary_user_assigned_identity_id = azurerm_user_assigned_identity.XXXXX.id
}

I expect a user assigned identity which I have created in another block azurerm_user_assigned_identity.XXXXX.id to be created on the sql server resource

As mentioned by @aristosvo azurerm_mssql_server should be used. It has block as below
azuread_administrator {
login_username = var.ua_managed_identity_name
object_id = var.ua_managed_identity_principal_id
}

My code is present inside module hence variables are assigned to login_username etc. Those values are assigned as below
ua_managed_identity_name = azurerm_user_assigned_identity.ua_managed_identity.name
ua_managed_identity_principal_id = azurerm_user_assigned_identity.ua_managed_identity.principal_id

Managed identity block looks like below

User assigned managed identity

#--------------------------------------------
resource “azurerm_user_assigned_identity” “ua_managed_identity” {
name = var.user_assigned_managed_identity_name
resource_group_name = azurerm_resource_group.rg.name
location = var.resource_group_location
}