Tuesday, November 16, 2021

[SOLVED] Azure Synapse Private Endpoint Approve

Issue

Via some Terraform scripts within a CICD process I am trying to create a Managed private Endpoint for an Azure SQL Server Linked service. This is successful using the following code:

resource "azurerm_synapse_managed_private_endpoint" "mi_metadata_transform_sql_server_private_endpoint" {
name                 = "mi_synapse_metadata_transform_private_endpoint"
subresource_name     = "sqlServer"
synapse_workspace_id = module.mi_synapse_workspace.synapse_workspace_id
target_resource_id   = azurerm_mssql_server.mi-metadata-transform-sql-server.id}

But that leaves the Endpoint in a "Pending Approval State". So adding the code below which is based on some of our existing code that approves some storage via Bash, I decided to copy that code and adjust accordingly for SQL Server. And this is where my problem begins.....

function enable_sql_private_endpoint {
        endpoints=$(az sql server show --name $1 -g ${{ parameters.resourceGroupName }} --subscription $(serviceConnection) --query  "privateEndpointConnections[?properties.privateLinkServiceConnectionState.status=='Pending'].id" -o tsv)        
        for endpoint in $endpoints 
        do
          az sql server private-endpoint-connection approve --account-name $1 --name $endpoint --resource-group ${{ parameters.resourceGroupName }} --subscription  $(serviceConnection)
        done
        }


    sqlServers="$(az sql server list -g ${{ parameters.resourceGroupName }} --query '[].name' --subscription $(serviceConnection) -o tsv)"

    for sqlServerName in $sqlServers
    do
        echo "Processing $sqlServerName ========================================="
        enable_sql_private_endpoint  $sqlServerName
    done

The code above is executed in a further step in a YAML file and in it's simplest terms:

  • YAML Orchestrator File executed via CICD
  • Terraform Script called to create resource (code snippet 1)
  • Another YAML file executed to approve endpoints using inline Bash (code snippet 2)

The problem is with az sql server private-endpoint-connection approve and that it doesn't exist. When I review this link I cannot see anything remotely like the approve option for SQL Server Endpoints like what Storage or MySQL have. Any help would be appreciated on how this can be achieved


Solution

In the end, this is what I used in my YAML / Bash to get things working:

        sqlServers="$(az sql server list -g ${{ parameters.resourceGroupName }} --query '[].name' --subscription $(serviceConnection) -o tsv)"

    for sqlServerName in $sqlServers
    do
      echo "Processing $sqlServerName ========================================="
      enable_sql_private_endpoint  $sqlServerName
    done

and

        function enable_sql_private_endpoint {
    endpoints=$(az sql server show --name $1 -g ${{ parameters.resourceGroupName }} --subscription $(serviceConnection) --query  "privateEndpointConnections[?properties.privateLinkServiceConnectionState.status=='Pending'].id" -o tsv)        
    for endpoint in $endpoints 
    do
      az network private-endpoint-connection approve -g  ${{ parameters.resourceGroupName }} --subscription $(serviceConnection) --id $endpoint  --type Microsoft.Sql/servers --description "Approved" --resource-name $1
    done
    }

With the following line being the key syntax to use if anyone ever encounters such a similar scenario in their CICD with Syanpse and Managed Private Endpoints:

az storage account private-endpoint-connection approve --account-name $1 --name $endpoint --resource-group ${{ parameters.resourceGroupName }} --subscription  $(serviceConnection)


Answered By - Raymondo
at