As you may well be aware, Microsoft have deprecated the original V1 SQL actions in Logic Apps and Power Automate. You have until March 2025 to update your logic apps before they break.
If you have any flows or Logic Apps that use the old V1 SQL actions you will need to update them.
Of course you can do this the tedious, manual way, or, if you’re a Logic Apps user, you can simply run this PowerShell script to do it for you:
- Step 1: Install-Module Az if you haven’t already.
- Step 2: Run this script. Substitute $subscriptionId with your own Subscription ID.
Including the -TenantId parameter to Connect-AzAccount is only required if you need to connect as a guest user to another tenant. If you need that, then obviously replace the value with the relevant tenant id. You can find this on the overview page of the Entra ID portal.
# Set the tenant ID and subscription ID
$tenantId = "00000000-0000-0000-0000-000000000000"
$subscriptionID = "00000000-0000-0000-0000-000000000000"
# Connect to Azure using the tenant ID and subscription ID
Connect-AzAccount -TenantId $tenantId -Subscription $subscriptionID
# Set the Azure context to the specified subscription
Set-AzContext -Subscription $subscriptionID
# Define the Azure Resource Graph query to find Logic Apps with SQL actions
$azGraphQueryActions = "resources
| where subscriptionId == ""$subscriptionID""
| where type == ""microsoft.logic/workflows""
| extend propertiesJson=parse_json(properties)
| extend actionsJson=propertiesJson[""definition""][""actions""]
| mv-expand actionsJson
| where notnull(actionsJson)
| extend path=extract(""\""path\"":\""(.*?)\"""", 1, tostring(actionsJson))
| where notnull(path) and path startswith ""/datasets/default/""
| extend actionConnectionName=extract(""\""connection\"":{\""name\"":\""(.*?)\""}"", 1, tostring(actionsJson))
| where notnull(actionConnectionName)
| parse actionConnectionName with ""@parameters('`$connections')['""parsedActionConnectionName""']['connectionId']""
| extend tmpConnection = propertiesJson[""parameters""][""`$connections""][""value""][parsedActionConnectionName]
| where notnull(tmpConnection)
| extend connectionId=extract(""\""id\"":\""(.*?)\"""", 1, tostring(tmpConnection))
| where notnull(connectionId) and connectionId endswith ""/managedApis/sql""
| project id, name, resourceGroup, actionsJson
| summarize by resourceGroup, logicAppName = name"
# Define the Azure Resource Graph query to find Logic Apps with SQL triggers
$azGraphQueryTriggers = "resources
| where subscriptionId == ""$subscriptionID""
| where type == ""microsoft.logic/workflows""
| extend propertiesJson=parse_json(properties)
| extend triggersJson=propertiesJson[""definition""][""triggers""]
| mv-expand triggersJson
| where notnull(triggersJson)
| extend path=extract(""\""path\"":\""(.*?)\"""", 1, tostring(triggersJson))
| where notnull(path) and path startswith ""/datasets/default/""
| extend triggerConnectionName=extract(""\""connection\"":{\""name\"":\""(.*?)\""}"", 1, tostring(triggersJson))
| where notnull(triggerConnectionName)
| parse triggerConnectionName with ""@parameters('`$connections')['""parsedTriggerConnectionName""']['connectionId']""
| extend tmpConnection = propertiesJson[""parameters""][""`$connections""][""value""][parsedTriggerConnectionName]
| where notnull(tmpConnection)
| extend connectionId=extract(""\""id\"":\""(.*?)\"""", 1, tostring(tmpConnection))
| where notnull(connectionId) and connectionId endswith ""/managedApis/sql""
| project id, name, resourceGroup, triggersJson
| summarize by resourceGroup, logicAppName = name"
# Execute the Azure Resource Graph query for Logic Apps with SQL actions
$logicAppsWithSQLActions = Search-AzGraph -Query $azGraphQueryActions
# Execute the Azure Resource Graph query for Logic Apps with SQL triggers
$logicAppsWithSQLTriggers = Search-AzGraph -Query $azGraphQueryTriggers
# Combine and sort the results from both queries, ensuring uniqueness
$logicApps = $($logicAppsWithSQLActions; $logicAppsWithSQLTriggers) | Sort-Object -Property @{Expression={$_.resourceGroup + $_.logicAppName}} -Unique
# Iterate over each Logic App found
foreach ($logicApp in $logicApps)
{
# Get the details of the current Logic App
$thisLA = Get-AzLogicApp -ResourceGroupName $logicApp.resourceGroup -Name $logicApp.logicAppName
# Convert the Logic App definition to a string
$definitionString = $thisLA.Definition.ToString()
# Replace the dataset path in the definition string
$newDefinitionString = $definitionString.Replace("/datasets/default/", "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/")
# Update the Logic App definition with the new string
Set-AzLogicApp -ResourceGroupName $logicApp.resourceGroup -Name $logicApp.logicAppName -Definition $newDefinitionString -Confirm:$false -Force
}
What’s going on here?
- After connecting to Azure and setting the context, we run an couple of Azure Graph Explorer queries to get the list of Logic Apps that use the V1 SQL connector (one for actions one for triggers). The query is straight off the MS Learn article but with the count (of actions or triggers) removed and with the quotes escaped.
- The two lists of Logic Apps are then appended and de-duped, as it is possible that the same Logic App can appear in both lists and we don’t want to process it twice.
- Then for each item in the result, we grab the definition (via the Get-AzLogicApp cmdlet), which in PowerShell is an object, so we convert it to a string using the ToString() method.
- Next, run the string Replace() method across it to change the value of the “path” property as per step 3b here: https://learn.microsoft.com/en-us/connectors/sql/#migrate-operations-via-code-view-in-azure-logic-apps
- Finally, update the Logic App using Set-AzLogicApp to overwrite the definition with the new value. Note that we don’t convert this back to an object, the -Definition parameter of Set-AzLogicApp takes a serialised JSON string, which differs from what Get-AzLogicApp delivers. Thus, the conversion to string is only one way.
A note to Power Automate users: If you have flows in your organisation that use the V1 SQL actions for production things, you’ll need to identify and update them manually either in the GUI or via the flow definition. This might be a good opportunity to migrate your production workloads to Azure Logic Apps where possible to avoid similar repetitive tasks in the future as various connector actions are deprecated and replaced.
William's IT Blog 🍉 