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

chgenzel commented 2 years ago

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

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.
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.
Go back to Azure DataFactory and "Test Connection" for the linked service. Connectivity should be fine.
Create an Azure DataFactory DataFlow, with a source set to the linked service Azure SQL Database with Source Type "inline" configuration.
Start debug session for Azure DataFlow.
When debug session is ready, do a connectivity test "Test connection" for the source of the DataFlow configuration.
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
Go to DataFactory linked service settings, edit the linked service Azure SQL Database in JSON view and remove "tenant":"".
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).
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 commented 2 years ago

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

chgenzel commented 2 years ago

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 commented 1 year ago

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 commented 4 months ago

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
}

hashicorp / terraform-provider-azurerm