Terraform - ADF to DB connectivity issue when tenant_id is provided in LS configuration - azurerm_data_factory_linked_service_azure_sql_database

Question

Terraform Version

1.2.3

AzureRM Provider Version

v3.13.0

Affected Resource(s)/Data Source(s)

Azure data factory, SQL Database

Terraform Configuration Files

resource "azurerm_data_factory_linked_service_azure_sql_database" "sqldatabase_linked_service_10102022" {
  count = (var.subResourcesInfo.sqlDatabaseName != "") ? 1 : 0
  depends_on = [azurerm_data_factory_integration_runtime_azure.autoresolve_integration_runtime,
  azurerm_data_factory_managed_private_endpoint.sqlserver_managed_endpoint]

  name            = "AzureSqlDatabase10102022"
  data_factory_id = azurerm_data_factory.datafactory.id

  integration_runtime_name = "AutoResolveIntegrationRuntime"
  use_managed_identity     = true
  connection_string = format("Integrated Security=False;Data Source=%s.database.windows.net;Initial Catalog=%s;",
    var.subResourcesInfo.sqlServerName,
  var.subResourcesInfo.sqlDatabaseName)
}

Expected Behaviour

Issue is ADF to DB connectivity, error:

Operation on target DWH_DF_aaa failed: {'StatusCode':'DFExecutorUserError','Message':'Job failed due to reason: com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for AzureSqlDatabase. ServicePrincipalAuthentication is invalid. One or two of servicePrincipalId/key/tenant is missing.','Details':''}

When we created this LS using TF, we get tenant="" in ADF LS Json file which we suspect that causing issue of above error.

When we created the same LS directly on ADF UI, then there is no field of tenant="" in its json file, and if we use this LS in dataflow/pipeline then communication works from ADF to DB.

Expected behavior should be, if we don't provide tenant_id parameter in TF code then in json also should not show tenant="" which then works for connectivity.

Answer 1

I tried to reproduce the scenario in my environment:

With below code , I could create a Linked Service (connection) between Azure SQL Database and Azure Data Factory.

Code:

resource "azurerm_data_factory" "example" {
  name                            = "kaADFexample"
  location                        = data.azurerm_resource_group.example.location
  resource_group_name             = data.azurerm_resource_group.example.name
  managed_virtual_network_enabled = true
}

resource "azurerm_storage_account" "example" {
  name                     = "kaaaexample"
  resource_group_name      = data.azurerm_resource_group.example.name
  location                 = data.azurerm_resource_group.example.location
  account_kind             = "BlobStorage"
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_data_factory_managed_private_endpoint" "example" {
  name               = "example"
  data_factory_id    = azurerm_data_factory.example.id
  target_resource_id = azurerm_storage_account.example.id
  subresource_name   = "blob"
}


resource "azurerm_user_assigned_identity" "main" {
    depends_on = [data.azurerm_resource_group.example]
    name = "kasupports01-mid"
    resource_group_name = data.azurerm_resource_group.example.name
    location = data.azurerm_resource_group.example.location
}


resource "azurerm_data_factory_integration_runtime_azure" "test" {
  name                    = "AutoResolveIntegrationRuntime"
  data_factory_id      = azurerm_data_factory.example.id
  location                = "AutoResolve"
  virtual_network_enabled = true
}

resource "azurerm_data_factory_linked_service_azure_sql_database" "linked_service_azure_sql_database" {
  name                     = "kaexampleLS"
  data_factory_id          = azurerm_data_factory.example.id
  connection_string        = "data source=serverhostname;initial catalog=master;user id=testUser;Password=test;integrated security=False;encrypt=True;connection timeout=30"
  use_managed_identity     = true
  integration_runtime_name = azurerm_data_factory_integration_runtime_azure.test.name
  
  

depends_on = [azurerm_data_factory_integration_runtime_azure.test,
azurerm_data_factory_managed_private_endpoint.example]

}



output "id" {
  value = azurerm_data_factory_linked_service_azure_sql_database.linked_service_azure_sql_database.id
}

Executed: terraform plan

Output:

id = "/subscriptions/xxxxxxxxx/resourceGroups/xxxxxx/providers/Microsoft.DataFactory/factories/kaADFexample/linkedservices/kaexampleLS"

If the error persists in your case ,try removing the tenant attribute in the data_factory just after deployment is done in terraform.

Please check this known issue and mentioned by @chgenzel in terraform-provider-azurerm issues | Github

ADF:

Managed Identity

Linked service : azure sql

Reference: data_factory_linked_service_azure_sql_database | terraformregistry