Synapse SQL Managed Identity role not behaving as expected

Skubakoob commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Community Note

Please vote on this issue by adding a :thumbsup: 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

Terraform Version

1.4.5

AzureRM Provider Version

2.9.9

Affected Resource(s)/Data Source(s)

azurerm_synapse_role_assignment

Terraform Configuration Files

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "=2.99.0"
    }
  }
}

# Configure the Microsoft Azure Provider
provider "azurerm" {
  features {}
}

resource "azurerm_storage_account" "my_synapse" {
  name                     = "mysynapsestg"
  resource_group_name       = azurerm_resource_group.my_resource_group.name
  location                  = azurerm_resource_group.my_resource_group.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
  account_kind             = "StorageV2"
  is_hns_enabled           = "true"

  tags = local.common_tags
}

resource "azurerm_storage_data_lake_gen2_filesystem" "my_synapse" {
  name               = "mysynapsestgfs"
  storage_account_id = azurerm_storage_account.my_synapse.id
}

resource "azurerm_synapse_workspace" "my_synapse" {
  name                                 = "mysynapsews"
  resource_group_name                  = azurerm_resource_group.my_resource_group.name
  location                             = azurerm_resource_group.my_resource_group.location
  storage_data_lake_gen2_filesystem_id = azurerm_storage_data_lake_gen2_filesystem.my_synapse.id
  sql_administrator_login              = "synapseadmin"
  sql_administrator_login_password     = var.sql_login_password
  managed_virtual_network_enabled      = true

  aad_admin {
    login     = var.synapse_admin_username
    object_id = data.azuread_user.svc_account.object_id
    tenant_id = data.azurerm_client_config.current.tenant_id
  }

  tags = local.common_tags
}

resource "azurerm_role_assignment" "my_synapse_svc" {
  scope                = azurerm_storage_account.my_synapse.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = azurerm_function_app.mya_az_function.identity[0].principal_id
  depends_on = [
    azurerm_function_app.mya_az_function
  ]
}

# Whilst the below correctly sets the permissions, it is unreliable
# as for some reason the account is sometimes not able to connect to Synapse
# This works if the Synapse SQL Admin role is removed then re-added manually
# There doesn't seem to be any difference in the setup, but the manual add works whereas 
# adding through TF does not. 
# The sleep seemed to help, but is also unreliable

# resource "time_sleep" "wait_120_seconds" {
#   depends_on = [azurerm_role_assignment.my_synapse_svc]
#   create_duration = "120s"
# }

resource "azurerm_synapse_role_assignment" "my_synapse_svc" {
  synapse_workspace_id = data.azurerm_synapse_workspace.shared_synapse.id
  role_name            = "Synapse SQL Administrator"
  principal_id         = azurerm_function_app.mya_az_function.identity[0].principal_id

  depends_on = [
    azurerm_function_app.mya_az_function,
    azurerm_role_assignment.my_synapse_svc
    # time_sleep.wait_120_seconds
  ]
}

Debug Output/Panic Output

No error is reported

Expected Behaviour

When an Azure function is given correct permissions via terraform to the synapse storage account and the sql role within the synapse workspace, the azure function should be able to query the synapse workspace via the serverless sql pool using azure ad managed identity connection string

Actual Behaviour

In most cases, the azure function cannot access the synapse workspace when these roles are assigned via terraform. The roles are correctly created and visible.

Interestingly, assigning permissions via terraform will sometimes (though, quite rarely) work, though I've not been able to pinpoint any factor that influences this. I.e. timing, sequencing, etc. of the creation of these roles. Initially I thought it may be to timing and sequencing - i.e. the SQL Admin role is added too soon after the storage account permissions but this doesn't seem to be the case.

When the exact same roles are assigned manually, the azure function can connect without any issue.

The error when the SQL connection fails is "Login failed for user ''

After deployment, if the azure function cannot access the workspace, deleting the role assignment within the workspace then re-adding it manually fixes the issue (though terraform will of course then complain about it not being in control of that role)

If I try to remove the role then re-run terraform to re-create it, then again it will run through and appear to be created correctly but whether the azure function can access the workspace or not seems to be a roll of the dice (heavily favouring not working!)

Steps to Reproduce

No response

Important Factoids

No response

References

No response

rcskosir commented 1 year ago

Thanks for opening this issue. This was a problem in the 2.x version of the provider which is no longer actively maintained. If this is still an issue with the 3.x version of the provider please do let us know by opening a new issue, thanks!

github-actions[bot] commented 4 months ago

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.

hashicorp / terraform-provider-azurerm