DataFactory azurerm_data_factory_linked_service_azure_sql_database with Managed Identity configuration not working properly with Azure DataFactory DataFlows

[chgenzel]

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

Terraform (and AzureRM Provider) Version

Terraform v1.0.7 on linux_amd64

• provider registry.terraform.io/hashicorp/azurerm v2.89.0
• provider registry.terraform.io/hashicorp/http v2.1.0

Affected Resource(s)

azurerm_data_factory_linked_service_azure_sql_database

Terraform Configuration Files

resource "azurerm_data_factory_linked_service_azure_sql_database" "factory_db" {
  name                 = "${var.dbname}linkedservice"
  resource_group_name  = data.azurerm_resource_group.main.name
  data_factory_name    = azurerm_data_factory.factory.name
  use_managed_identity = true
  connection_string    = "integrated security=False;encrypt=True;connection timeout=30;data source=${azurerm_mssql_server.server.fully_qualified_domain_name};initial catalog=${azurerm_mssql_database.db.name}"
}
# I know that name is deprecated and will change it in the future.

Debug Output

n/a

Panic Output

n/a

Expected Behaviour

DataFactory linked service configuration (no "tenant"):

{
    "name": "platondfdblnkrun",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "integrated security=False;encrypt=True;connection timeout=30;data source=somedb.database.windows.net;initial catalog=somedb"
        },
        "annotations": []
    }
}

Actual Behaviour

DataFactory linked service configuration:

{
    "name": "platondfdblnkrun",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "integrated security=False;encrypt=True;connection timeout=30;data source=somedb.database.windows.net;initial catalog=somedb",
            "tenant": ""
        },
        "annotations": []
    }
}

Steps to Reproduce

1. Create a DataFactory and a Azure SQL Database together with a linked service via Terraform. DataFactory must have a Managed Identity. Azure SQL Database must have Azure AD Administrator set.

2. After everything is deployed, create a user for the DataFactory Managed Identity in the Azure SQL Database using "PROVIDER EXTERNAL" e.g. CREATE USER [datafactoryname] FROM EXTERNAL PROVIDER.

3. Go back to Azure DataFactory and "Test Connection" for the linked service. Connectivity should be fine.

4. Create an Azure DataFactory DataFlow, with a source set to the linked service Azure SQL Database with Source Type "inline" configuration.

5. Start debug session for Azure DataFlow.

6. When debug session is ready, do a connectivity test "Test connection" for the source of the DataFlow configuration.

7. The connectivity test will fail with com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for <linkname>. ServicePrincipalAuthentication is invalid. One or two of servicePrincipalId/key/tenant is missing. - RunId: xxxx

8. Go to DataFactory linked service settings, edit the linked service Azure SQL Database in JSON view and remove "tenant":"".

9. Go back to the DataFlow change the source to nothing and back to the linked service Azure SQL Database (DataFactory needs to recognize the change or it will fail again).

10. Do a connectivity test "Test connection" for the source of the DataFlow configuration. This time the test will pass.

I tried to add the actual tenant id, like "tenant":"some-tenant-id", this does not help. Only removing the "tenant":"" statement helps. I checked how the linked service would look like, if it is created via the web-based management. It does not contain the "tenant":"" statement.

What I don't know is, if the "tenant":"" statement can be influenced by the Terraform provider or if it is a problem with Microsoft ARM or CLI? I can raise a ticket for Azure Support, if the latter is the case.

Important Factoids

n/a

References

n/a

[sfeyaerts]

Any updates on this issue? Did you find a way to resolve it? I am currently experiencing the same issue.

[chgenzel]

Any updates on this issue? Did you find a way to resolve it? I am currently experiencing the same issue.

@sfeyaerts At the moment, I am manually removing the "tenant" attribute in the data factory after deployment. Then it works fine.

[supereddie]

I have worked around this by using the custom_linked_service

resource "azurerm_data_factory_linked_custom_service" "sql" {
  name                 = "LinkedSql"
  data_factory_id      = azurerm_data_factory.factory.id
  type                 = "AzureSqlDatabase"
  type_properties_json = <<JSON
  {
    "connectionString": "integrated security=False;encrypt=True;connection timeout=30;data source=${azurerm_mssql_server.server.fully_qualified_domain_name};initial catalog=${azurerm_mssql_database.db.name}"
  }
  JSON
}

Drawback is that the json cannot be validated with the validate command.

[pregress]

Also you do not have a terraform option to specify a credential, If you want to connect to the Azure Sql using a user assigned credential.

Response of az rest: https://learn.microsoft.com/en-us/rest/api/datafactory/linked-services/get?view=rest-datafactory-2018-06-01&tabs=HTTP

{
  "etag": "b2002343-0000-0d00-0000-65df142b0000",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/example/providers/Microsoft.DataFactory/factories/example/linkedservices/example",
  "name": "example",
  "properties": {
    "annotations": [],
    "description": "Connection to the example azure sql database using managed identity",
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=example.database.windows.net;Initial Catalog=example;Application Name=example",
      "credential": {
        "referenceName": "example",
        "type": "CredentialReference"
      }
    }
  },
  "type": "Microsoft.DataFactory/factories/linkedservices"
}

I also went the route of the custom_service:

resource "azurerm_data_factory_linked_custom_service" "example" {
  name                 = "example"
  data_factory_id      = azurerm_data_factory.example.id
  description          = "Connection to the example azure sql database using managed identity"
  type                 = "AzureSqlDatabase"
  type_properties_json = <<JSON
  {
    "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=${data.azurerm_mssql_server.example.fully_qualified_domain_name};Initial Catalog=${azurerm_mssql_database.example.name};Application Name=${azurerm_data_factory.example.name}",
    "credential": {
        "referenceName": "${azurerm_data_factory_credential_user_managed_identity.example.name}",
        "type": "CredentialReference"
      }
  }
  JSON
}