By the end of this you have a deployIfNotExists assignment whose identity holds exactly the roles its definition asks for and nothing more, a remediation task that ran with a failure threshold you chose, and proof that the deployed resource exists on a database that was non-compliant before you started.
How this was verified: every command, setting and value below is taken from current Microsoft documentation, checked against the product’s own command reference in August 2026, and shaped by estates I have built. The sequence has not been executed end to end as a single unbroken lab run for this article. Where a step is more likely than others to differ at a console, it says so in place.
Before you start
You need Owner or User Access Administrator on the target subscription, because granting a managed identity its roles is a privilege escalation and Resource Policy Contributor cannot perform it. You need the Azure CLI and Azure PowerShell with the Az.PolicyInsights module, since one part of this build has no CLI equivalent. You need a SQL server and at least one database that does not yet have transparent data encryption enabled, which the first step creates.
The worked example is transparent data encryption on Azure SQL databases rather than diagnostic settings, because the Landing Zones build sheet on this site already deploys diagnostic settings by policy at management group scope, including the managed identity role trap, and there is nothing to gain from building it twice.
SUB_ID=$(az account show --query id -o tsv)
RG="rg-northfork-dinelab"
LOC="eastus"
DEF_ID="/providers/Microsoft.Authorization/policyDefinitions/86a912f6-9a06-4e26-b447-11b16ba8659f"
ASSIGN="NFK-Asgn-Sql-Tde"
az group create --name "$RG" --location "$LOC"Step 1. Read the roles the definition asks for
Do this before creating anything. The definition declares the roles its identity needs, and reading them is the only reliable way to know what you are about to grant. Never take the role from a blog post, including this one.
az policy definition show --name "86a912f6-9a06-4e26-b447-11b16ba8659f" \
--query "policyRule.then.details.roleDefinitionIds" --output tsvExpected output is one or more full role definition resource IDs. Resolve each to a readable name before deciding whether you are comfortable with it, because a GUID tells you nothing about what the identity will be able to do.
for RID in $(az policy definition show --name "86a912f6-9a06-4e26-b447-11b16ba8659f" \
--query "policyRule.then.details.roleDefinitionIds" -o tsv); do
az role definition list --name "${RID##*/}" --query "[].roleName" -o tsv
doneThe ${RID##*/} strips the path and leaves the GUID, because az role definition list --name takes the role name or its GUID and not the full resource ID. If the resolved role is broader than the job requires, that is a finding about the definition rather than a step to work around, and for a custom definition it is a signal to narrow the array before assigning it anywhere.
Step 2. Create something that is non-compliant on purpose
SQLSRV="sql-nfk-$RANDOM"
SQLADMIN="nfkadmin"
SQLPASS=$(openssl rand -base64 24)
az sql server create --name "$SQLSRV" --resource-group "$RG" --location "$LOC" \
--admin-user "$SQLADMIN" --admin-password "$SQLPASS"
az sql db create --name "db-nfk-payments" --server "$SQLSRV" \
--resource-group "$RG" --edition GeneralPurpose \
--family Gen5 --capacity 2
echo "server: $SQLSRV"The server name is generated because SQL server names are globally unique, and the password is generated because a build sheet that prints a password is training people to reuse it. Read the real server name from the echo. Store the password somewhere sensible for the duration of the lab and discard it at the end.
Step 3. Assign with an identity, in DoNotEnforce
Enforcement mode is the correct lever for a remediating effect, unlike the audit-versus-deny parameter used for a blocking rule. DoNotEnforce means the assignment evaluates and reports, and the deployment never fires, which is what you want while you are still confirming that the rule matches the right resources.
az policy assignment create \
--name "$ASSIGN" \
--display-name "Northfork SQL transparent data encryption" \
--scope "/subscriptions/$SUB_ID/resourceGroups/$RG" \
--policy "$DEF_ID" \
--mi-system-assigned \
--location "$LOC" \
--identity-scope "/subscriptions/$SUB_ID/resourceGroups/$RG" \
--role "SQL DB Contributor" \
--enforcement-mode DoNotEnforce| Setting | Value | Why |
|---|---|---|
--mi-system-assigned | System-assigned identity | Created and destroyed with the assignment. For a pipeline-managed assignment prefer --mi-user-assigned, so the grant is a reviewable object rather than something recreated on every run. |
--location | The region of the identity | Mandatory with a system-assigned identity. It cannot be global and it cannot be changed later. Getting it wrong means deleting and rebuilding the assignment. |
--identity-scope | The resource group, not the subscription | The narrowest scope where remediation has to run. This is the single line that decides whether this assignment becomes a standing subscription-wide grant. |
--role | Whatever step 1 resolved | Match the definition. If step 1 returned a role broader than this one, use what it returned and record why. |
--enforcement-mode | DoNotEnforce | Evaluate and report without deploying anything, until the compliance result confirms the rule matches what you intended. |
The --role and --identity-scope pair is doing the grant that the portal would have performed silently. Through the CLI it is one visible line in a script somebody can review, which is the reason to prefer this path for anything that matters.
Step 4. Confirm the grant landed before enforcing
This gate goes before enforcement, because a remediation task launched against an identity with no role assignment fails resource by resource and the errors point at the deployment rather than at the missing permission.
PRINCIPAL=$(az policy assignment show --name "$ASSIGN" \
--scope "/subscriptions/$SUB_ID/resourceGroups/$RG" \
--query identity.principalId -o tsv)
az role assignment list --assignee "$PRINCIPAL" --all \
--query "[].{role:roleDefinitionName, scope:scope}" --output tableExpected output is one row, naming the role from step 1 and scoped to the resource group. Two failure shapes matter here. No rows at all means the grant did not happen and the remediation will fail; re-run with the role and identity scope arguments. A row scoped to the subscription or higher means you granted more than intended, and that is worth correcting now rather than discovering in an access review in eighteen months.
Step 5. Evaluate, and confirm the right resources are caught
az policy state trigger-scan --resource-group "$RG"
az policy state list --resource-group "$RG" \
--filter "policyAssignmentName eq '$ASSIGN'" \
--query "[].{resource:resourceId, state:complianceState}" --output tableThe database created in step 2 should read NonCompliant. Under a deployIfNotExists policy that means the if condition matched and the existence condition found nothing, which is exactly the state a remediation task is designed to resolve.
Step 6. Turn enforcement on
az policy assignment update --name "$ASSIGN" \
--scope "/subscriptions/$SUB_ID/resourceGroups/$RG" \
--enforcement-mode DefaultFrom this point new and updated databases in scope are remediated as they are created, without a task and without anybody asking. The database that already existed is untouched until step 7, and that asymmetry is the reason it is safe to do this on a populated estate.
Step 7. Run the remediation task, with thresholds you chose
This step uses Azure PowerShell rather than the CLI, and the reason is a real gap rather than a preference. The parameters that bound a remediation task, how many resources it touches, how many deployments run at once, and what proportion may fail before it stops, exist only in the PowerShell module. A team standardised on the CLI runs every task at defaults, which means up to 500 resources, ten at a time, and a failure threshold of 100 percent, meaning it never stops on its own.
# Connect-AzAccount first if not in Cloud Shell
$ErrorActionPreference = 'Stop'
$sub = (Get-AzContext).Subscription.Id
$scope = "/subscriptions/$sub/resourceGroups/rg-northfork-dinelab"
$assign = (Get-AzPolicyAssignment -Name 'NFK-Asgn-Sql-Tde' -Scope $scope).Id
Start-AzPolicyRemediation `
-Name 'NFK-Rem-Sql-Tde-01' `
-PolicyAssignmentId $assign `
-ResourceCount 50 `
-ParallelDeploymentCount 5 `
-FailureThreshold 10| Setting | Value here | Why |
|---|---|---|
-ResourceCount | 50 | Default is 500 and the maximum is 50,000. On a first run against production, a small number turns a bad definition into a small incident. |
-ParallelDeploymentCount | 5 | Allowed range is 1 to 30, default 10. Lower concurrency means more time to notice and cancel. |
-FailureThreshold | 10 | A percentage. The default of 100 means the task never gives up. Ten means it stops once one deployment in ten has failed, which is the behaviour most people assume they already have. |
These settings cannot be changed once the task has begun. Decide them before you press the button rather than during.
Step 8. The end-to-end test
Compliance turning green is a claim by the policy engine. Read the resource itself and confirm the thing the policy promised is actually there.
az sql db tde show --database "db-nfk-payments" \
--server "$SQLSRV" --resource-group "$RG" \
--query "{database:name, state:state}" --output tableExpected output shows the encryption state as Enabled. That is the assertion worth making, because it tests the resource rather than the report, and the two have been known to disagree while a template deployment succeeds without producing the configuration anyone wanted.
Then confirm the task itself did what you think, while the record still exists. Remediation task objects are deleted by the service 60 days after their last modification, so this is not an audit trail and anything you need long term has to be captured now.
Get-AzPolicyRemediation -Name 'NFK-Rem-Sql-Tde-01' -Scope $scope |
Select-Object Name, ProvisioningState,
@{n='Succeeded';e={$_.DeploymentSummary.SuccessfulDeployments}},
@{n='Failed';e={$_.DeploymentSummary.FailedDeployments}}Step 9. Roll back
Deleting the assignment removes the system-assigned identity with it, and deletes the role assignment that identity held. It does not undo the remediation, and it should not: the encryption stays on, which is the correct outcome and the reason to be sure about a modify or deploy policy before enforcing it rather than after.
az policy assignment delete --name "$ASSIGN" \
--scope "/subscriptions/$SUB_ID/resourceGroups/$RG"
az role assignment list --assignee "$PRINCIPAL" --all --output table # expect empty
az group delete --name "$RG" --yes --no-waitCheck that the role assignment is gone rather than assuming it. Orphaned role assignments belonging to deleted identities are one of the more common findings in an access review, and this is where a share of them come from.
From here
Every rule used so far has been one Microsoft wrote and you assigned as shipped. The next article is about what happens when the built-in is nearly right: what customising one actually involves, given that Azure Policy has no edit path for a built-in, and what the copy costs you for as long as it runs.
Azure Policy
‹ Previous: [AP 5] Remediation: The Identity That Acts on Your Behalf
Next: [AP 6] Customising What Microsoft Ships, and What the Fork Costs ›
