azurerm_data_factory_linked_service_azure_sql_database with private endpoint is not able to connect to the database

edi239 commented 2 years 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.2.8

AzureRM Provider Version

3.2.0

Affected Resource(s)/Data Source(s)

azurerm_data_factory_linked_service_azure_sql_database

Terraform Configuration Files

terraform {

required_providers {
azurerm = {
source = "hashicorp/azurerm"
version = "=3.20.0"
}
azuread = {
source = "hashicorp/azuread"
version = "=1.5.1"
}
random = {
source = "hashicorp/random"
version = "=3.1.0"
}
}
}

provider "azurerm" {
subscription_id = var.subscription_id
tenant_id = var.tenant_id
features {}
}

resource "azurerm_resource_group" "rg" {
name = "test_df"
location = var.location
}

resource "azurerm_virtual_network" "base_vnet" {
name = "base-vnet"
address_space = ["192.168.1.0/24"]

resource_group_name = azurerm_resource_group.rg.name
location = var.location
}

resource "azurerm_subnet" "base_subnet" {
name = "base-sn"
resource_group_name = azurerm_resource_group.rg.name
virtual_network_name = azurerm_virtual_network.base_vnet.name
address_prefixes = ["192.168.1.0/25"]
private_endpoint_network_policies_enabled = true
}

resource "azurerm_mssql_server" "sql_server" {
name = "df-test-sql-239134"
resource_group_name = azurerm_resource_group.rg.name
location = var.location
version = "12.0"
administrator_login = "sad"
administrator_login_password = "Test12345"
public_network_access_enabled = "false"
}

resource "azurerm_private_endpoint" "sql_srv_endpoint" {
name = "pe-sql"
location = var.location
resource_group_name = azurerm_resource_group.rg.name
subnet_id = azurerm_subnet.base_subnet.id

private_service_connection {
name = "psc-sql"
private_connection_resource_id = azurerm_mssql_server.sql_server.id
subresource_names = [ "sqlServer" ]
is_manual_connection = false
}

lifecycle { ignore_changes = [ private_dns_zone_group ] }

}

resource "azurerm_mssql_database" "sql_database" {
name = "sql_db"
server_id = azurerm_mssql_server.sql_server.id
sku_name = "Basic"

//geo_backup_enabled = var.mssqldatabase_geo_backup_enabled
//create_mode = var.mssqldatabase_create_mode
}

resource "azurerm_data_factory" "DataFactory01" {
name = "test-df-239134"
location = var.location
resource_group_name = azurerm_resource_group.rg.name
managed_virtual_network_enabled = true
public_network_enabled = false

//identity {type = "SystemAssigned" }
}

resource "azurerm_data_factory_integration_runtime_azure" "IntegrationRuntime02" {
name = "integrationruntime02"
data_factory_id = azurerm_data_factory.DataFactory01.id
//resource_group_name = azurerm_resource_group.ek_rg.name
location = var.location
time_to_live_min = 60
virtual_network_enabled = true
}

resource "azurerm_data_factory_linked_service_azure_sql_database" "df_to_sqllock" {
name = "sqldb_db"
//resource_group_name = azurerm_resource_group.ek_rg.name
data_factory_id = azurerm_data_factory.DataFactory01.id
integration_runtime_name = azurerm_data_factory_integration_runtime_azure.IntegrationRuntime02.name
connection_string = "data source=${azurerm_mssql_server.sql_server.name}.database.windows.net;Initial Catalog=${azurerm_mssql_database.sql_database.name};Persist Security Info=False;User ID=sad;Password=Test12345;Encrypt=True;Connection Timeout=30;"

}

resource "azurerm_data_factory_managed_private_endpoint" "df_to_sql" {
name = "pe_to_sql"
data_factory_id = azurerm_data_factory.DataFactory01.id
target_resource_id = azurerm_mssql_server.sql_server.id
subresource_name = "sqlServer"

lifecycle { ignore_changes = [ fqdns ] }

Debug Output/Panic Output

Debug Output
Error code
9070
Details
Failed to decrypt sub-resource 'None' payload on cloud with error: Failed to decrypt sub-resource payload {
"name": "sqldb_db",
"properties": {
"connectVia": {
"name": "integrationruntime02",
"properties": {
"type": "Managed",
"typeProperties": {
"computeProperties": {
"location": "West Europe",
"dataFlowProperties": {
"computeType": "General",
"coreCount": 8,
"timeToLive": 60,
"cleanup": true
},
"pipelineExternalComputeScaleProperties": {
"timeToLive": 60
}
}
},
"managedVirtualNetwork": {
"referenceName": "default",
"type": "ManagedVirtualNetworkReference",
"id": "fba9f0fd-2a85-4da7-958b-350346efc35f"
}
}
},
"type": "AzureSqlDatabase",
"annotations": [],
"typeProperties": {
"connectionString": "",
"servicePrincipalId": "",
"tenant": "",
"encryptedCredential": ""
}
}
} and error is: Message for the errorCode 'UserErrorInvalidDbConnectionString' not found.., status code: BadRequest.
Activity ID: 40622a2d-6a75-40be-9651-8f8f805dd064

Expected Behaviour

After deployment the linked service should be able to connect to the sqlserver like this:

Actual Behaviour

Test Connection to sql server will produce the above error. Private Endpoint is not found by linked service

failed Linked Service

Steps to Reproduce

terraform plan terraform apply Approve Private Endpoint in SQL Server - Networking - Private Endpoints

Wait some time to let integration service get healthy

Go to datafactory Open Azure DataFactory Studio Go To linked services Open linked service Look whether interactive authoring is enabled - otherwise enable it

Test Connection

Important Factoids

No response

References

No response

sinbai commented 2 years ago

@edi239 thanks for opening this issue here. Could you try changing Account select method below to From Azure Subscription and select database sql_db to see if the managed private endpoint is available?

sinbai commented 2 years ago

On my side, it worked fine even though the Account selection method was set to Enter manually. But the Fully qualified domain name and database name should be specified manually instead of the default value *.

edi239 commented 2 years ago

In terraform-code I used the same parameters as I used manually (The picture under expected behaviour). Manually it worked. But in terraform it did not. The managed private endpoint in the manual section is deployed also by terraform. There is no problem to use it.

I did not find a way to enter the parameters to "from azure subscription" it switches always to "enter manually"

sinbai commented 2 years ago

Hi @edi239,

Thanks for your quick reply.

Here is the Azure API called by terraform resource azurerm_data_factory_managed_private_endpoint. Since the target_resource_id property of azurerm_data_factory_managed_private_endpoint (aka the privateLinkResourceId property of Azure API ) has been correctly set to azurerm_mssql_server.sql_server.id by terraform. And the managed private endpoint is actually be found in Azure portal(either From Azure subscription or Enter manually), so I assume that terraform's job is done.

In additional, there is no corresponding property for Account selection method related settings in the Azure API, so terraform could not determine its selection value. For Enter Manually in Azure Portal, it seems to me that it literally means manual enter instead of using the default value *, WDYT?

edi239 commented 2 years ago

Hi @sinbai,

Thank you for your answer.

I don't think that the problem is the private endpoint. The problem here is the azurerm_data_factory_linked_service_azure_sql_database resource. If I enter the parameter of the manually I can read them afterwards and the private endpoint is found by azure. If I deploy them via terraform I can't read them, because they are hidden. See pictures above. In my oppinion the value under Fully qualified domain name is not correct. So the service can't find the correct private endpoint. In my oppinion there are two possibilities. The first one is that terraform delivers the wrong parameters (perhaps as secure string which the azure portal can't read correctly) or the azure api doesn't work correctly in this point.

ssudhakarrao commented 1 year ago

On my side, it worked fine even though the Account selection method was set to Enter manually. But the Fully qualified domain name and database name should be specified manually instead of the default value *.

Hi, for the above result, how did you enter the values using terraform?

herman-starzhynski-safefood360-com commented 7 months ago

This issue clearly still active. The issue, as mentioned by @edi239 above, is around how TF sets the connection settings. Thus, Data Factory cannot determine which server it connects to to map to the private link.

`resource "azurerm_data_factory_linked_service_azure_sql_database" "example" { name = "example" data_factory_id = azurerm_data_factory.example.id integration_runtime_name = azurerm_data_factory_integration_runtime_azure.example.name connection_string = "Data Source=example.database.windows.net;Initial Catalog=example;User ID=test;"

key_vault_password { linked_service_name = azurerm_data_factory_linked_service_key_vault.example.name secret_name = "sql-password" } }`

herman-starzhynski-safefood360-com commented 7 months ago

Here the connection string is set to a secure string - this causes the behaviour above - https://github.com/hashicorp/terraform-provider-azurerm/blob/15921fe5695cbe6bd5fd9ce96c9cca1fc2e77689/internal/services/datafactory/data_factory_linked_service_azure_sql_database_resource.go#L210

However, Azure API allows either of three: "The connection string. Type: string, SecureString or AzureKeyVaultSecretReference" - https://github.com/Azure/azure-rest-api-specs/blob/208c91dc71eec634400fb0c0a7e073e35afd2978/specification/datafactory/resource-manager/Microsoft.DataFactory/stable/2018-06-01/entityTypes/LinkedService.json#L381

Changing the data type of the connection string must fix the issue and allow the setting up of private endpoints for Azure SQL Database connections. Anyone who can change that?

hashicorp / terraform-provider-azurerm